跳到主要内容

如果给定值存在于Excel中的某个范围内，如何返回值？

如左图所示，如果给定数字5在某个数字范围内，则如何返回相邻单元格中的值。 本文中的公式方法可以帮助您实现它。

如果给定值在一定范围内，则使用公式返回一个值

如果给定值在一定范围内，则使用公式返回一个值

如果给定值存在于Excel中的某个范围内，请应用以下公式返回一个值。

1.选择一个空白单元格，输入公式 = VLOOKUP（E2，A2：C8,3，TRUE） 进入编辑栏，然后按 输入 键。 看截图：

您可以看到给定的数字5在数字范围4-6中，然后立即将相邻单元格中的对应值Addin 012填充到所选单元格中，如上面的屏幕快照所示。

注意：在公式中，E2是包含给定数字的单元格，A2：C8包含数字范围和您将基于给定数字返回的值，数字3表示您将返回的值位于第三个列中范围A2：C8。 请根据需要更改它们。

相关文章：

最佳办公生产力工具

🤖 Kutools 人工智能助手：基于以下内容彻底改变数据分析： 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup  |   多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧   |  工作簿和工作表管理器   |  资源库 （自动文本）   |  日期选择器   |  合并工作表   |  加密/解密单元格   |  按列表发送电子邮件   |  超级筛选   |   特殊过滤器 （过滤粗体/斜体/删除线...）...
前 15 个工具集:  12 文本 工具 (添加文本, 删除字符，...）   |   50+ 图表 类型 (甘特图，...）   |   40+ 实用 公式 (根据生日计算年龄，...）   |   19 插入 工具 (插入二维码, 从路径插入图片，...）   |   12 转化 工具 (小写金额转大写, 货币兑换，...）   |   7 合并与拆分 工具 (高级组合行, 分裂细胞，...）   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能，体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述

Office Tab 为 Office 带来选项卡式界面，让您的工作更加轻松

  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
Comments (14)
No ratings yet. Be the first to rate!
Nilton Vanini
Nilton Vanini
This comment was minimized by the moderator on the site
Prezados,
Bom dia!

É possível determinar algum parâmetro dentro do Range.

Quero fazer o PROCV trazer de um intervalo, porém ele acaba retornando valores de CEPs que não existem, mas que estão muito próximos...

Exemplo:

Na tabela existe a faixa de CEP 68795000 A 68796999 que é do estado do Pará
Ao utilizar as formula sugeridas, resulta como "verdadeiro" para a faixa de CEP 69301000 A 69390971 que é do estado de Roraima, devido a proximidade da numeração.
Reply
0
0
crystal
crystal     Nilton Vanini
This comment was minimized by the moderator on the site
Hi Nilton Vanini,
I don't quite understand your question. Do you mind uploading a screenshot of your data? Sorry for the inconvenience.
Reply
Report
0
0
Andrew
Andrew
This comment was minimized by the moderator on the site
This seems to also work with text. I got this to work with exact value rather than a range by using the "Value" column as part of the range in the formula.

My question is, are you forced to use 2 columns or is it possible to only use 1 and the value column?

I created a master sheet to call from but this is how I setup the formla

=VLOOKUP(I2,'Master Sheet'!$A$2:$B$53,2,TRUE)

Column "I" text (Number column in the example), I was forced to have a range so column "B" is both the Range and Value.

Is this best practice or would I run into unexpected issues down the road if more line items are added to this list?
Reply
0
0
crystal
crystal     Andrew
This comment was minimized by the moderator on the site
Hi Andrew,
You may need to upload a screenshot of your data. Sorry for the inconvenience.
Reply
Report
0
0
Damien Dulac
Damien Dulac
This comment was minimized by the moderator on the site
Bonjour je cherche à vérifier si une de mes valeurs correspond à une plage de données, le problème est que je n'ai pas qu'une valeur à vérifier mais une longue liste. J'aimerais que si une des 3 valeurs suivantes que je tape dans une case (exemple "panneau";"Strat";"chants") correspondent à une plage de données, elles 's'affichent en rouge par exemple. Sauriez vous me renseigner ? Merci.
Reply
0
0
Daili
Daili
This comment was minimized by the moderator on the site
Yo quiero devolver 1, si se encuentra en un rango de numeros determinados, ej. el rango es de 4 a 10, y en la celda tengo 5, devolver 1, pero si tengo 2 o 15, devolver 0.
Reply
0
0
crystal
crystal     Daili
This comment was minimized by the moderator on the site
Hi Daili,
If you just want to check if a number exists in a given range and return a specified value, you can combine IF with AND functions to get it done.
Suppose the number 4 is in cell A9, the number 10 is in cell B9, the number you want to determine if it exists between 4 and 10 is locateed in D9, you can apply the following formula to get the correct result.
=IF(AND(D9>A9,D9<B9),1,0)
Reply
Report
0
0
Judy
Judy
This comment was minimized by the moderator on the site
Can you do a lookup searching a range of 3digit zips (i.e. 010-013 or 280-283,286,297) and pull in the city / state from your zip code list. The zip code list is all 3 digit zips in US broken out individually. The zips are listed multiple times because there are several city / state combinations. (i.e. 010 Ludlow MA; 010 Plamer MA etc).
Reply
0
0
kl
kl
This comment was minimized by the moderator on the site
The value works on the first cell. How do you do it for the entire column cell?
Reply
0
0
Jim
Jim     kl
This comment was minimized by the moderator on the site
You will need to use $ for the lookup cells values
So if you have a column of values you want to look up you would modify the formula as follows:
=VLOOKUP(E2,$A$2:$C$8,3, TRUE)

E2 would change as you fill down but the table you are looking up from will remain constant
Reply
0
0
Adele
Adele
This comment was minimized by the moderator on the site
awesome, thanks!
Reply
0
0
Teresa
Teresa
This comment was minimized by the moderator on the site
Doesn't work. if only it was that simple, wouldn't need to be googling for this
Reply
0
0
treefireonwater@gmail.com
    Teresa
This comment was minimized by the moderator on the site
It obviously does. Instead of whining and trying to be a smartass about it, you could have at least tried it out.
Reply
Report
0
0
Mika Donner
Mika Donner     Teresa
This comment was minimized by the moderator on the site
It does. Try it out before rudely complaining, simple as that.
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations