Formula for returning value where first word in one column matches AND last four digits of another column does

Question

So, I have a excel sheet with 100.000+ names, phone numbers and emails and another sheet with names and the last four digits of phone numbers, for ie.

In sheet 1

A || B
Johnny Applesauce || xxxx1934
Jane Delaney Applesauce || xxxx3221

and in sheet 2 ('full')

A || B || C || D
ID || Johnny Applesauce || 45191934 || [email protected]
ID2 || David Useless || 48123221 || [email protected]
ID3 || Jane Applesauce || 32193221 || [email protected]
(and 99,999+ other people)

How can I make a formula in sheet 1 that compares with the 'full' list of people in sheet 2 and will return the corresponding email address if and only if

1. The first word of the name is the same
2. The last four digits of the phone number are the same

I've been trying to do something like =INDEX(full.D$2:D$166856;MATCH(RIGHT(B2;4)&LEFT(A2;FIND(" ";A2)-1);RIGHT(full.C$2:C$166856;4)&LEFT(full.B$2:B$166856;FIND(" ";full.B$2:B$166856)-1);0)) but I'm having trouble getting it to work properly - I just get a bunch of #N/A and #VALUE!

Answer 1

In Excel, using Tables and structured references, you can return the emails of the matching data with this formula (normally entered)

=INDEX(fullTable,AGGREGATE(14,6,(LEFT([@Name],4)=LEFT(fullTable[Name],4))*(RIGHT([@Phone],4)=RIGHT(fullTable[Phone],4))*ROW(fullTable),1)-ROW(fullTable[#Headers]),4)

fullTable is the table you have on sheet 2

The way it is written it will return the last match if there are multiple matches. It can also be written to return the first match, or even multiple matches in multiple columns. I leave those changes to you.