使用三个查找值的VLOOKUP替代 [英] VLOOKUP alternative using three lookup values

问题描述

您知道Excel仅允许您使用一个查找值，但是我需要比较三个查找值以找到第四个值.

As you know Excel only lets you use one lookup value, but I need to compare three lookup values to find a fourth value.

例如，在下面的屏幕截图中，我想使用信息name，pet和business来查找第二张工作表上的teacher是谁.作为记录，这只是了解如何执行任务的示例数据，我需要将其应用于600多个行！

For example in the screenshot below I want to use the information name, pet and business to find who the teacher is on the second sheet. For the record this is just example data to understand how to perform the task and I need to apply this to over 600 rows!

第一张纸: 这是第一张纸

第二张纸: 这是我的第二张纸

当我尝试输入以下公式时，我总是遇到错误:= index(Sheet2！A2:G7，MATCH(Sheet1！A5& Sheet！C5& Sheet1！D5，Sheet2！B2:B7& Sheet2！D2 :D7& Sheet2！E2:E7,0))

I keep getting an error when I try to enter this formula: =index(Sheet2!A2:G7,MATCH(Sheet1!A5&Sheet!C5&Sheet1!D5,Sheet2!B2:B7&Sheet2!D2:D7&Sheet2!E2:E7,0))

推荐答案

您可以使用索引/匹配来代替Vlookup()，这是一个不错的选择，可以让您执行此操作.

Instead of Vlookup(), you can use Index/Match, which is a nice alternative that lets you do this.

该公式正确返回史密斯小姐": 注意:这是使用 CTRL + SHIFT + ENTER

The formula correctly returns "Miss Smith": Note: This is entered as an array formula with CTRL+SHIFT+ENTER

因此，公式的基本布局为: =Index([range to return],match([single look up value],[range where that value would be],0))

So, the basic layout of the formula is: =Index([range to return],match([single look up value],[range where that value would be],0))

您可以通过串联查找值来组合查找值，如屏幕截图所示.因此，单个查找值为A2，C2和D2.而且，您希望在G2:G3范围内找到"Sarah"，在H2:H3范围内找到"Dog"，等等.

You can combine lookup values by concatenating them, as you can see in the screenshot. So, the single lookup values are A2, C2, and D2. And, you'd expect to find "Sarah" in the range G2:G3, "Dog" in H2:H3, etc.

请让我知道是否可以澄清任何事情.

Please let me know if I can clarify anything.

以帮助可视化两张表.我将表(您将在其中查找 )放在名为数据表"的表上.我们需要完成的表位于主WS"上.

To help visualize the two sheets thing. I put the table (which you will lookup from) on a sheet called "Data Sheet". The table we need to complete is on "Main WS".

这里是主要WS":

Here's "Main WS":

和数据表":

这篇关于使用三个查找值的VLOOKUP替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！