Lookup/Reference Help - Multiple lookup ranges

brianharg

New Member
Joined
Mar 18, 2018
Messages
5
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 bcde
1InDay 1Day 2Day 3
2Player NameOut
3Bill=IF(ISERROR(VLOOKUP(C5,'Survivor Support'!$C$3:$C$34, 1, FALSE)),$B$2,CONCATENATE("Out"," - ",C$1))Winner
4Bob=IF(ISERROR(VLOOKUP(C4,'Survivor Support'!$C$3:$C$34, 1, FALSE)),$B$2,CONCATENATE("Out"," - ",C$1))Loser
5Jane=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))WinnerLoser
6Sue=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))WinnerWinnerLoser

<tbody>
</tbody>

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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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


Book1
ABCDEF
1Day1234
2ResultABAA
3
4PicksStatus
5BillOut Day 2AAAA
6BobOut Day 1BBBB
7JaneOut Day 4ABAB
8MaryStill inABAA
Sheet1
 
Last edited:
Upvote 0
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

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.
 
Upvote 0
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.

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-announcements/127080-guidelines-forum-use.html
 
Upvote 0
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 1Day 2Day 3
2
TeamseedTeamseedTeamseedDay 1 LosersDay 2 LosersDay 3 Losers
3
Rhode Island
7​
Texas A&M
7​
Rhode Island
7​
OklahomaPronidenceVillanova
4
Tennessee
3​
Purdue
2​
Tennessee
3​
Wright StateCal State FullertonKentucky
5
Gonzaga
4​
Marshall
13​
Gonzaga
4​
UNCGWichita StateHouston
6
Kansas
1​
Cincinnati
2​
Kansas
1​
PennGeorgia StateTexas Tech
7
Duke
2​
North Carolina
2​
Duke
2​
IonaLipscombAlabama
8
Loyola (Ill.)
11​
Butler
10​
Loyola (Ill.)
11​
MiamiArkansasBuffalo
9
Ohio State
5​
West Virginia
5​
Ohio State
5​
South Dakota StateMurray StateMichigan
10
Seton Hall
8​
Nevada
7​
Seton Hall
8​
N.C. StateTexasFlorida
11
Villanova
1​
Kansas State
9​
Villanova
1​
RadfordCreighton
12
Kentucky
5​
Michigan State
3​
Kentucky
5​
DavidsonBucknell
13
Houston
6​
Xavier
1​
Houston
6​
San Diego StateTexas Southern
14
Texas Tech
3​
Auburn
4​
Texas Tech
3​
Stephen F. AustinCharleston
15
Alabama
9​
UMBC
16​
Alabama
9​
Virginia TechVirginia
16
Buffalo
13​
Syracuse
11​
Buffalo
13​
ArizonaTCU
17
Michigan
3​
Florida State
9​
Michigan
3​
MontanaMissouri
18
Florida
6​
Clemson
5​
Florida
6​
St. BonaventureNew 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?
 
Upvote 0
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


Book1
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
Sheet1
 
Last edited:
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,726
Members
448,294
Latest member
jmjmjmjmjmjm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top