Lookup - text and numbers

[Annette]

I have a table that formally contained only numbers, now we have one number
with text characters throwing off my formula for return values. This is the
current formula -

=IF(L19="","",CONCATENATE(LOOKUP(L19,Products!$B$2:$B$111,Products!$G$2:$G$111),"
",LOOKUP(L19,Products!$B$2:$B$111,Products!$D$2:$D$111),"
",LOOKUP(L19,Products!$B$2:$B$111,Products!$E$2:$E$111),"
",LOOKUP(L19,Products!$C$2:$C$111,Products!$F$2:$F$111)))

Is there a way to change this to recognize there could be text and numbers
or do I have to change the tables to be all text?

 

[ShaneDevenshire]

Hi Annette,

The prefered way would be to convert all the items to text or numbers, but
you don't need to.

It's not clear where the text/number are located - in L19 or in the first
column of each of the table ranges. More importantly it's not clear what you
mean by throwing off you formula? For Lookup to work the data in the B2:B111
range must be sorted - at least, that is the numeric data is you are looking
up numbers. The numbers can be interspersed with text as long as the numbers
are still in Ascending order.

It might be that what you are suggesting is that some of the text are
numbers entered as text? We need more info.

 

[Annette]

Okay, I wondered if I provided enough. Here's more information. My product
numbers have always been ##.####.###.### ... they were always numbers, but
they through in a loop today and indicated the last three numbers were now
going to be text.
Prior example: 24.0827.839.833
New example: 24.0827.839.US3

The cells were formatted as above in custom, but now I'm lost. They were in
numeric order and are still in numeric order, but the 'US' is throwing off
other cells that look at the L19 - which contains the basis of other
formulas to look at in various places on the spreadsheet.

Products sheet contacts the numbers in numeric order, the cell final result
will list is looking at a side column from the product list. When I enter
the new number with the text, N/A#. I changed the format to text on the
product list, but that didn't resolve the N/A#.