Search a cell for multiple critera from a list and return a corresponding value from the list

Question

This example should explain my question clearly

I have a huge table of values (50,000+ rows) in this format

PHRASE 1   | PHRASE 2   | VALUE
Sun        | Blue       | North
Moon       | Green      | South
Star       | Red        | West

I have a list of phrases in the format (again 50,000+ rows)

                      A                  B
         1| Moon Fun Light Green   |
         2| Star Amazing Ball Red  |
         3| Sun Cat Inside Blue    |
         4| Star Dog Red Watch     |

I need to search along the following lines - search A1 if you find a row where both PHRASE 1 & PHRASE 2 are present return the corresponding VALUE

Below is an example of how I would like the results would look. I.e. the search has checked to see if any of the word combos are present in the top table and returned the appropriate result if possible. There won't be an issue of two sets of positives i.e. Moon Fun and Light Green. The real values are all artist name and track name combinations.

                     A                   B
        1| Moon Fun Light Green    |   South   |
        2| Star Amazing Ball Red   |   West    |
        3| Sun Cat Inside Blue     |   North   |
        4| Star Dog Red Watch      |   West    |

A few notes:

• The list of phrases to search are full of other text as well I.e. the cell will be the "The Moon was out tonight I was on the green hill". We need to search the phrase table, see if we find the combo of Moon & Green and if we do return the associated value.

• I need to have multiple criteria in the search as a single search term doesn't narrow results enough, some of the words I'm searching are to general or are parts of other words (i.e. OFF is part of OFFLINE) which returns the wrong results where as searching OFF and another term simultaneously resolves that issue.

• The list of phrases isn't in a set format, so I can't really exact all the values by splitting the columns i.e. it's not all "Moon - Green"

• The closes I have come to solving this is using this formula:

  IF(AND(ISNUMBER(SEARCH(C2,E2)),ISNUMBER(SEARCH(D2,E2))),B2,"")

  Where C2 & D2 are the values to search, E2 is the box to search in & B2 is the value to return. The problem with this formula is that I'm limited to searching 1 combination at a time I.e. check C2 & D2 rather than saying check an array of C2:C100 & D2:D100

  I've tried making that formula into an array with no luck.

• I've also tried this formula:

  =IFERROR(LOOKUP(2, 1/(ISNUMBER(SEARCH($L$7:$L$8, E2))), $K$7:$K$8),"")m

  But in this situation it can only handle one critera (i.e. only search for Phrase 1) not both Phrase 1 and 2. $L$7:$L$8 is the list to search, E2 is the box to search in, $K$7:$K$8 is the corresponding value to return.

Hopefully that is enough to go on.

Answer 1

If you are happy to add an extra column to each dataset, then this will work:

• Add an index column on your first table of values containing just a number going from 1 to 50,000+

• Add this array formula on your phrase sheet

  {=SUM(ISNUMBER(SEARCH(L$2:L$50000,E2))*ISNUMBER(SEARCH(M$2:M$50000,E2))*P$2:P$50000)}
  

  (where L and M are the columns containing your phrases and P is the index column)

• Use the result of this column in a lookup.

As suggested below, if you can't (or don't want to) add a column to your first worksheet, you could change the formula to

{=SUM(ISNUMBER(SEARCH(L$2:L$50000,E2))*ISNUMBER(SEARCH(M$2:M$50000,E2))*ROW(L$2:L$50000))}

And use OFFSET to return the correct entry from your VALUE column.