get #N/A from vlookup function

  • Thread starter Thread starter yhoy
  • Start date Start date
Y

yhoy

It seems to be an easy task however I keep getting #N/A-- I have checked so
far:

- the "table array" is sorted
- both "look up value" and the "table array" have format "General"
- lookup value is in the "table array"

what can possibly go wrong? any help is appreciated
 
It might be that the lookup value is actually text and the lookup
vector is made up of numbers, or vice-versa. Formatting does not
affect the underlying value in the cell. Post your formula and then I
could advise how you might trap both of these situations.

Pete
 
How do I change the underlying value of the cell? Both of them show up as
number with format "General" and I tried to change format to "number" and it
didn't help- as you prdicted.

The formula is pretty simple

= vlookup(A2, lookuptable $A2, B35, 2, false)
 
Well that is not a syntactically correct formula - you probably meant
this:

= vlookup(A2, lookuptable!$A$2:$B$35, 2, false)

where lookuptable is the name of the sheet where your data can be
found. If this is correct then you can try this:

=IF(ISNA(VLOOKUP(A2+0,lookuptable!$A$2:$B$35,2,0)),IF(ISNA(VLOOKUP
(A2&"",lookuptable!$A$2:$B$35,2,0)),"not found",VLOOKUP
(A2&"",lookuptable!$A$2:$B$35,2,0)),VLOOKUP(A2+0,lookuptable!$A$2:$B
$35,2,0))

A2+0 forces A2 to become a number if it contains text that looks like
a number, and A2&"" forces A2 to become text, even if it is really a
number, so this formula tests all those possible conditions.

Hope this helps.

Pete
 
Back
Top