VLOOKUP vs INDEX

Question

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

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

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)

I want: VLOOKUP(sheet1!A1,sheet2!A2:A11696,sheet2!4,FALSE)
I can only: VLOOKUP(sheet1!A1,sheet2!A2:A11696,4,FALSE)

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))

This doesn't work either.

Answer 1

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

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

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

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.

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

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.

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))

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

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

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

Answer 2

The INDEX/MATCH function pair should look like this.

=INDEX(sheet2!D:D, MATCH(sheet1!A1, sheet2!B:B, 0))

Answer 3

In a more generic sense, the INDEX/MATCH method is used as follows:

=INDEX(A:A, MATCH(B1, C:C, 0))

Where:

A:A = The row or column containing the value you need to find.

B1 = The value you are using to reference the index (location) of the value you're trying to find.

C:C = The row or column containing the value that matches B1. The size of this range should match the size of A:A, though it is not required.

0 = This just means "Match exactly". -1 would mean "match if B1 is less than C:C. 1 would mean "match if B1 is greater than C:C.