VLOOKUP for a cell with both letters and numbers

Sonohal · 04-07-2005, 04:06 PM

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 · 04-07-2005, 05:06 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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

**BenjieLop** · 04-07-2005, 05:09 PM

Originally Posted by 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

Try this syntax ...

=vlookup(lookup_value,table_array,column_index_number,FALSE)

and see if it will work for you. I am assuming that you do not have the "FALSE" argument in your formula.

Hope this works ...

Sonohal · 04-07-2005, 06:06 PM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 · 04-07-2005, 06:06 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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 · 04-07-2005, 07:06 PM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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
> >>
> >>
> >>
>
>
>

Fredrik Wahlgren · 04-07-2005, 07:06 PM

"Sonohal" <[email protected]> wrote in message
news:[email protected]...
> 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

Sonohal · 04-08-2005, 10:06 AM

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

"Fredrik Wahlgren" wrote:

>
> "Sonohal" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>