VLOOKUP for a cell with both letters and numbers
 

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

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

Regards

Trevor


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

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

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

Regards

Trevor


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

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


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

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

Regards

Trevor


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

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

"Trevor Shuttleworth" wrote:

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


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

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

Regards

Trevor


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

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

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

"Fredrik Wahlgren" wrote:


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