VLOOKUP 与索引 [英] VLOOKUP vs INDEX

问题描述

在 Excel 中，我尝试执行以下操作:

In Excel, I'm trying to do the following:

其中sheet1 column 1 = sheet2 column 2，返回sheet2列D

Where sheet1 column 1 = sheet2 column 2, return the value in sheet2 column D

我对如何执行此操作感到困惑，因为我发现的每个示例似乎都使用包含公式的工作表的列索引值.(即，sheet1)

I'm stumbling on how to do this as every example I've found seems to use the column index value of the sheet containing the formula. (i.e., sheet1)

我想要:VLOOKUP(sheet1!A1,sheet2!A2:A11696,sheet2!4,FALSE)
我只能:VLOOKUP(sheet1!A1,sheet2!A2:A11696,4,FALSE)

阅读其他主题后，我看到人们似乎推荐使用 INDEX.所以我尝试了

After reading other threads, I see people seem to recommend using INDEX. So I tried

=INDEX(sheet2!A2:A11696, MATCH(sheet1!A1004,sheet2!D:D,FALSE))

这也不起作用.

推荐答案

你的 VLOOKUP 只引用了一个 ccolumn，应该是 3.然后从 B 列开始

Your VLOOKUP only references one ccolumn, It should be 3. And start in Column B

VLOOKUP(sheet1!A1,sheet2!B2:D11696,3,FALSE)

第一个标准是查找什么，sheet1!A1

The First Criterion is the what to lookup, sheet1!A1

第二个是要查找的值和要返回的值所在的范围.范围的第一列必须是将在其中找到条件的列.根据 sheet1 column 1 = sheet2 column 2 然后将在 B 列中开始范围.

The second is the range in which the value to find and the value to return is found. The first column of the range must be the column in which the criteria will be found. As per sheet1 column 1 = sheet2 column 2 that would then start the range in Column B.

并且由于您想要的列 D 列 D 中的值必须包含在范围内.

And since the value you want in Column D Column D must be included in the range.

第三个是值在范围的哪一列.它不是列号本身，而是相对列号，在本例中它是范围 sheet2!B2:D11696 中的第三列.

The Third is in which column of the range is the value. It is not the column number itself, but the relative column number, in this case it is the third column in the Range sheet2!B2:D11696.

第四个强制完全匹配或相对匹配.FALSE 强制精确匹配.

The forth forces an exact match or relative match. FALSE forces an Exact Match.

如果您要使用索引/匹配，则:

If you are going to use an INDEX/MATCH then:

=INDEX(sheet2!D2:D11696, MATCH(sheet1!A1,sheet2!B2:B11696,0))

MATCH 部分返回相对行号，其中 A1 在第二张纸的 B 列中找到.

The MATCH part returns the relative row number where A1 is found in Column B on sheet two.

然后在 INDEX 中使用这个数字，它会在 D 列的范围内找到相对行号并返回该值.

Then using this number in the INDEX it finds that relative row number in the range in Column D and returns that value.

MATCH() 中的 0 告诉 Match 寻找精确匹配.

The 0 in the MATCH() tells the Match to look for the exact match.

这篇关于VLOOKUP 与索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！