Another Excel index/match or vlookup problem 1

[sdiverdan]

I'm stuck again and this time it's turing my hair gray! I have two sheets (call it Sheet1 & sheet2).

Sheet1 is:
Name Team Position
Bob 2 1
Dan 5 3
George 1 2
Mac 1 1
Ted 2 2

this sheet is sorted by name.

Sheet2 is a report that looks like:
Team 1
PlayerA team 1 position 1
PlayerB team 1 position 2
etc..

Team 2
PlayerC team 2 position 1
PlayerD team 2 position 2
etc..

I'm looking for a formula that lookups the team # and position # for the report and returns the player's name. So in this simple example. PlayerA is a index/match or whatever finding the player of team 1 position 1 which would be Sheet1!A5 or 'Mac'.

Dazed again!


Dan

 

[sdiverdan]

oh... one more thing if the team/position is not there I want to return "Vacant" versus a player's name.

Dan

 

[xlhelp]

Once you have type the following formula, use CTRl-SHIFT-ENTER to enter it as an array formula

=INDEX(Sheet1!$A$2:$A$5,MATCH(B2,IF(Sheet1!$C$2:$C$5=C2,Sheet1!$B$2:$B$5),0))

and if you want to add "Vacant" to it then it becomes

=IF(ISERROR(=INDEX(Sheet1!$A$2:$A$5,MATCH(B2,IF(Sheet1!$C$2:$C$5=C2,Sheet1!$B$2:$B$5),0)),"Vacant",=INDEX(Sheet1!$A$2:$A$5,MATCH(B2,IF(Sheet1!$C$2:$C$5=C2,Sheet1!$B$2:$B$5),0)))

http://www.geniusmedia.ca

 

[xlhelp]

This proves I am trigger happy. Too quick to hit submit. the equal signs in front of index in both instances in the second formula have to go.

http://www.geniusmedia.ca

 

[sdiverdan]

thanks xlhelp! I had to also put in an extra ')' before the "Vacant" and viola it works beautifully!


Dan