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


"sfeng63" wrote:

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.