Looking up values within a range of cells

Question

Suppose I have the following data table in Excel

Company Amount  Text
Oracle  $3,400  330 Richard ERP
Walmart $750    348 Mary ERP
Amazon  $6,880  xxxx Loretta ERP
Rexel   $865    0000 Mike ERP
Toyota  $11,048 330 Richard ERP

I want to go through each item in the "Text" column, search the item against the following range of names:

Mary 
Mike 
Janine 
Susan 
Richard 
Jerry 
Loretta 

and return the name in the "Person" column, if found. For example:

Company Amount  Text    Person
Oracle  $3,400  330 Richard ERP Richard
Walmart $750    348 Mary ERP    Mary
Amazon  $6,880  xxxx Loretta ERP    Loretta
Rexel   $865    0000 Mike ERP   Mike
Toyota  $11,048 330 Richard ERP Richard

I've tried the following in Excel which works:

=IF(N2="","",
IF(ISNUMBER(SEARCH(Sheet2!$A$1,N2)),Sheet2!$A$1,
IF(ISNUMBER(SEARCH(Sheet2!$A$2,N2)),Sheet2!$A$2,
IF(ISNUMBER(SEARCH(Sheet2!$A$3,N2)),Sheet2!$A$3,
....

Where $A$1:$A$133 is my range and N2 is the "Text" column values; however, that is a lot of nested code and apparently Excel has a limit on the number of nested IF statements you can have.

Is there a simpler solution (arrays? VBA?)

Thanks!

Answer 1

Use the following formula:

=IFERROR(INDEX(Sheet2!A:A,AGGREGATE(15,6,ROW(Sheet2!$A$1:INDEX(Sheet2!A:A,MATCH("zzz",Sheet2!A:A)))/(ISNUMBER(SEARCH(Sheet2!$A$1:INDEX(Sheet2!A:A,MATCH("zzz",Sheet2!A:A)),N2))),1)),"")