why this strange result with vlookup

hi i have this formula in a cell =VLOOKUP(A3,'LookUp Data'!A2:O740,2,FALSE)
but any time i pick a code from my list items in column A it gives #NA but
as soon as i change to a different code it gives the name.
any help why

My best guess would be a data type mismatch. Looking up text in a column of
numbers. When you change the value the type gets converted and the result is
returned.

Hard to say though based on yoru description. The ohter thig to look for
might be blank characters padded at the end of the input.
--
HTH...

Jim Thomlinson


"Amin" wrote:

hi i have this formula in a cell =VLOOKUP(A3,'LookUp Data'!A2:O740,2,FALSE)
but any time i pick a code from my list items in column A it gives #NA but
as soon as i change to a different code it gives the name.
any help why

Any chance that your values are text and you're trying to match a number -- or
your values are numbers and you're trying to match text.

'123
is different from
123

And changing the format of the cell isn't sufficient to fix the problem.

Or maybe you have extra spaces in some of the entries????

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

Amin wrote:

hi i have this formula in a cell =VLOOKUP(A3,'LookUp Data'!A2:O740,2,FALSE)
but any time i pick a code from my list items in column A it gives #NA but
as soon as i change to a different code it gives the name.
any help why

--

Dave Peterson

Try swing it to a text match:
=VLOOKUP(A3&"",'LookUp Data'!A2:O740,2,FALSE)
The &"" bit will convert the lookup value in A3 (which might be a real
number) into text for consistent matching, w/o impacting text lookup value.
--
Max
Singapore
---
"Amin" wrote:
hi i have this formula in a cell =VLOOKUP(A3,'LookUp Data'!A2:O740,2,FALSE)
but any time i pick a code from my list items in column A it gives #NA but
as soon as i change to a different code it gives the name.
any help why