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"
<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
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 |
<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