Excel VLOOKUP，其中键不在第一列中 [英] Excel VLOOKUP where the key is not in the first column

问题描述

查找值所在的范围.请记住，查找值应始终在VLOOKUP正常工作的范围内的第一列.例如，如果查找值在单元格C2中，则应从范围开始与C."

"The range where the lookup value is located. Remember that the lookup value should always be in the first column in the range for VLOOKUP to work correctly. For example, if your lookup value is in cell C2 then your range should start with C."

但是有时候我希望能够进行双向查找.即，使用A列中的键进行查找以获取B列中的值，同时，在其他公式中，使用B列中的值进行查找以获取A中的值.

But sometimes I want to be able to do dual-direction lookups. Ie, lookup using a key in column A to get the value in column B AND at the same time, in other formulas, lookup the value in B to get the value in A.

我知道的唯一方法是添加一个对A进行镜像的列C，然后将AB用于第一次查找，并将BC用于第二次查找.但是必须有一些更清洁的解决方案.是否有某种方法可以强制VLOOKUP使用除第一个列以外的其他列来查找键值，或者是否存在允许等效的函数?

The only way I know is to add a column C which mirrors A, then use AB for the first lookup, and BC for the second lookup. But there has to be some cleaner solution. Is there some way to force VLOOKUP to use a different column other than the first one to find the key value, or is there a different function that would allow the equivalent?

作为补充，我问的是Excel，但实际上我使用的是LibreOffice.假定功能应该相同，但是最好在LibreOffice中也可以使用答案.

As a side note, I am asking about Excel, but I actually use LibreOffice. Presumably the functions should be identical, but an answer that also works in LibreOffice would be preferable.

推荐答案

INDEX/MATCH will do it any direction of search.

对于您的B示例-> A:

So for your example of B --> A:

=INDEX(A:A,MATCH(yourCriteria,B:B,0))

MATCH返回匹配项的行号. 0的第三个条件是可选的.对于第四个VLOOKUP标准，0与FALSE相同，因为它查找完全匹配.

The MATCH returns the row number of the match. The third Criterion of 0 is optional. The 0 is the same as FALSE for the forth criterion of VLOOKUP, in that it looks for an exact match.

默认值为1，数据排序后，它将返回小于或等于条件(如VLOOKUP的TRUE)的匹配项.

The default is 1 with the data sorted it will return the match that is less than or equal to the criteria Like VLOOKUP's TRUE.

通过INDEX查找并返回正确的值.

From that the INDEX finds and returns the correct value.

这篇关于Excel VLOOKUP，其中键不在第一列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！