Forum Discussion
Shorter way or shortcut to use IF, AND, AND drawing from 3 variables
- 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.
=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,""))))))))))))))))))))))))))))))))))))))))))))))))
- 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...
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) ) )- 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!
Just for the record, the solution that SergeiBaklan 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
- Hmmm... let me see if I can handle this. Be back to you.
