Lookup function

[sfeng63]

I would like to set up a lookup table. when I enter a value in the
spreadsheet, it will find the match value on the column A and print out the
value of column B.

See below
A B
A-01 10.5
A-02 5.6
A-2C 21.2
A-13 11.6

I used lookup function. If the entry is A-2C, the result shows 11.6 instead
of 21.2. Anyone can tell me why? How can I fix the problem? Besides, I
would like to set a function. If my entry does not exist in the table, e.g.
A-1B, I would like to show a "NA" in the result column so I know the input is
incorrect. Can you tell me how to do it?
Thanks.

 

[WLMPilot]

First of all, I cannot tell you what is wrong with your formula with you
showing it. However, this is what I came up with and it worked. I used A1
as the cell the user would enter that data ("lookup value"). I had the table
in A5:B8.

=IF(COUNTIF(A5:A8,A1)=0,"NA",LOOKUP(A1,A5:A8:B5:B8))

What happens in this formula is that it counts the number of times the data
entered matches that data in the table. If it is 0, then it does not exist.
Otherwise it finds the appropriate value.

Hope this helps you out,

Les

 

[Gaurav]

=IF(ISNA(VLOOKUP(F1,Sheet1!A1:B4,2,FALSE)),"NA",VLOOKUP(F1,Sheet1!A1:B4,2,FALSE))

change the references to suit your need.

 

[Niek Otten]

Make sure both your search argument and your table entries don't have any leading or trailing spaces in them. You can check by
using the LEN() function and compare the result with what you see.

Use VLOOKUP instead of LOOKUP

Formula if search argument is in C1:

=VLOOKUP(C1,A2:B5,2,FALSE)

BTW If you post a question, always supply your formula, the values of the input cells , the expected result and what you got
instead.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


|I would like to set up a lookup table. when I enter a value in the
| spreadsheet, it will find the match value on the column A and print out the
| value of column B.
|
| See below
| A B
| A-01 10.5
| A-02 5.6
| A-2C 21.2
| A-13 11.6
|
| I used lookup function. If the entry is A-2C, the result shows 11.6 instead
| of 21.2. Anyone can tell me why? How can I fix the problem? Besides, I
| would like to set a function. If my entry does not exist in the table, e.g.
| A-1B, I would like to show a "NA" in the result column so I know the input is
| incorrect. Can you tell me how to do it?
| Thanks.