SOLVED

Shorter way or shortcut to use IF, AND, AND drawing from 3 variables

Occasional Contributor

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

20 Replies

@tomeegee 

 

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.

Thanks so much for taking the time Mathetes. I consolidate the various table sources on this Test Sheet.

@tomeegee 

=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,""))))))))))))))))))))))))))))))))))))))))))))))))

 

Thanks QP. I need to unpack this, but it looks like an answer! Be back later.

@tomeegee 

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) )
)
Wow that works well. I'll start dissecting to learn what it's doing and integrate it into my workbook. Thanks a ton! I might have a question or two...
Hmmm... let me see if I can handle this. Be back to you.
best response confirmed by tomeegee (Occasional Contributor)
Solution
I'm very impressed by this formula! I am tooling around with it to expand it to all courses and when the formula is on separate worksheets etc. But you've given me a great tool to work with! Thanks.
Hello Sergei and thanks. I am not at all versed in VBA, though I'm watching a few tutorials on how to use this code in my workbook. Hope I don't blow it up!

@tomeegee 

 

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

 

Thanks for that clarification before I disappeared into a VBA rabbit hole! To be clear, a deeply nested IF function is what I am trying to avoid, correct? Quadruple Pawn's formula seems to work pretty well. I just need to now expand it to include all courses and apply across multiple pages of the workbook.

Thanks, @mathetes . It was mentioned Excel for Mac, I'm not on it, but guess LET() shall be available for subscription version.

@tomeegee Nested IF() always works fine and available on any version, except it's bit hard in support.

I have 365 for Mac so it is compatible. Where does the rest of the formula "live"? J3 has "=_xlfn.LET(" in it, but the function works so the rest must be somewhere! I want to play around with expanding this to the full tournament.
Yes, indeed. I've been using LET without problems on my Mac, as well as FILTER and UNIQUE and others of the new functions. I do have the subscription, and get access as a Beta user as well.
I am so appreciative of all the responses to my inquiry. But QP I just quickly applied your formula to the whole event in minutes! I am curious about Sergia's LET, but for the moment, this formula you've provided is a solid answer to my challenge. Thanks again!

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?

@tomeegee 

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.

That's really incredible streamlined! I would imagine if I had more than one woman, I could create a W list and replace Gina M with that sublist? I look forward to applying this tonight to see it action. Thank you!