Lookup range of words in range of sentences

Question

Based on the following example:

I want to look up for words from the $D$2:$D$6 range in sentences from the $A$2:$A$8 range and use the value from the side column (here Tag). So far I'm doing this:

B2 formula
------------------
=IF(NOT(ISERROR(SEARCH($D$2,A2))),$E$2,
IF(NOT(ISERROR(SEARCH($D$3,A2))),$E$3,
IF(NOT(ISERROR(SEARCH($D$4,A2))),$E$4,
IF(NOT(ISERROR(SEARCH($D$5,A2))),$E$5,
IF(NOT(ISERROR(SEARCH($D$6,A2))),$E$6,
"other"
)
)
)
)
)

Although this works, my formula is as long as the number of lookup words I have, whereas I'd like to use a range to make it cleaner and easier to maintain (i.e. now if I add a lookup word I need to add an IF statement for that word in my formula).

Is there a way to replicate above solution with a range-based formula?

Solution 1

You can use this ordinary formula in B2 copied down

=LOOKUP(2^15,SEARCH(D$2:D$6,A2),E$2:E$6)

See discussion and more about that formula here

Solution 2

You need to use array formulae. In cell B2, type:

=IFERROR(OFFSET($E$1,MATCH(1E+37,SEARCH(D$2:D$6,A2),1),0),"other")

and press Ctrl+Shift+Enter to enter it as an array formula.

It uses the SEARCH function to match each word in your list, and finds the last one which is less than a "big number" (here 1E+37). If there is more than one match, it will find the one which is furthest down the list (e.g. "a red car and a black cat" will return "object"). In the case that none is found, the IFERROR bit gives you "other".

Of course, you'll have to extend it as your list gets longer. (Always remember to Ctrl+Shift+Enter when you edit!)

Comments

[Max]

Thanks, but it looks like your VBA is going to require even more maintenance than my formula. Also, I disagree with IF(SEARCH being the same as IF(NOT(ISERROR( SEARCH, if the search fails, the former returns #VALUE! whereas the latter returns the IF false-case value: see for yourself =IF(SEARCH("a","b"),"found","not found") and =IF(NOT(ISERROR(SEARCH("a","b"))),"found","not found")

[Max]

I believe you meant "In B2, type:"

[Max]

To save me from having to update the formula every time I modify my range, I have replaced D$2:D$6 with INDIRECT and COUNTA which is working fine: =IFERROR(OFFSET($E$1,MATCH(1E+37,SEARCH(INDIRECT("D$2:D$"&CO‌​UNTA($D:$D)+1),A2),1‌​),0),"other")

[benshepherd]

Yes; I thought about mentioning something like that but I didn't like to overcomplicate it since you didn't specify whether it wouldn't be regularly updated. OFFSET with a number of rows might be more 'elegant' than INDIRECT. I also like to use dynamic named ranges. Many ways to skin a cat :)

[Max]

I prefer this one over @bensheperd's solution as I don't have to use an array formula. To get rid of the static range I have =LOOKUP(2^15,SEARCH(INDIRECT("D$2:D$"&COUNTA($D:$D)+1),A2),E‌​$2:E$6). What's interesting is that although I have E$2:E$6 static, it does work (i.e. it looks up for values on cells below E$6 :)

[barry houdini]

I would convert columns D and E in to a table [Select the range, Insert > Table and use "words" and "tags" in the header row], then your formula can be this: =LOOKUP(2^15,SEARCH(Table[words],A2),Table[tags]) - If you add rows to the table those will automatically be incorporated

[J_D]

Ah yes, I forgot Search returns a number. I disagree about the maintenance, the code is easy to append and can scale easily to larger datasets however the Houdini approach certainly is more elegant.