IF THEN formula help

[s0nginmyheart]

Hey all. Wondering if someone can help me. I was googling IF and THEN tutorials but couldn't really find one that would help with what I'm specifically trying to do.


Trying to have a formula that would do this:

IF CELL A1 = A, B, C, D, E... then X
IF CELL A1 = F, G, H, I, J (etc)... then Y

These are not numeric values, they are words...

For example

IF CELL A1 = CAT, DOG, BIRD... then PET
IF CELL A1 = CAR, TRUCK, TIRE... then AUTO

I've got about 20 items to put in the "IF" part, and about 10 different "THEN" results... is this possible to have that many?

Thanks in advance!

[Pete_UK]

You could do it like this:

=IF(ISNUMBER(SEARCH(A1,"Cat,Dog,Bird")),"Pet",IF(ISNUMBER(SEARCH(A1,"Car,Truck,Tire")),"Auto",""))

Just add more words within the quotes, as required (you don't strictly need the commas between the words, but it helps to make the words more recognisable).

Hope this helps.

Pete

[s0nginmyheart]

Thank you. How do I add a 3rd, 4th 5th (and so on) category though?

For example:

IF Cell A = Blue, Red, Green, Yellow...... THEN color
IF Cell A = Couch, Chair, Table...... THEN house

I'll have about 10 different THEN/categories... not sure if it can be this long?


My actual formula (THEN) is going to have units of measure: EA, SY, LF, SF, SET, etc.

[Pepe Le Mokko]

In that case perhaps create a small table somewhere ( say AA1:AB10) with search terms in the 1st column and results in the second

And use a VLOOKUP like =VLOOKUP(A1,$AA$1:$AB$10,2,0) or an INDEX/MATCH combination to retrieve the values

[Pete_UK]

Why don't you attach a sample workbook which will show all the variations that you want to cover, then we can cater for them all in one go.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it is broken on this forum.

Hope this helps.

Pete