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

On Dec 12, 10:30*am, yhoy wrote:
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

Is the lookup value text.

Try

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

--
__________________________________
HTH

Bob

"yhoy" wrote in message
...
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

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" wrote:

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

On Dec 12, 10:30 am, yhoy wrote:
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

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,l ookuptable!$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

On Dec 12, 5:00*pm, yhoy wrote:
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" wrote:
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

On Dec 12, 10:30 am, yhoy wrote:
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- Hide quoted text -

- Show quoted text -