Lookup/Reference Help - Multiple lookup ranges
Lookup/Reference Help - Multiple lookup ranges
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Lookup/Reference Help - Multiple lookup ranges

  1. #1
    New Member
    Join Date
    Mar 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Lookup/Reference Help - Multiple lookup ranges

     
    Not sure I am on the right track - let me try to explain what I am trying to do. I am running a "survivor" pool (person picks a team each day - if they win - they keep going - if the pick a loser, they are out).

    What I want to do - is look at the pick they made on the day, check to see if it is a loser - then note if they are still "In" - or if they got knocked out. If they get knocked out, I want to say which day they lost. Following is a picture that shows what I am doing for "day 1" and I got it to work for that specific day. I am using another tab (named Survivor Support) that will list the losers each day (specific to the day). Day 1 will be in column C of that tab. Day 2 will be in G and Day 3 in column K. Using the formula in B3 & 4 - all good - tells me if on the 1st day they are in or out - B3 returns "In" and B4 Returns "Out - Day 1"


    a b c d e
    1 In Day 1 Day 2 Day 3
    2 Player Name Out
    3 Bill =IF(ISERROR(VLOOKUP(C5,'Survivor Support'!$C$3:$C$34, 1, FALSE)),$B$2,CONCATENATE("Out"," - ",C$1)) Winner
    4 Bob =IF(ISERROR(VLOOKUP(C4,'Survivor Support'!$C$3:$C$34, 1, FALSE)),$B$2,CONCATENATE("Out"," - ",C$1)) Loser
    5 Jane =IF(ISNA(VLOOKUP(C5,'Survivor Support'!$C$3:$C$34, 1, FALSE)),VLOOKUP(D5,'Survivor Support'!$G$3:$G$34, 1, FALSE),VLOOKUP(E5,'Survivor Support'!$K$3:$K$34, 1, FALSE)) Winner Loser
    6 Sue =IF(ISNA(VLOOKUP(C6,'Survivor Support'!$C$3:$C$34, 1, FALSE)),VLOOKUP(D6,'Survivor Support'!$G$3:$G$34, 1, FALSE),VLOOKUP(E6,'Survivor Support'!$K$3:$K$34, 1, FALSE)) Winner Winner Loser

    So how do I extend this to check all 10 days of March Madness? Using the formula in B5 - I can get it to return "Loser" (I have not tried to do the cell reference yet to get it to return "In" our "Out - Day 2") - but since I can get it to return "Loser" I feel I can figure that out... However - using the same row 6 - gives me a "#N/A" return?

    I am on Excel 2016 - but before the new functions came out - so I do not have IFS (thought that might be my answer)...

    Is there an easier approach someone can think of - or if this is the right approach, any help on the formula would be appreciated.

    Thanks,
    Brian

  2. #2
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,254
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup/Reference Help - Multiple lookup ranges

    Welcome to the Forum!

    I'm not sure what your other sheet looks like, but I think your formulae can be much simpler:

    B5: =IFERROR("Out Day "&MATCH(TRUE,C5:F5<>C$2:F$2,),"Still in") Array entered

    ABCDEF
    1Day1234
    2ResultABAA
    3
    4PicksStatus
    5BillOut Day 2AAAA
    6BobOut Day 1BBBB
    7JaneOut Day 4ABAB
    8MaryStill inABAA



    Last edited by StephenCrump; Mar 18th, 2018 at 06:07 PM.

  3. #3
    New Member
    Join Date
    Mar 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup/Reference Help - Multiple lookup ranges

    Quote Originally Posted by StephenCrump View Post
    Welcome to the Forum!

    I'm not sure what your other sheet looks like, but I think your formulae can be much simpler:

    B5: =IFERROR("Out Day "&MATCH(TRUE,C5:F5<>C$2:F$2,),"Still in") Array entered

    A B C D E F
    1 Day 1 2 3 4
    2 Result A B A A
    3
    4 Picks Status
    5 Bill Out Day 2 A A A A
    6 Bob Out Day 1 B B B B
    7 Jane Out Day 4 A B A B
    8 Mary Still in A B A A

    I am trying to play with this as it certainly seems a lot simpler! What do you mean by "array entered" at the end of the equation?

    Maybe to try to be more clear (my bad!).... If you think about the NCAA Basketball tournament.. On day 1 the players (Bill, Bob, Jane, Mary) will each have to pick 1 team out of 32 - so I have that as one list/column in my other sheet. There will then be 16 losing teams which I will track as another column. So I need to see what Bill picked (C5) compared to the whole list of day 1 losers 9 (C2 above - but it will be a list C2 thru C17). Then again for day 2 (D5 compared to a list D2 thru D17). Then Day 3 - through 10. The fact I needed to look in a range is why I was thinking VLOOKUP? I have not used MATCH before, but trying to see if it will work.

  4. #4
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,254
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup/Reference Help - Multiple lookup ranges

    Quote Originally Posted by brianharg View Post
    What do you mean by "array entered" at the end of the equation?
    CTRL-Shift-Enter rather than Enter, i.e. type the formula, then instead of hitting the Enter key, first hold down the CTRL and Shift keys and then hit the Enter key.

    Quote Originally Posted by brianharg View Post
    Maybe to try to be more clear (my bad!)....
    It would help if you could show screenshots of your data, and the results you're expecting to see.

    Part B here gives you a couple of ways you can use to post screenshots: https://www.mrexcel.com/forum/board-...forum-use.html

  5. #5
    New Member
    Join Date
    Mar 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup/Reference Help - Multiple lookup ranges

    OK - will do what I can - thanks for the patience with a newbie!!!

    Here is the main Survivor Worksheet where they will pick the winner each day. I do have conditional formation working that shows the losers in yellow/red. Column B is what I am trying to get working.

    Excel 2013 32 bit
    A
    B
    C
    D
    E
    2
    In
    Day 1
    Day 2
    Day 3
    3
    Player
    Out
    15-Mar
    16-Mar
    17-Mar
    4
    a 1
    In
    Rhode Island
    Cincinnati
    Rhode Island
    5
    a 2
    In
    Tennessee
    Purdue
    Villanova
    6
    a 3
    In
    Gonzaga
    New Mexico State
    7
    b 1
    Out - Day 1
    St. Bonaventure
    North Carolina
    8
    b 2
    Still in
    St. Bonaventure
    Sheet: Survivor

    Here is the table again - but with the formulas... Using the Vlookup I can get the first day to work - but I do not know how to nest more lookups to get day2 , day 3, etc.

    Excel 2013 32 bit
    A
    B
    C
    D
    E
    2
    In
    Day 1
    Day 2
    Day 3
    3
    Player
    Out
    15-Mar
    16-Mar
    17-Mar
    4
    a 1
    =IF(ISERROR(VLOOKUP(C4,'Survivor Support'!$J$3:$J$34, 1, FALSE)),$B$2,CONCATENATE("Out"," - ",C$2))
    Rhode Island
    Cincinnati
    Rhode Island
    5
    a 2
    =IF(ISERROR(VLOOKUP(C5,'Survivor Support'!$J$3:$J$34, 1, FALSE)),$B$2,CONCATENATE("Out"," - ",C$2))
    Tennessee
    Purdue
    Villanova
    6
    a 3
    =IF(ISERROR(VLOOKUP(C6,'Survivor Support'!$J$3:$J$34, 1, FALSE)),$B$2,CONCATENATE("Out"," - ",C$2))
    Gonzaga
    New Mexico State
    7
    b 1
    =IF(ISERROR(VLOOKUP(C7,'Survivor Support'!$J$3:$J$34, 1, FALSE)),$B$2,CONCATENATE("Out"," - ",C$2))
    St. Bonaventure
    North Carolina
    8
    b 2
    =IFERROR("Out Day "&MATCH(TRUE,C8:F8<>'Survivor Support'!J3:J18,),"Still in")
    St. Bonaventure
    Sheet: Survivor

    Here is the reference sheet. First several columns (a thru H) will be all the teams that will play that day - that I will extend for 10 days. Then the next set of columns is where I was going to list the losers (I am using this for the conditional formation).

    Excel 2013 32 bit
    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    L
    1
    Day 1 Day 2 Day 3
    2
    Team seed Team seed Team seed Day 1 Losers Day 2 Losers Day 3 Losers
    3
    Rhode Island
    7
    Texas A&M
    7
    Rhode Island
    7
    Oklahoma Pronidence Villanova
    4
    Tennessee
    3
    Purdue
    2
    Tennessee
    3
    Wright State Cal State Fullerton Kentucky
    5
    Gonzaga
    4
    Marshall
    13
    Gonzaga
    4
    UNCG Wichita State Houston
    6
    Kansas
    1
    Cincinnati
    2
    Kansas
    1
    Penn Georgia State Texas Tech
    7
    Duke
    2
    North Carolina
    2
    Duke
    2
    Iona Lipscomb Alabama
    8
    Loyola (Ill.)
    11
    Butler
    10
    Loyola (Ill.)
    11
    Miami Arkansas Buffalo
    9
    Ohio State
    5
    West Virginia
    5
    Ohio State
    5
    South Dakota State Murray State Michigan
    10
    Seton Hall
    8
    Nevada
    7
    Seton Hall
    8
    N.C. State Texas Florida
    11
    Villanova
    1
    Kansas State
    9
    Villanova
    1
    Radford Creighton
    12
    Kentucky
    5
    Michigan State
    3
    Kentucky
    5
    Davidson Bucknell
    13
    Houston
    6
    Xavier
    1
    Houston
    6
    San Diego State Texas Southern
    14
    Texas Tech
    3
    Auburn
    4
    Texas Tech
    3
    Stephen F. Austin Charleston
    15
    Alabama
    9
    UMBC
    16
    Alabama
    9
    Virginia Tech Virginia
    16
    Buffalo
    13
    Syracuse
    11
    Buffalo
    13
    Arizona TCU
    17
    Michigan
    3
    Florida State
    9
    Michigan
    3
    Montana Missouri
    18
    Florida
    6
    Clemson
    5
    Florida
    6
    St. Bonaventure New Mexico State
    19
    Oklahoma
    10
    Pronidence
    10
    20
    Wright State
    14
    Cal State Fullerton
    15
    21
    UNCG
    13
    Wichita State
    4
    22
    Penn
    16
    Georgia State
    15
    23
    Iona
    15
    Lipscomb
    15
    24
    Miami
    6
    Arkansas
    7
    25
    South Dakota State
    12
    Murray State
    12
    Sheet: Survivor Support

    Am I way off base on how I am trying to get this to work? If there something simpler that I am not thinking of? I have read I can only nest 7 statements - will this be a problem w/ the need for 10 days?

  6. #6
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,254
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup/Reference Help - Multiple lookup ranges

    C2 conditionally formatted with formula: =ISNUMBER(MATCH(C2,G$2:G$20,))

    B2: =IFERROR("Out day " &1/(1/MIN(IF(C2:E2=G$2:I$20,COLUMN(G2:I2)-COLUMN(G2)+1))),"Still in") Array-entered

    ABCDEFGHI
    1PlayerIn/OutDay 1Day 2Day 3Day 1 LosersDay 2 LosersDay 3 Losers
    2a 1Still inRhode IslandCincinnatiRhode IslandOklahomaPronidenceVillanova
    3a 2Out day 3TennesseePurdueVillanovaWright StateCal State FullertonKentucky
    4a 3Out day 2GonzagaNew Mexico StateUNCGWichita StateHouston
    5b 1Out day 1St. BonaventureNorth CarolinaPennGeorgia StateTexas Tech
    6b 2Out day 1St. BonaventureIonaLipscombAlabama
    7MiamiArkansasBuffalo
    8South Dakota StateMurray StateMichigan
    9N.C. StateTexasFlorida
    10RadfordCreighton
    11DavidsonBucknell
    12San Diego StateTexas Southern
    13Stephen F. AustinCharleston
    14Virginia TechVirginia
    15ArizonaTCU
    16MontanaMissouri
    17St. BonaventureNew Mexico State



    Last edited by StephenCrump; Mar 18th, 2018 at 10:06 PM.

  7. #7
    New Member
    Join Date
    Mar 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup/Reference Help - Multiple lookup ranges

    Seems to be working GREAT! Thank you so much

  8. #8
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,254
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup/Reference Help - Multiple lookup ranges

    Great! You're welcome.

    You should probably also validate the players' choices against some master list to make sure you have exact matches.

    If, for example, I chose Maimi, Tennassee and Oklahamo as my winners, I'd be pretty confident these choices wouldn't appear on your list of losers.

    More subtly, any leading, trailing or intermediate spaces would also prevent an exact match.

  9. #9
    New Member
    Join Date
    Mar 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Lookup/Reference Help - Multiple lookup ranges

      
    Thanks and great idea! I am using the "Survivor Support" tab to create a drop down list they have to select from. Column A for day 1, Column D for day 2 and so on. I am running an easier pool now and learned that the hard way! Types, spaces, etc. can kill me!

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com