Match two columns in one file with two from another, returning a third column from file 2

[rimhoff]

Hi Everyone,

I have been trying to figure out different combinations of INDEX, MATCH, IF, AND, etc. to get a solution with no success and would really appreciate some help.

Basically, I have a list of first and last names (in separate columns) in one file and need to look up the email for that person in another file, matching on the first and last name columns. I attached an example of my data with the two sheets representing the two files and pasted a copy here as well.

File 1
First Name Last Name Email
x x Need to lookup
y y Need to lookup
z z Need to lookup

File 2
First Name Last Name Email
a a [email protected]
…
z z [email protected]


Any ideas on how to do this?

Thanks in advance.

[Tony Valko]

Try this...

Data Range

	A	B	C
1	First Name	Last Name	Email
2	x	x	[email protected]
3	y	y	[email protected]
4	z	z	[email protected]

This array formula** entered in C2 and copied down:

=INDEX('File 2'!C$2:C$9,MATCH(1,('File 2'!A$2:A$9=A2)*('File 2'!B$2:B$9=B2),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[rimhoff]

This worked perfect, thanks a lot!

[Tony Valko]

You're welcome. Thanks for the feedback!