For the following table,
下面的表,
I want to look up the value in col C. Since the values in col A and col B are not unique, VLOOKUP fails. For example, VLOOKUP(1,table,3) returns 5 and never 1.
我想查找col c中的值，因为col A和col B中的值不是唯一的，VLOOKUP失败。例如，VLOOKUP(1,table,3)返回5，从不返回1。
However, the combinations of cols A & B are unique. For example, 1blah = 5, while 1foo = 1.
然而，cols A和B的组合是唯一的。例如，1blah = 5，而1foo = 1。
How can I use the combination of cols A & B as a unique key to return the corresponding value in col C?
如何使用cols A和B组合作为唯一键返回col C中的对应值?
I'm not sure if this should be implemented with worksheet functions or a custom VBA function. I tried using CONCATENATE to generate the unique key, but this did not work because this results in combinations of numbers and strings.
我不确定这是否应该通过工作表函数或自定义VBA函数实现。我尝试使用CONCATENATE来生成唯一的键，但这不起作用，因为这会导致数字和字符串的组合。
3 个解决方案
#1
6
You can use an array formula:
你可以使用数组公式:
=INDEX($C$1:$C$7,MATCH("1foo",$A$1:$A$7 & $B$1:$B$7,0))
just select in example D1, enter formula in formula bar and press CTRL+SHIFT+ENTER to evaluate it
只需在示例D1中选择，在公式栏中输入公式并按CTRL+SHIFT+ enter对其进行计算
#2
1
The way I usually do it is by concatenating the values separated by a pipe character (|). See the formula in the screenshot below.
我通常的做法是将由管道字符(|)分隔的值连接起来。请看下面截图中的公式。
Then you can vlookup using the concatenated key.
然后可以使用连接键进行vlookup。
=VLOOKUP("1|foo",$C$1:$D$7,2,FALSE)
#3
0
You can use VLOOKUP if your lookup value is a concatenation of your two or more key fields (A2&B2). Then, add a first sorted column in your LUT sheet with the array that is the concatenation of the same key fields. Viola.
如果查找值是两个或多个键字段(A2&B2)的串联，则可以使用VLOOKUP。然后，在LUT表中添加第一个排序的列，该列的数组是相同键字段的连接。中提琴。
#1
6
You can use an array formula:
你可以使用数组公式:
=INDEX($C$1:$C$7,MATCH("1foo",$A$1:$A$7 & $B$1:$B$7,0))
just select in example D1, enter formula in formula bar and press CTRL+SHIFT+ENTER to evaluate it
只需在示例D1中选择，在公式栏中输入公式并按CTRL+SHIFT+ enter对其进行计算
#2
1
The way I usually do it is by concatenating the values separated by a pipe character (|). See the formula in the screenshot below.
我通常的做法是将由管道字符(|)分隔的值连接起来。请看下面截图中的公式。
Then you can vlookup using the concatenated key.
然后可以使用连接键进行vlookup。
=VLOOKUP("1|foo",$C$1:$D$7,2,FALSE)
#3
0
You can use VLOOKUP if your lookup value is a concatenation of your two or more key fields (A2&B2). Then, add a first sorted column in your LUT sheet with the array that is the concatenation of the same key fields. Viola.
如果查找值是两个或多个键字段(A2&B2)的串联，则可以使用VLOOKUP。然后，在LUT表中添加第一个排序的列，该列的数组是相同键字段的连接。中提琴。