I posted this in the Office 2003 fo

[sdiverdan]

I posted this in the Office 2003 forum and did not get a response so I'm trying this forum. I'm really stumped.

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 with their scores
PlayerB team 1 position 2 with their scores
etc..

Team 2
PlayerC team 2 position 1 with their scores
PlayerD team 2 position 2 with their scores
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". If no player is found I want the cell to contain "Vacant"

Dazed again! %-)


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))

http://www.geniusmedia.ca

 

[xlhelp]

Please note: I only used four names instead of five from sheet1.

http://www.geniusmedia.ca