VLOOKUP vs INDEX [英] VLOOKUP vs INDEX

问题描述

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

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

其中sheet1列1 = sheet2列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列1 = sheet2列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.

如果要使用INDEX / MATCH，则：

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

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

MATCH部分返回在表二中的列B中找到A1的相对行号。

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.

<$ c MATCH（）中的$ c> 0 会告诉Match找到完全匹配。

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

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