Thanks:  0
Likes:  0

# Thread: Lookup/Reference Help - Multiple lookup ranges

1. ## 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. ## 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

3. ## Re: Lookup/Reference Help - Multiple lookup ranges

Originally Posted by StephenCrump
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. ## Re: Lookup/Reference Help - Multiple lookup ranges

Originally Posted by brianharg
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.

Originally Posted by brianharg
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. ## 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
7
Seton Hall
8
N.C. State Texas Florida
11
Villanova
1
Kansas State
9
Villanova
1
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. ## 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
11DavidsonBucknell
12San Diego StateTexas Southern
13Stephen F. AustinCharleston
14Virginia TechVirginia
15ArizonaTCU
16MontanaMissouri
17St. BonaventureNew Mexico State

7. ## Re: Lookup/Reference Help - Multiple lookup ranges

Seems to be working GREAT! Thank you so much

8. ## 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. ## 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!