Lookup from multiple rows and columns

[JoeJaycee]

I’m using MS Excel 2010

Here’s what I’m trying to do:

IF Team Name (A4) matches Team Name on (worksheet Teams)Teams!C2:R2, then return Players 1,2,3 & 4 for that Team.

Here is what I tried, but it’s not returning the correct answers I’m looking for:

=LOOKUP(A4,Teams!$C$2:$R$2,Teams!$C$4:$R$4)&", "&LOOKUP(A4,Teams!$C$2:$R$2,Teams!$C$5:$R$5)&", "&LOOKUP(A4,Teams!$C$2:$R$2,Teams!$C$6:$R$6)&", "&LOOKUP(A4,Teams!$C$2:$R$2,Teams!$C$7:$R$7)


This is my first time posting. Your help would be appreciated!

[Special-K]

Try

in 4Man_Bracket_1
in B4

=INDEX(Teams!$C$2:$R$7,3,MATCH(A4,Teams!$C$2:$R$2,FALSE))&", "&INDEX(Teams!$C$2:$R$7,4,MATCH(A4,Teams!$C$2:$R$2,FALSE))&", "&INDEX(Teams!$C$2:$R$7,5,MATCH(A4,Teams!$C$2:$R$2,FALSE))&", "&INDEX(Teams!$C$2:$R$7,6,MATCH(A4,Teams!$C$2:$R$2,FALSE))

copy it down

Note you want it copied to B4 B6 B8 etc only the even numbers not a straight copy down the column

[JoeJaycee]

Thank you. Works perfectly!