Prev: Percentages in Pivotal
Next: looking for a function that will populate opening a worksheet
From: Amin on 22 Apr 2010 14:21
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

From: Jim Thomlinson on 22 Apr 2010 14:58
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
>
From: Dave Peterson on 22 Apr 2010 15:35
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
From: Max on 22 Apr 2010 19:16
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
>
 | 
Pages: 1
Prev: Percentages in Pivotal
Next: looking for a function that will populate opening a worksheet