get #N/A from vlookup function

[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

 

[Pete_UK]

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

 

[Bob Phillips]

Is the lookup value text.

Try

-VLOOKUP(--lookup_value,lookup_table,2,False)

 

[yhoy]

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)

 

[Pete_UK]

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