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

this example should explain question clearly

i have huge table of values (50,000+ rows) in format

phrase 1   | phrase 2   | value sun        | blue       | north moon       | green      | south star       | red        | west 

i have list of phrases in format (again 50,000+ rows)

                                       b          1| moon fun light green   |          2| star amazing ball red  |          3| sun cat inside blue    |          4| star dog red watch     | 

i need search along following lines - search a1 if find row both phrase 1 & phrase 2 present return corresponding value

below example of how results look. i.e. search has checked see if of word combos present in top table , returned appropriate result if possible. there won't issue of 2 sets of positives i.e. moon fun , light green. real values artist name , track name combinations.

                                       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 search full of other text i.e. cell "the moon out tonight on green hill". need search phrase table, see if find combo of moon & green , if return associated value.

• i need have multiple criteria in search single search term doesn't narrow results enough, of words i'm searching general or parts of other words (i.e. off part of offline) returns wrong results searching off , term simultaneously resolves issue.

• the list of phrases isn't in set format, can't exact values splitting columns i.e. it's not "moon - green"

• the closes have come solving using formula:

  if(and(isnumber(search(c2,e2)),isnumber(search(d2,e2))),b2,"")

  where c2 & d2 values search, e2 box search in & b2 value return. problem formula i'm limited searching 1 combination @ time i.e. check c2 & d2 rather saying check array of c2:c100 & d2:d100

  i've tried making formula array no luck.

• i've tried formula:

  =iferror(lookup(2, 1/(isnumber(search($l$7:$l$8, e2))), $k$7:$k$8),"")m

  but in situation can handle 1 critera (i.e. search phrase 1) not both phrase 1 , 2. $l$7:$l$8 list search, e2 box search in, $k$7:$k$8 corresponding value return.

hopefully enough go on.

if happy add column each dataset, work:

• add index column on first table of values containing number going 1 50,000+

• add array formula on phrase sheet

  {=sum(isnumber(search(l$2:l$50000,e2))*isnumber(search(m$2:m$50000,e2))*p$2:p$50000)} 

  (where l , m columns containing phrases , p index column)

• use result of column in lookup.

as suggested below, if can't (or don't want to) add column first worksheet, change 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 return correct entry value column.