Look up and return value based on more than one factor

[toMarsandBack]

I've been searching high and low for a formula that will return a value from a table based on multiple factors (seems to be some combination of IF and Vlookup formula). I looked through the forum to see if anyone has already posted a question similar to mine but I didn't find an exact match (is an excel formula as unique as a snowflake?) Anyways, with that being said, I understand that the people providing help on this forum have to take time out of their day to come up with answers so I'm more than willing to make a donation to the forum. I've spent way too much time on this and just need a good answer at this point.

Sheet 1

Player	Position	Team	Opponent	vs. player
John	PG	LAL	MEM	?????
Mark	SG	LAL	MEM	?????

<tbody>
</tbody>

Player	Position	Team	Opponent
Mike	PG	MEM	LAL

<tbody>
</tbody>

I need a formula that will use values in the Opponent and Position columns in sheet1 - in this case MEM and PG - and return the corresponding value - in this case Mike. Simply put, Mike is the Memphis PG that is guarding the Lakers PG.

 

[toMarsandBack]

The second table in my example (Mike PG MEM LAL) is located in sheet2.

 

[Marcelo Branco]

Welcome to Mr Excel

You need and array formula.

E2 of Sheet1
=IFERROR(INDEX(Sheet2!$A$2:$A$100,MATCH(1,IF(Sheet2!$B$2:$B$100=B2,IF(Sheet2!$C$2:$C$100=D2,1)),0)),"Not Found")

confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[Aladin Akyurek]

In E2 of Sheet1 control+shift+enter, not just enter:

=INDEX(Sheet2!$A$2:$A$10,MATCH($B2,IF(Sheet2!$C$2:$C$10=$D2,Sheet2!$B$2:$B$10),0))

 

[toMarsandBack]

worked perfectly! thank you both