Replacing a bunch of nested IFs

[fmartins]

Hi everyone.

I have a list of sentences in a column.
I need to check for particular strings within those sentences.
Depending on the string I find in each sentence (each sentence only has one of the strings I'm searching, I just don't know which) I want the cell with the formula to return a given second string.

With an example it's easier to explain:

Sentences
========
The quick brown fox jumps over the lazy dog
To be or not to be that is the question
Are you talking to me?

Strings to be searched within the sentences | Second strings to be returned if the the string is found within the sentences
(different strings might return the same value, although not on this example)
====================================================================
brown | Test sentence
question | Shakespeare
talking | Taxi Driver

I'm using, and it works, nested IFs. But it's far from elegant or easy to maintain:

=IF(ISNUMBER(FIND("brown";A1));"Test sentence";IF(ISNUMBER(FIND("question";A1));"Shakespeare";... and so on


How can I make this more elegant and using a list of strings to be searched + corresponding second strings to be returned?


Thank you in advance for you time.

[Pete_UK]

Suppose you put those words in F1, F2 and F3, and the strings to be returned in G1:G3, then you could have this formula in B1:

=INDEX(G:G,SUMPRODUCT(--(ISNUMBER(SEARCH(F$1:F$3,A1))),ROW(F$1:F$3)))

and copy this down. You should extend the range as shown in red if you have more words to search for. If none of the words are found then the formula will return zero.

Hope this helps.

Pete

[Pete_UK]

If you want to trap the zero for no matched words and return a message instead, then you can do this:

=IFERROR(INDEX(G$1:G$3,SUMPRODUCT(--(ISNUMBER(SEARCH(F$1:F$3,A1))),ROW(F$1:F$3))),"not present")

Again, change the ranges in red to suit your list.

Hope this helps.

Pete

[fmartins]

Thank you very much, Pete. In times like this I realize how little I know about Excel and its functions.

It worked. I just have to troubleshoot now why in _some_ rows I get the wrong result.

FWIW, we're talking about 500 lines with several types of sentences and I'm searching for 75 different strings to display 45 different resulting strings.

The purpose of it to classify bank account lines based on the description of each line (meal, supermaket, mortgage, insurance etc.).

[fmartins]

I found the problem. What is happening is that

=SUMPRODUCT(--(ISNUMBER(SEARCH(F$1:F$3,A1))),ROW(F$1:F$3))

is returning the sum of all rows indexes which include a matching string whereas a have to account for only the first one.

Can you help me, please? I've tried a few methods but I don't seem to be getting there.

Thank you.

[Pete_UK]

You said in your first post:

each sentence only has one of the strings I'm searching

which is why I was able to suggest that formula. However, if you have several matches then it won't work correctly, as you have found out.

Post an example workbook so I can see more of the variations that you have encountered.

Pete