Populate data in cell by looking at another cells data

[gAZZA]

Hi Everyone,

Hope I find you well.

I'm not even sure how to go about this, so I hope that
someone can shed some light.

I have a serial number in one cell eg '80199DD270238'
where 'DD' is code for another value, in this case 'DD'
= 'BLUE'.

How can I automatically populate a cell with the
value 'BLUE' by looking at the serial number.

Many thanks for any help you can provide.

Best Regards

Gazza

 

[Frank Kabel]

Hi
if the characters are alsways at the same place use
=IF(MID(A1,6,2)="DD","Blue","other color")

if they could be at different positions tryx
=IF(ISNUMBER(FIND("DD",A1)),"Blue","other color")

 

[Gazza]

Hi Frank,

Thanks for that, it works great.

Expanding on that a bit more, is it possible to have
multiple IF statements so that I can check for several
different codes within the serial number and then have
several different values for each of the codes?

Cheers

Gazza

 

[Gazza]

Hi Frank,

It's OK, I've sorted it. Thanks for your help.

Best Regards

Gazza

 

[daniels012]

You can nest If functions within one another in the Spot he has "othe
color" just enter a new if function.
You could also use a lookup table. Even have the information o
another tab that has the lookup table.

Frank?
How does this the ISNUMBER Function work since "DD" is not a number???

Michae

 

[Dave Peterson]

I'm not Frank, but here's his formula:

=IF(ISNUMBER(FIND("DD",A1)),"Blue","other color")

So =isnumber() isn't testing the DD value at all. It's testing whether the
=FIND() function returned a number. (=Find() returns a number if it finds the
string. It returns #VALUE! (an error) if the substring isn't found.

And there's a function called =search() that is not case sensitive. DD, dD, Dd,
dd would all be found with Search. Only DD with Find.