跳到主要内容

如何在Excel中vlookup查找第一个，第二个或第n个匹配值？

假设您有两列产品和数量，如下图所示。 为了快速找出第一根或第二根香蕉的数量，您会怎么做？

这里的vlookup函数可以帮助您解决此问题。 在本文中，我们将向您展示如何使用Excel中的Vlookup函数vlookup查找第一个，第二个或第n个匹配值。

Vlookup使用公式在Excel中查找第一个，第二个或第n个匹配值

使用 vlookup 在 Excel 中轻松找到第一个匹配值 Kutools for Excel

Vlookup在Excel中查找第一个，第二个或第n个匹配值

请执行以下操作以在Excel中找到第一个，第二个或第n个匹配值。

1.在单元格D1中，输入要查看的条件，在这里我输入“香蕉”。

2.在这里，我们将找到香蕉的第一个匹配值。 选择一个空白单元格，例如E2，复制并粘贴公式 =INDEX($B$2:$B$6,MATCH(TRUE,EXACT($D$1,$A$2:$A$6),0)) 进入编辑栏，然后按 按Ctrl + 转移 + 输入 键同时。

备注：在此公式中，$ B $ 2：$ B $ 6是匹配值的范围； $ A $ 2：$ A $ 6是具有所有vlookup条件的范围； $ D $ 1是包含指定vlookup条件的单元格。

然后，您将在单元格E2中获得香蕉的第一个匹配值。 使用此公式，您只能根据您的条件获得第一个对应的值。

要获取第n个相对值，可以应用以下公式： =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),1)) + 按Ctrl + 转移 + 输入 键在一起，此公式将返回第一个匹配的值。

:

1.要找到第二个匹配值，请将上面的公式更改为 =INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),2))，然后按 按Ctrl + 转移 + 输入 同时键。 看截图：

2.上式中的最后一个数字表示vlookup条件的第n个匹配值。 如果将其更改为3，它将获得第三个匹配值，并更改为n，将找到第n个匹配值。

Vlookup 在 Excel 中找到第一个匹配值 Kutools for Excel

Y您可以轻松地在Excel中找到第一个匹配值，而无需记住公式 在列表中查找值 公式的 Kutools for Excel.

申请前 Kutools for Excel请 首先下载并安装.

1.选择一个单元格以查找第一个匹配值（例如单元格E2），然后单击 库工具 > 公式助手 > 公式助手。 看截图：

3.在 公式助手 对话框，请进行如下配置：

  • 3.1在 选择一个公式 框，找到并选择 在列表中查找值;
    Tips：您可以检查 筛选器 框，在文本框中输入特定单词以快速过滤公式。
  • 3.2在 表格数组 框，选择 包含第一个匹配值的表格。;
  • 3.2在 查找值 框，选择包含 标准 您将基于返回第一个值；
  • 3.3在 柱 框，指定您要从中返回匹配值的列。 或者，您可以根据需要直接在文本框中输入列号。
  • 3.4点击 OK 按钮。 看截图：

现在，将基于下拉列表选择在单元格C10中自动填充相应的单元格值。

  如果您想免费试用（30-day) 这个实用程序， 请点击下载，然后按照上述步骤进行操作。

最佳办公生产力工具

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

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

kte选项卡201905

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

  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
Comments (43)
No ratings yet. Be the first to rate!
sima
sima
This comment was minimized by the moderator on the site
You only return first match with VLOOKUP so your article "How To Vlookup Find The First, 2nd Or Nth Match Value In Excel?" does not make any sense....
Reply
0
0
crystal
crystal     sima
This comment was minimized by the moderator on the site
Hi Sima,
"VLOOKUP" is a Google search term, so I used it as a keyword for the article title. The main operation of this article is to lookup values. I'm sorry for the misunderstanding.
Reply
Report
0
0
emenson
emenson
This comment was minimized by the moderator on the site
What if the next record on col b is a duplicate how do ignore duplicate? Let's Banana has quantities of 200 twice? Can you please help me figure out how to ignore the duplicate?
Reply
0
0
Demco K
Demco K
This comment was minimized by the moderator on the site
Cna anybody Explain the small function with the IF statement for me please? I don't really understand how that array works :(
Reply
0
0
SC
SC
This comment was minimized by the moderator on the site
The following formulas works perfect for me:
(a) or(b) =IF(H8='Raw Data'!B4,INDEX('Raw Data'!H6:H20,SMALL(IF(C18='Raw Data'!B6:B20,ROW('Raw Data'!B6:B20)-ROW('Raw Data'!B6:B20)+1),1)))

