VLOOKUP for a cell with both letters and numbers

[Guest]

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

 

[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

 

[Guest]

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.

 

[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

 

[Guest]

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.?

 

[Fredrik Wahlgren]

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.?

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

 

[Guest]

Ok thanks. That is what I thought, but you never know.