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.
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.
=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,""))))))))))))))))))))))))))))))))))))))))))))))))- 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.
- 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!
- Thanks QP. I need to unpack this, but it looks like an answer! Be back later.
- 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...