However, I have encountered a problem where there are 7 sets of the same criteria but in different columns:(1) can the above formula (a) be repeated and search in a different cells in a single formula and(2) the above formula (b) only allows up to two sets of the similar search with matched value result but when trying for the third set in the formula as showed below, Microsoft Excel appeared as "You've entered too many arguments for this function."
=<span style="letter-spacing: 0.2px; color: inherit; font-family: inherit; font-style: inherit; font-variant-ligatures: inherit; font-variant-caps: inherit; font-weight: inherit;">IF(H8='Raw Data'!J4,INDEX('Raw Data'!P6:P20,SMALL(IF(C18='Raw Data'!J6:J20,ROW('Raw Data'!J6:J20)-ROW('Raw Data'!J6:J20)+1),1))),</span>
Reply
0
0
Cesar B
Cesar B
This comment was minimized by the moderator on the site
What do Ctrl + Shift + Enter do?? At the beginning I didn'
Reply
0
0
crystal
crystal     Cesar B
This comment was minimized by the moderator on the site
Hi，An array formula needs to hit the Ctrl + Shift + Enter keys simultaneously to get the correct result.
Reply
Report
0
0
Shahzad Afzal
Shahzad Afzal
This comment was minimized by the moderator on the site
I have a scenario... How do I get last price of anything for reference... Example: Banana first price was 200... While purchasing for second time; I need to display 200 in my expected price cell and then if I buy that on the day at 220, I will put this value manually as 220... Whenever next time I will buy banana; I need to display 220 from the last purchase price
Reply
0
0
slattynan
slattynan     Shahzad Afzal
This comment was minimized by the moderator on the site
Try =INDEX($B$2:$B$6,XMATCH(TRUE,EXACT($D$1,$A$2:$A$6),0,-1))

This is essentially reversing the search order and returning the first match using the XMATCH function.

Better late than never, hopefully helps someone :)
Reply
Report
0
0
Hoori
Hoori
This comment was minimized by the moderator on the site
Perfect explanation, thanks.
Reply
0
0
rasho
rasho
This comment was minimized by the moderator on the site
If the first or any of the other entry's for 'banana' column B was blank cell, of which I don't require this number, what changes are required to this formula to skip blank cell in column B.
Reply
Report
0
0
rasho
rasho     rasho
This comment was minimized by the moderator on the site
Sorry I am using this formula
=INDEX($B$2:$B$6,SMALL(IF($D$1=$A$2:$A$6,ROW($A$2:$A$6)-ROW($A$2)+1),1))
Reply
Report
0
0
rasho
rasho     rasho
This comment was minimized by the moderator on the site
SOLVED:
=SMALL(IF(A2:A7=D1,IF(B2:B7<>"",B2:B7)),1)

If 2nd or 3rd number required exchange ),1) to 2 or 3

This formula does not require index, as it directly looks at the value in Cell
Reply
Report
0
0
rasho
rasho     rasho
This comment was minimized by the moderator on the site
Correction to previous formula:
The value was reading either the lesser or greater.

Updated formula
=INDEX($B$2:$B$7,SMALL(IF($A2:$A6=$D$1,IF($B$2:$B$7<>"",ROW($A2:$A6)-ROW($A2)+1)),1))

This skips blank cell and places value of non blank cell. Replace +1 with +2 or +3 for 2nd or 3rd value
Reply
Report
0
0
JAntonio
JAntonio
This comment was minimized by the moderator on the site
And if you want the last, second last, nth last just add a counter (count the number of events already hapenned) to the end and subtract it by 0,1,n respectively.

Thank you so much! I was searching for this for a long time
Reply
0
0
crystal
crystal     JAntonio
This comment was minimized by the moderator on the site
Good Day,
Sorry can't help you with this yet. Thank you for your comment.
Reply
Report
0
0
Seth
Seth
This comment was minimized by the moderator on the site
Is it possible to find an average of the non-unique data. Or would it be possible to have a list dropdown on the cell of the various values?
Reply
0
0
crystal
crystal     Seth
This comment was minimized by the moderator on the site
Good Day,
Sorry can't help you with this yet. Thank you for your comment.
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations