VLOOKUP for a cell with both letters and numbers

S

Sonohal

Hello

I am trying to use VLOOKUP to find an exact match, the problem is that the
cell contains data that is a series of letters and numbers. I have used
vlookup to find and then return data successfully if the search cells are all
letters OR all numbers, but can't get it to work when both are present. I
have also tried formatting the cells to read as 'text', or 'general', neither
of which made a difference. PLEASE HELP! Thanks
 
T

Trevor Shuttleworth

Please give examples of the data you are using, the value you are using as a
key and the LOOKUP formula itself

Regards

Trevor
 
S

Sonohal

This is the formula that works:
=VLOOKUP(J1,'VW THCD List'!G:BE,3,0)
It searches data on a different tab, the inserts it.

I am essentiall looking up an assigned number and returning a specific piece
of data in the row that the assigned number is in. This formula is to
display the corresponding name of the assigned number. The problem is when
the assigned number (which is usually six numbers long) has a letter in it.
It is always in the fourth position, and for some reason, vlookup can't find
it.
 
T

Trevor Shuttleworth

Try:

=VLOOKUP(J1,'VW THCD List'!$G:$BE,3,FALSE)

or with error trapping:

=IF(ISNA(VLOOKUP(J1,'VW THCD List'!$G:$BE,3,FALSE)),"not
found",VLOOKUP(J1,'VW THCD List'!$G:$BE,3,FALSE))

works for me with 123x45

Regards

Trevor
 
S

Sonohal

Thanks, but still no luck. I do get the "not found" message though. Do the
columns have to have a particular format, ie text, general, number, etc.?
 
F

Fredrik Wahlgren

Sonohal said:
Thanks, but still no luck. I do get the "not found" message though. Do the
columns have to have a particular format, ie text, general, number, etc.?
Click to expand...

Formatting only affects what you see, not the underlying value/text
/Fredrik
 
Top