Oct 25 2021 04:32 PM
I have a scoresheet spreadsheet I've developed. I have 12 players playing 5 courses and each course you could be playing FRONT or BACK. Each player has a unique number associated with their name, the course, and whether it's front or back. So:
=IF(AND(B3="Player 1 Name",I3="Course Name",B5="BACK NINE"),'Course HDCPs'!G12)
Works like a charm... for one person. But I want to be able to drop in any name in the Player name field, and swap out the course from a list pull down. I've done this before and my formula is really, really long. For just two players it's: =IF(AND(B22="Player 1 Name",I22="Conestoga",B24="BACK NINE"),'Course HDCPs'!$G$31,IF(AND(B22="Player 2 Name",I22="Conestoga",B24="BACK NINE"),'Course HDCPs'!$G$4)) and so on.
So for 12 players... =IF(AND(B3="Player Name" x 12 x 5 courses x Front and Back. Is there a way to shortcut this? Or does Excel not care how many logical tests there are? Way above my pay grade. Excel for Mac. Vers 16.37 running on macOS Big Sur Vers 11.6
Oct 25 2021 05:36 PM
I strongly suspect there's an easier way to do this, but without seeing how you've laid out the spreadsheet, it's hard to give any specifics. Could you modify what you have so that no real names are involved, nor anything else that would connect it to the actual courses, for example.....and then post that example. My guess is that some table lookups based on the three variables would suffice. VLOOKUP or XLOOKUP often can be used in place of nested IF statements. The latter, as you're experiencing, can become unintelligible (even if it works) when you get to 12 deep nesting.
So a sample of your spreadsheet, please.
Oct 25 2021 06:43 PM - edited Oct 25 2021 06:44 PM
Thanks so much for taking the time Mathetes. I consolidate the various table sources on this Test Sheet.
Oct 26 2021 08:57 AM
=INDEX((B13:E24,B29:E40,B45:E56,B61:E72,B77:E88),MATCH(B3,B13:B24,0),IF(D3="Front",3,4),MATCH(G3,G13:G17,0))
Maybe this is the formula you are looking for. I suppose you have 5 tables, one for each of the 5 courses. In my example these 5 tables are in ranges B13:E24,B29:E40,B45:E56,B61:E72 and B77:E88.
Nested IF formula for 2 courses would be:
=IF(AND(B3="Player 1",D3="FRONT",G3="Course 1"),D13,IF(AND(B3="Player 2",D3="FRONT",G3="Course 1"),D14,IF(AND(B3="Player 3",D3="FRONT",G3="Course 1"),D15,IF(AND(B3="Player 4",D3="FRONT",G3="Course 1"),D16,IF(AND(B3="Player 5",D3="FRONT",G3="Course 1"),D17,IF(AND(B3="Player 6",D3="FRONT",G3="Course 1"),D18,IF(AND(B3="Player 7",D3="FRONT",G3="Course 1"),D19,IF(AND(B3="Player 8",D3="FRONT",G3="Course 1"),D20,IF(AND(B3="Player 9",D3="FRONT",G3="Course 1"),D21,IF(AND(B3="Player 10",D3="FRONT",G3="Course 1"),D22,IF(AND(B3="Player 11",D3="FRONT",G3="Course 1"),D23,IF(AND(B3="Player 12",D3="FRONT",G3="Course 1"),D24,IF(AND(B3="Player 1",D3="BACK",G3="Course 1"),E13,IF(AND(B3="Player 2",D3="BACK",G3="Course 1"),E14,IF(AND(B3="Player 3",D3="BACK",G3="Course 1"),E15,IF(AND(B3="Player 4",D3="BACK",G3="Course 1"),E16,IF(AND(B3="Player 5",D3="BACK",G3="Course 1"),E17,IF(AND(B3="Player 6",D3="BACK",G3="Course 1"),E18,IF(AND(B3="Player 7",D3="BACK",G3="Course 1"),E19,IF(AND(B3="Player 8",D3="BACK",G3="Course 1"),E20,IF(AND(
B3="Player 9",D3="BACK",G3="Course 1"),E21,IF(AND(B3="Player 10",D3="BACK",G3="Course 1"),E22,IF(AND(B3="Player 11",D3="BACK",G3="Course 1"),E23,IF(AND(B3="Player 12",D3="BACK",G3="Course 1"),E24,IF(AND(B3="Player 1",D3="FRONT",G3="Course 2"),D29,IF(AND(B3="Player 2",D3="FRONT",G3="Course 2"),D30,IF(AND(B3="Player 3",D3="FRONT",G3="Course 2"),D31,IF(AND(B3="Player 4",D3="FRONT",G3="Course 2"),D32,IF(AND(B3="Player 5",D3="FRONT",G3="Course 2"),D33,IF(AND(B3="Player 6",D3="FRONT",G3="Course 2"),D34,IF(AND(B3="Player 7",D3="FRONT",G3="Course 2"),D35,IF(AND(B3="Player 8",D3="FRONT",G3="Course 2"),D36,IF(AND(B3="Player 9",D3="FRONT",G3="Course 2"),D37,IF(AND(B3="Player 10",D3="FRONT",G3="Course 2"),D38,IF(AND(B3="Player 11",D3="FRONT",G3="Course 2"),D39,IF(AND(B3="Player 12",D3="FRONT",G3="Course 2"),D40,IF(AND(B3="Player 1",D3="BACK",G3="Course 2"),E29,IF(AND(B3="Player 2",D3="BACK",G3="Course 2"),E30,IF(AND(B3="Player 3",D3="BACK",G3="Course 2"),E31,IF(AND(B3="Player 4",D3="BACK",G3="Course 2"),E32,IF(AND(B3="P
layer 5",D3="BACK",G3="Course 2"),E33,IF(AND(B3="Player 6",D3="BACK",G3="Course 2"),E34,IF(AND(B3="Player 7",D3="BACK",G3="Course 2"),E35,IF(AND(B3="Player 8",D3="BACK",G3="Course 2"),E36,IF(AND(B3="Player 9",D3="BACK",G3="Course 2"),E37,IF(AND(B3="Player 10",D3="BACK",G3="Course 2"),E38,IF(AND(B3="Player 11",D3="BACK",G3="Course 2"),E39,IF(AND(B3="Player 12",D3="BACK",G3="Course 2"),E40,""))))))))))))))))))))))))))))))))))))))))))))))))
Oct 26 2021 09:23 AM
Oct 26 2021 10:33 AM
As variant
=LET(
pos, XMATCH(G3,A:A),
names, INDEX(B:B, pos+1):INDEX(B:B, pos+12),
front, INDEX(D:D, pos+1):INDEX(D:D, pos+12),
back, INDEX(E:E, pos+1):INDEX(E:E, pos+12),
XLOOKUP(B3,names, CHOOSE( (D3="FRONT")+1, back, front) )
)
Oct 26 2021 04:34 PM
Oct 26 2021 04:36 PM
Oct 27 2021 07:39 AM
SolutionOct 27 2021 07:45 AM
Oct 27 2021 09:10 AM
Just for the record, the solution that @Sergei Baklan gave you is not VBA. It is a function (LET) that is only available in the newest versions of Excel, and it's a powerful one, a way to make formulas more readable and therefore more manageable. Also for the record, building deeply nested IF functions sadly goes in the opposite direction, less manageable and far less easily maintained, more error prone as a result.
Here, to help learn LET and how it works, its value, is a web reference site describing it: https://exceljet.net/excel-functions/excel-let-function
Oct 27 2021 09:28 AM
Oct 27 2021 09:32 AM
Thanks, @mathetes . It was mentioned Excel for Mac, I'm not on it, but guess LET() shall be available for subscription version.
Oct 27 2021 09:34 AM
@tomeegee Nested IF() always works fine and available on any version, except it's bit hard in support.
Oct 27 2021 01:20 PM
Oct 27 2021 01:23 PM
Oct 27 2021 01:49 PM
Nov 03 2021 02:24 PM
Hello again! I have been having a good time applying @Quadruple_Pawn's index formula. I also learned when not to, and just stick with nested IF. Though I did come across this challenge. In the attached spreadsheet, I have a nested IF (in C9) to populate the appropriate hole handicap based on course, and side. However, I have one woman player in my Players list, and the handicaps for her would all be different (except on Sand Hallow). So at the bottom I experimented with a modified IF to include =IF(AND(B3=Player (in C33) so that if any player from the list is in B3, the handicaps populate. And then I duplicated the formula and changed it to =IF(AND(B3="Gina M" (in C34) and have it populating the women's handicap. However, I'm not sure how to have both exist in the same formula without getting into another really, really long nested IF. They're pretty long already. Essentially I'm looking for an IF/BUT sort of thing, if there was one. Or am I getting to crazy for one golfer? I could eventually get more women, so I could have two lists, Mens, Womens.
Any help would be great. Is it possible to buy you guys a beer?
Nov 04 2021 02:33 AM
I suggest to add an IF statement in cell C3 that returns either F or M for female (in this example for "Gina M") or male based on the selected value in cell B3.
I adapted the named ranges e.g. "Sand Hollow" to "Sand_Hollow" according to the requirements of named ranges. Formulas in ranges C33:K33 and C34:K34 are updated accordingly and the suggested formulas are in C8 and C9 (copied across ranges C8:K8 and C9:K9) and it seems to work.
Nov 04 2021 09:35 AM
Oct 27 2021 07:39 AM
Solution