Formula Broken: Need to return Sheet3:C"Address" when Sheet3:C"Name" is in Sheet1:C"Name"

=IF(OR(ISNUMBER(SEARCH(Elliott!A:A,B2))),INDEX(Elliott!D:D,MATCH(TRUE,ISNUMBER(SEARCH(Elliott!A:A,B2)),0)),"N/A")

The results I received were different from what I expected:

What results did you expect? I want to return “address” field on Sheet 3 (Elliott) when “name” in Sheet 3 (Elliott) is contained within “name” in sheet 1.

What results did you actually receive from the code/formula? All records return #N/A (no value available) or N/A (as I have defined to return when FALSE)

In what way are those 2 things different? I am not getting “Address” from sheet 3 returned when “Name” on sheet 3 is within “name” on sheet 1



Which sheets/columns/rows contains which data?
1. Sheet 1 has four Columns (FFL,NAME,Address, Phone), Around 70k rows
2. Sheet 2 Has 1 column of names that have been removed from Sheet 1 – not relevant to issue.
3. Sheet 3 (Elliott) has four Columns (Name, FFL, Phone, Address), nearly 200k rows



***The overall goal is to remove all records from sheet 1 that are present in sheet 3. I am trying to do this first by name (but name on sheet one is a combo of two possible names), then by FFL, then phone – address is to verify it is indeed a true match.***


Here is 1 example that should result with an address from sheet 3:

Sheet 1: (FFL,NAME,Address, Phone)
571035027K04592 1 STOP PAWN SHOP LLC\1 STOP PAWN SHOP 1985 KROGER DR STE C1\WEST MEMPHIS 8703944589

Sheet 3: (Name, FFL, Phone, Address) – want to find yellow within green and then return address from sheet 3
1 STOP PAWN SHOP 571035027K04592 8703944589 1985 KROGER DR STE C1\WEST MEMPHIS


I have tried INDEX MATCH as well with wildcards for Sheet3:C"Name"

The information on the spreadsheet is publicly published information, and thus not confidential.

Thanks.