VLookUp value to another worksheet

问题:

I need to get the value of the corresponding item in Lookup table. But, I am getting N/A in my output. In worksheet1, I need to use column H as a lookup value then reference it in VLookup sheet or Table2 using ColumnC, the corresponding value should get the value in ColumnD. I used this code:

=VLOOKUP(H2, Table2, 4, FALSE)

Is there something wrong in my code?

Sample value:

Screenshot for Sheet1:

Screenshot for VLookup

回答1:

Correct your vlookup to :

=VLOOKUP(H2,C2:D128,2, FALSE)

Or define Table3 as c2:D128... and:

=VLOOKUP(H2, Table3, 4, FALSE)

To go the index & match route and it does have advantages, that the data does not have to be in the same area or even on the same sheet, I would suggest:

=index(sheet_name!D2:D128,MATCH(H2,sheet_name!C2:C128,0))

You can see the ranges defined in comparison to the vlookup, ie column D has the result, Column C the target... See:

回答2:

VLOOKUP always looks in the first column of the lookup_range. It then return the corresponding value from the column to the right identified by column_num. If you aren't trying to find something in the first column then you need an INDEX/MATCH pair instead.

=index(table2[legend], match(h2, table2[function], 0))

You do not need to add the worksheet names if Table2 is a structured table. Table2 is a unique identifier and can be referenced across worksheets.

回答3:

Try using the formula VLOOKUP(H2, 'Sheetname'!$C:$D,2, FALSE). Your formula will not work because the column(Column C in your case) that you are using to lookup the value is not the left most.