Lookup, match , list names

[Killer17]

Say Hey!

I need some assistance on creating a formula that will Match "W1 Sheet" Team number look this number under Schedule Sheet then list names under "W1 Sheet".

I have added a simple sheet.

Thanks Ahead

[carsto]

see the attached

as long as all your data is always in the format on Schedule this should work.

comment:
on Schedule:
Team No = D, Name = C (1 col offset)
Team No = K, Name = I (2 cols offset)

[Killer17]

Hey Carsto

Thanks for the help on the formula.

I gave it a try and it seems to work great I do have one question what would I have to do towards the formula if I wanted to expand the Schedule team from 4 to 14 teams.

The layout would be the same 2 rows 7 teams across?

Thanks again

[Killer17]

I'm still having problems in getting this formula to work with 14 teams worksheet works fine with 4 teams only.

=IF(ISERROR(MATCH(B4,Schedule!D:D,0)), INDEX(Schedule!I:I,MATCH(B4,Schedule!K:K,0)+1,1),INDEX(Schedule!C:C,MATCH(B4,Schedule!D:D,0)+1,1))

=IF(ISERROR(MATCH(B4,Schedule!D:D,0)), INDEX(Schedule!I:I,MATCH(B4,Schedule!K:K,0)+2,1),INDEX(Schedule!C:C,MATCH(B4,Schedule!D:D,0)+2,1))

=IF(ISERROR(MATCH(B4,Schedule!D:D,0)), INDEX(Schedule!I:I,MATCH(B4,Schedule!K:K,0)+3,1),INDEX(Schedule!C:C,MATCH(B4,Schedule!D:D,0)+3,1))

=IF(ISERROR(MATCH(B4,Schedule!D:D,0)), INDEX(Schedule!I:I,MATCH(B4,Schedule!K:K,0)+4,1),INDEX(Schedule!C:C,MATCH(B4,Schedule!D:D,0)+4,1))

I have created another simple sheet

Thanks

[contaminated]

U can doo it by using simple CHOOSE() formula/
Here's examle.

Please Login or Register  to view this content.

PS: Copy this formula and paste it in A8. Then select A8:A11 (in all these cells MUST be the same formula) press F2 then Ctrl + Shift + Enter

[Killer17]

Thanks Contaminated