+ Reply to Thread
Results 1 to 5 of 5

Using VLOOKUP to Pull Data from Several Named Ranges

  1. #1
    Registered User
    Join Date
    02-05-2020
    Location
    Herndon, Va
    MS-Off Ver
    365
    Posts
    5

    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

  2. #2
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,154

    Re: Using VLOOKUP to Pull Data from Several Named Ranges

    Quote Originally Posted by SportsAnnouncing View Post
    . . . 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))

  3. #3
    Registered User
    Join Date
    02-05-2020
    Location
    Herndon, Va
    MS-Off Ver
    365
    Posts
    5

    Re: Using VLOOKUP to Pull Data from Several Named Ranges

    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.

  4. #4
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,154

    Re: Using VLOOKUP to Pull Data from Several Named Ranges

    Quote Originally Posted by SportsAnnouncing View Post
    . . . 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)

  5. #5
    Registered User
    Join Date
    02-05-2020
    Location
    Herndon, Va
    MS-Off Ver
    365
    Posts
    5

    Re: Using VLOOKUP to Pull Data from Several Named Ranges

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VLOOKUP in Data validation based on named ranges
    By milo1984 in forum Excel General
    Replies: 4
    Last Post: 12-15-2016, 09:12 AM
  2. Vlookup and named ranges ...
    By Tresfjording in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-17-2016, 03:17 PM
  3. Pull data from various Named Ranges
    By RussB in forum Excel General
    Replies: 8
    Last Post: 07-21-2015, 08:17 PM
  4. VLookup, Named Ranges and IF
    By amthyst826 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-19-2010, 07:14 PM
  5. VLOOKUP within various named ranges
    By johnnyhall81 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-15-2008, 08:31 AM
  6. [SOLVED] PULL function (Harlan Grove) - can it use Named Ranges?
    By sstackhoTEST@yahooTEST.com in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-28-2006, 02:15 PM
  7. Vlookup and named ranges
    By XCubed in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-03-2005, 03:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1