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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Player[/TD]
[TD]Position[/TD]
[TD]Team[/TD]
[TD]Opponent[/TD]
[TD]vs. player[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]PG[/TD]
[TD]LAL[/TD]
[TD]MEM[/TD]
[TD]?????[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]SG[/TD]
[TD]LAL[/TD]
[TD]MEM[/TD]
[TD]?????[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Player[/TD]
[TD]Position[/TD]
[TD]Team[/TD]
[TD]Opponent[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD]PG[/TD]
[TD]MEM[/TD]
[TD]LAL[/TD]
[/TR]
</tbody>[/TABLE]

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