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.
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...
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!
