Using VLOOKUP to Pull Data from Several Named Ranges

I have sheet that's using a ton of IF/THEN references and it's really driving me nuts. What I'd like to do is not have to create a new IF/THEN every time I add a new "team".

I have a sheet called "ROSTERS" which has 26 named ranges that have team rosters in them. I have scoresheets for each game of a tournament, I only know the first round and sometimes teams are added last minute, it's not really easy to just copy and paste. What I'd like to do is set two cells (one for each team in the scoresheets) that I can type in the team abbreviation and it fills the designated cells with the roster information. This is what I have currently:

=IF($H$1=Rosters!$D$1,Rosters!I21,IF($H$1=Rosters!$D$25,Rosters!I45,IF($H$1=Rosters!$D$49,Rosters!I69,IF($H$1=Rosters!$D$73,Rosters!I93,IF($H$1=Rosters!$D$97,Rosters!#REF!,IF($H$1=Rosters!$D$121,Rosters!I141,IF($H$1=Rosters!$D$145,Rosters!I165,IF($H$1=Rosters!$D$169,Rosters!I189,IF($H$1=Rosters!$D$193,Rosters!I213,IF($H$1=Rosters!$D$289,Rosters!I309,IF($H$1=Rosters!$D$313,Rosters!I333,IF($H$1=Rosters!$D$337,Rosters!I357,IF($H$1=Rosters!$D$361,Rosters!I381,IF($H$1=Rosters!$D$385,Rosters!I405,IF($H$1=Rosters!$D$409,Rosters!I429,IF($H$1=Rosters!$D$433,Rosters!I453,IF($H$1=Rosters!$D$457,Rosters!I477,IF($H$1=Rosters!$D$481,Rosters!I501,IF($H$1=Rosters!$D$505,Rosters!I525,IF($H$1=Rosters!$D$529,Rosters!I549,IF($H$1=Rosters!$D$553,Rosters!I573,IF($H$1=Rosters!$D$577,Rosters!I597,IF($H$1=Rosters!$D$601,Rosters!I621,IF($H$1=Rosters!$D$625,Rosters!I645,IF($H$1=Rosters!$D$649,Rosters!I669,IF($H$1=Rosters!$D$673,Rosters!I693,IF($H$1=Rosters!$D$697,Rosters!I717,IF($H$1=Rosters!$D$722,Rosters!I742,IF($H$1=Rosters!$D$747,Rosters!I767,IF($H$1=Rosters!$D$772,Rosters!I792,IF($H$1=Rosters!$D$797,Rosters!I817,IF($H$1=Rosters!$D$822,Rosters!I842,IF($H$1=Rosters!$D$847,Rosters!I867,IF($H$1=Rosters!$D$872,Rosters!I892,IF($H$1=Rosters!$D$897,Rosters!I917,IF($H$1=Rosters!$D$922,Rosters!I942,IF($H$1=Rosters!$D$947,Rosters!I967,IF($H$1=Rosters!$D$972,Rosters!I992,IF($H$1=Rosters!$D$997,Rosters!I429,IF($H$1=Rosters!$D$1022,Rosters!I1042,IF($H$1=Rosters!$D$1047,Rosters!I1067,"")))))))))))))))))))))))))))))))))))))))))

I want to really pare this down and as you can tell there are some #REF because teams were removed by accident, also, these cell references all need to change because I had to alter the rosters a bit. Abbreviations now fall in D1, D31, etc., but I'd like to make it so I don't have to update this string EVERY time I change the roster page or if I decide to create this for another sport with larger or smaller rosters.

Thanks,

Jarrod

Originally Posted by SportsAnnouncing

. . . I have a sheet called "ROSTERS" which has 26 named ranges that have team rosters in them. I have scoresheets for each game of a tournament, . . . What I'd like to do is set two cells (one for each team in the scoresheets) . . .

Your formula, reformatted.

=IF($H$1=Rosters!$D$1,Rosters!I21,
IF($H$1=Rosters!$D$25,Rosters!I45,
IF($H$1=Rosters!$D$49,Rosters!I69,
IF($H$1=Rosters!$D$73,Rosters!I93,
IF($H$1=Rosters!$D$97,Rosters!#REF!,
IF($H$1=Rosters!$D$121,Rosters!I141,
IF($H$1=Rosters!$D$145,Rosters!I165,
IF($H$1=Rosters!$D$169,Rosters!I189,
IF($H$1=Rosters!$D$193,Rosters!I213,
IF($H$1=Rosters!$D$289,Rosters!I309,
IF($H$1=Rosters!$D$313,Rosters!I333,
IF($H$1=Rosters!$D$337,Rosters!I357,
IF($H$1=Rosters!$D$361,Rosters!I381,
IF($H$1=Rosters!$D$385,Rosters!I405,
IF($H$1=Rosters!$D$409,Rosters!I429,
IF($H$1=Rosters!$D$433,Rosters!I453,
IF($H$1=Rosters!$D$457,Rosters!I477,
IF($H$1=Rosters!$D$481,Rosters!I501,
IF($H$1=Rosters!$D$505,Rosters!I525,
IF($H$1=Rosters!$D$529,Rosters!I549,
IF($H$1=Rosters!$D$553,Rosters!I573,
IF($H$1=Rosters!$D$577,Rosters!I597,
IF($H$1=Rosters!$D$601,Rosters!I621,
IF($H$1=Rosters!$D$625,Rosters!I645,
IF($H$1=Rosters!$D$649,Rosters!I669,
IF($H$1=Rosters!$D$673,Rosters!I693,
IF($H$1=Rosters!$D$697,Rosters!I717,
IF($H$1=Rosters!$D$722,Rosters!I742,
IF($H$1=Rosters!$D$747,Rosters!I767,
IF($H$1=Rosters!$D$772,Rosters!I792,
IF($H$1=Rosters!$D$797,Rosters!I817,
IF($H$1=Rosters!$D$822,Rosters!I842,
IF($H$1=Rosters!$D$847,Rosters!I867,
IF($H$1=Rosters!$D$872,Rosters!I892,
IF($H$1=Rosters!$D$897,Rosters!I917,
IF($H$1=Rosters!$D$922,Rosters!I942,
IF($H$1=Rosters!$D$947,Rosters!I967,
IF($H$1=Rosters!$D$972,Rosters!I992,
IF($H$1=Rosters!$D$997,Rosters!I429,
IF($H$1=Rosters!$D$1022,Rosters!I1042,
IF($H$1=Rosters!$D$1047,Rosters!I1067,
"")))))))))))))))))))))))))))))))))))))))))

Note the definite and likely errors,

IF($H$1=Rosters!$D$97,Rosters!#REF!,

IF($H$1=Rosters!$D$972,Rosters!I992,
IF($H$1=Rosters!$D$997,Rosters!I429,
IF($H$1=Rosters!$D$1022,Rosters!I1042,

I'd bet those should be Rosters!$I$117 in the 1st case and Rosters!$I$1017 in the 2nd case.

Up to Rosters!D697, the col D cells are multiples of 24 rows apart. After Rosters!D697, the col D cells are 25 rows apart. That may not matter. The Rosters!D:D cells and corresponding Rosters!I:I cells are all 20 rows apart (except for the sections highlighted above).

If the only cells in Rosters!D:D with team abbreviations are the cells in this formula, then you should be able to use

=INDEX(Rosters!$I$21:$I$1067,MATCH($H$1,Rosters!$D$1:$D$1047,0))

I've altered the Rosters sheet so that they are all 29 rows long with a 1 row space, which puts the top of each one a increments of 30 rows. The abbreviation for the teams would be in D1, D31, D61, etc. with the last team showing up at 751 and ending at 779.

Those errors are in there, I just haven't fixed them because I'd like to write a shorter script.
For the first team, VWM, the abbreviation is in D1, the roster is A3:G29
The second team, HSM, the abbreviation is in D1, the roster A33:G59
The Named Ranges are also the abbreviations so A3:G29 is named VWM

If I should move the abbreviation or change the Named Range, please let me know

In your script: =INDEX(Rosters!$I$21:$I$1067,MATCH($H$1,Rosters!$D$1:$D$1047,0))
I'm not 100% sure how the INDEX is working here because I wasn't good at it before so it's still confusing me now.

Originally Posted by SportsAnnouncing

. . . which puts the top of each one a increments of 30 rows. The abbreviation for the teams would be in D1, D31, D61, etc. with the last team showing up at 751 and ending at 779. . . . For the first team, VWM, the abbreviation is in D1, the roster is A3:G29 . . . If I should move the abbreviation or change the Named Range, please let me know

. . . =INDEX(Rosters!$I$21:$I$1067,MATCH($H$1,Rosters!$D$1:$D$1047,0))

I'm not 100% sure how the INDEX is working here . . . so it's still confusing me now.

If you want to simplify your formulas, you may need to learn some things which may be unfamiliar at first.

If you've now changed the layout to 1 team every 30 rows, that's good. If team abbreviation would be in col D beginning with cell D1, good. If the corresponding range were A3:G29, that's good.

MATCH($H$1,Rosters!$D$1:$D$751,0) returns the row index at which the team abbreviation in H1 matches a cell in Rosters!D1:D751.

Example for INDEX: Q77:X99 represents the rectangular range of cells from Q77 right and down to X99. INDEX(Q77:X99,i,j) refers to the cell in the ith row and jth column in the range Q77:X99. If i were 17 and j were 4, that'd be cell T93.

INDEX(Rosters!$A$3:$A$753,MATCH($H$1,Rosters!$D$1:$D$751,0)) is the cell in the Rosters worksheet 2 rows below and 3 columns to the left of the cell in Rosters!D1:D751 matching cell H1 from your original formula.

INDEX(Rosters!$G$29:$G$779,MATCH($H$1,Rosters!$D$1:$D$751,0)) is the cell in the Rosters worksheet 28 rows below and 3 columns to the right of the cell in Rosters!D1:D751 matching cell H1 from your original formula.

INDEX(Rosters!$A$3:$A$753,MATCH($H$1,Rosters!$D$1:$D$751,0)):INDEX(Rosters!$G$29:$G$779,MATCH($H$1,Rosters!$D$1:$D$751,0)) is the range of cells from the topmost cell in col A to the bottommost cell in col G for the team in Rosters!D1:D751 matching cell H1 from your original formula.

If you only need particular cells in Rosters!I1:I779 (implied by your original formula), you could use the formula I suggested in my previous reply. If you want to perform VLOOKUP calls on Ax:Gy ranges for particular teams, you can use

=VLOOKUP(something,INDEX(Rosters!$A$3:$A$753,MATCH($H$1,Rosters!$D$1:$D$751,0)):INDEX(Rosters!$G$29:$G$779,MATCH($H$1,Rosters!$D$1:$D$751,0)),col_num,0)

This is wonderful, thank you for the explanations. I'm learning, I'm always open to learning.

In the roster sheet that would call for the data, the names start on the fourth row. For the home team, A4:G29 (H1 is the cell where the abbreviation would go), visiting team is N4:T29 (U1 is the cell where the abbreviation would go). The 0 at the end of the index screen, does that indicate the column similar to VLOOKUP?

This information is used on a starting line-up sheet that I've already worked out with VLOOKUP (this is how I learned how to use VLOOKUP honestly).