Multiple IF formulas help needed

[Zozika]

Dear All,

I've encountered an issue in Excel two days ago and ever since I'm trying to find the solution, read tremendous amount of articles and forum threads but none of them seems to be able to help.
My problem is the following:
I've got one column (let's call it col.A) with several lines of data, each cell is filled with a lots of characters and one or maybe two key words in them. I'd like to assign a short identification (it can be a word, or number, whatever) to each categories represented by the key words. However I've got more than 15 types of key words and a simple IF formula can't help me in the case of more than seven.
I've tried to name one string of IF formulas and another, then put them together into one master formula as I read some places, but that still doesn't work as it only gives the result I need if it's in the second formula string. If the result should be something that's in the first string, then the result comes back as the "if not"-value of the second string.

This is the master formula I used:
=IF(A1=OneToSix,OneToSix,SevenToMany)


Do you know any other way to solve this with 15+ arguments and the same amount of answers?

Many thanks in advance,
Zoli

[vlady]

Moderator's Note:
Welcome to the forum.
Post a sample workbook with your expected outcome to better explain.

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[benishiryo]

hi Zoli, welcome to the forum. it's always good to upload a sample Excel file to illustrate. i've done it for you this time. see if it meets your needs.

so i put the keywords in G2:G4. this would be my formula to see if those keywords are found in A1
=IFERROR(LOOKUP(2^15,SEARCH($G$2:$G$4,A1),$G$2:$G$4),"Not found")

[Zozika]

Hi All,

Thanks for the answers so far, I attach a sample workbook. The cells in column A are almost like the ones I need to evaluate.
The green result cells are OK, the red are very not OK. The orange ones are correct but irrelevant.

Thank you,
Zoli

[Zozika]

Wow, thank you very much, the formula worked! I really appreciate the help!

[MINZO]

Sorry to barge in...

I have a question, I think this may help me with one of my problems. What does the 2^15 stand for in the formula
=IFERROR(LOOKUP(2^15,SEARCH($G$2:$G$4,A1),$G$2:$G$4),"Not found")

[Zozika]

Yep, I'd like to know that too!

[benishiryo]

@MINZO:
here's rule number 2 of the forum:

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread. You may include up to 3 links to other URLs in a single post, no more, so only link to the relevant pages

but i'll explain this for the benefit of the OP. 2^15 gives you 32768. the max number of characters in a cell is 32767. if you know LOOKUP, it searches for the exact number. if it cannot find one, it goes back to the number below it. so your character can be anywhere from the 1st to 32767th position. the SEARCH will return the position of the text you are looking for & LOOKUP will pick up that number by using 2^15. this is using my eg from B1:
=IFERROR(LOOKUP(2^15,SEARCH($G$2:$G$4,A1),$G$2:$G$4),"Not found")

if you select the range with red font & press F9 to calculate, you'll notice this:
=IFERROR(LOOKUP(2^15,{8;#VALUE!;#VALUE!},$G$2:$G$4),"Not found")
that is "banana" found in the 8th character position of A1. 2^15 will pick up 8 & ignore the VALUE (where Grapes & Oranges were not found). it then picks the text form G2:G4 accordingly.