Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
menu search
person
Welcome To Ask or Share your Answers For Others

Categories

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
738 views
Welcome To Ask or Share your Answers For Others

1 Answer

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
...