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?

Answer 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

Answer 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!)

Answer 3

To solve this I used some VBA on a macro enabled button on the page labelled 'Update' so you could append the sheet as neccesary;

Private Sub CommandButton1_Click()
Dim Target, cell As Range
Set Target = Range(Range("A1"), Range("A65536").End(xlUp))

    Dim term, tag As String

        term = "cat"
        tag = "animal"
        For Each cell In Target
            If InStr(1, cell, term, 1) Then cell.Offset(0, 1).Value = tag
        Next cell

        term = "dog"
        tag = "animal"
        For Each cell In Target
            If InStr(1, cell, term, 1) Then cell.Offset(0, 1).Value = tag
        Next cell

        i = "car"
        k = "object"
        For Each cell In Target
            If InStr(1, cell, term, 1) Then cell.Offset(0, 1).Value = tag
        Next cell

        term = "plane"
        tag = "object"
        For Each cell In Target
            If InStr(1, cell, term, 1) Then cell.Offset(0, 1).Value = tag
        Next cell

        term = "sister"
        tag = "person"
        For Each cell In Target
            If InStr(1, cell, term, 1) Then cell.Offset(0, 1).Value = tag
        Next cell

    End Sub

The basic format of;

term= "cat"
tag = "animal"
For Each cell In Target
    If InStr(1, cell, i, 1) Then cell.Offset(0, 1).Value = k
Next cell

Can be copied and the variable values of term and tag changed for whatever search term and tags you would like without having to exist on the sheet.

It is also worth noting your IF equation is using a double negative, you can just use IF(SEARCH instead of IF(NOT(ISERROR( SEARCH.