KevCarter
Board Regular
- Joined
- Dec 7, 2013
- Messages
- 161
- Office Version
- 365
- Platform
- Windows
- MacOS
I know there is an easier, much more elegant way of doing this. Trying to look up a name and flight from the table on the left, and bring back the starting time from the table on the right. The formula I am hoping to fix is the one in J3:J54. XL2BB is messing up my column widths, but you get the idea. I'm just plain spacing out and drawing a blank...
2020 MARSH TOURNAMENT SCOREBOARD MONDAY.xlsm | ||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
E | F | G | H | I | J | R | S | T | U | V | W | X | ||||||||||
1 | SORTED SCORES FLIGHTED | MARSH OUTING TUESDAY PAIRINGS | ||||||||||||||||||||
2 | RANK | NAME | 18 HOLE SCORE | TUESDAY FLIGHT | CARD DRAWN | PAIRED | # | TEE TIME | FLIGHT A | FLIGHT B | FLIGHT C | FLIGHT D | ||||||||||
3 | 1 | Wick, John | 79 | FLIGHT A | 9 | 10:56 AM | 1 | 9:28 AM | Cragg, Mike | Eastwold, Conrad | Epple, John | McEathron, Paul | ||||||||||
4 | 2 | Nelson, John | 82 | FLIGHT A | 10 | 11:04 AM | 2 | 10:00 AM | Solum, Craig | May, Steve | Scobie, Peter | Decker, Steve | ||||||||||
5 | 3 | Sevals, Jim | 82 | FLIGHT A | 13 | 11:28 AM | 3 | 10:08 AM | Schell, John | Yoder, Preston | Sharp, Terry | Nymo, Mark | ||||||||||
6 | 4 | Krautkramer, Randy | 84 | FLIGHT A | 4 | 10:16 AM | 4 | 10:16 AM | Krautkramer, Randy | Mitchell, Scott | Ritchie, Steve | Thompson, Marv | ||||||||||
7 | 5 | Morgan, Mike | 84 | FLIGHT A | 7 | 10:40 AM | 5 | 10:24 AM | Lucas, Gary | Epple, Dennis | Lorenz, Ardy | Bonneville, John | ||||||||||
8 | 6 | Leach, Greg | 85 | FLIGHT A | 12 | 11:20 AM | 6 | 10:32 AM | Allar, Vern | Malom, Randy | Halverson, Paul | Schneider, Al | ||||||||||
9 | 7 | Lucas, Gary | 86 | FLIGHT A | 5 | 10:24 AM | 7 | 10:40 AM | Morgan, Mike | Buergi, Scott | Anderson, Myron | Johnson, Gary | ||||||||||
10 | 8 | Schell, John | 86 | FLIGHT A | 3 | 10:08 AM | 8 | 10:48 AM | Nichols, Rick | Beguhn, Jerry | Skinner, Doug | Missling, Bob | ||||||||||
11 | 9 | Allar, Vern | 88 | FLIGHT A | 6 | 10:32 AM | 9 | 10:56 AM | Wick, John | Degidio, Dennis | Schuler, Dan | Vaccaro, Sam | ||||||||||
12 | 10 | Nichols, Rick | 89 | FLIGHT A | 8 | 10:48 AM | 10 | 11:04 AM | Nelson, John | Olson, Chris | Wehman, Carl | Edming, Dave | ||||||||||
13 | 11 | Schieffer, John Sr. | 89 | FLIGHT A | 11 | 11:12 AM | 11 | 11:12 AM | Schieffer, John Sr. | Nelson, **** | Marc, Terry | Schieffer, John Jr. | ||||||||||
14 | 12 | Solum, Craig | 89 | FLIGHT A | 2 | 10:00 AM | 12 | 11:20 AM | Leach, Greg | Marsh, Bob | Collins, Mark | Lefebvre, Larry | ||||||||||
15 | 13 | Cragg, Mike | 90 | FLIGHT A | 1 | 9:28 AM | 13 | 11:28 AM | Sevals, Jim | Koepp, Tom | Petersen, Ken | Sharp, Rick | ||||||||||
16 | 14 | May, Steve | 90 | FLIGHT B | 2 | 10:00 AM | ||||||||||||||||
17 | 15 | Eastwold, Conrad | 91 | FLIGHT B | 1 | 9:28 AM | ||||||||||||||||
18 | 16 | Marsh, Bob | 91 | FLIGHT B | 12 | 11:20 AM | ||||||||||||||||
19 | 17 | Nelson, **** | 91 | FLIGHT B | 11 | 11:12 AM | ||||||||||||||||
20 | 18 | Yoder, Preston | 91 | FLIGHT B | 3 | 10:08 AM | ||||||||||||||||
21 | 19 | Epple, Dennis | 93 | FLIGHT B | 5 | 10:24 AM | ||||||||||||||||
22 | 20 | Koepp, Tom | 93 | FLIGHT B | 13 | 11:28 AM | ||||||||||||||||
23 | 21 | Buergi, Scott | 94 | FLIGHT B | 7 | 10:40 AM | ||||||||||||||||
24 | 22 | Degidio, Dennis | 94 | FLIGHT B | 9 | 10:56 AM | ||||||||||||||||
25 | 23 | Malom, Randy | 94 | FLIGHT B | 6 | 10:32 AM | ||||||||||||||||
26 | 24 | Olson, Chris | 94 | FLIGHT B | 10 | 11:04 AM | ||||||||||||||||
27 | 25 | Beguhn, Jerry | 95 | FLIGHT B | 8 | 10:48 AM | ||||||||||||||||
28 | 26 | Mitchell, Scott | 95 | FLIGHT B | 4 | 10:16 AM | ||||||||||||||||
29 | 27 | Collins, Mark | 96 | FLIGHT C | 12 | 11:20 AM | ||||||||||||||||
30 | 28 | Halverson, Paul | 96 | FLIGHT C | 6 | 10:32 AM | ||||||||||||||||
31 | 29 | Skinner, Doug | 96 | FLIGHT C | 8 | 10:48 AM | ||||||||||||||||
32 | 30 | Ritchie, Steve | 97 | FLIGHT C | 4 | 10:16 AM | ||||||||||||||||
33 | 31 | Schuler, Dan | 97 | FLIGHT C | 9 | 10:56 AM | ||||||||||||||||
34 | 32 | Epple, John | 98 | FLIGHT C | 1 | 9:28 AM | ||||||||||||||||
35 | 33 | Marc, Terry | 98 | FLIGHT C | 11 | 11:12 AM | ||||||||||||||||
36 | 34 | Petersen, Ken | 98 | FLIGHT C | 13 | 11:28 AM | ||||||||||||||||
37 | 35 | Scobie, Peter | 98 | FLIGHT C | 2 | 10:00 AM | ||||||||||||||||
38 | 36 | Lorenz, Ardy | 99 | FLIGHT C | 5 | 10:24 AM | ||||||||||||||||
39 | 37 | Anderson, Myron | 100 | FLIGHT C | 7 | 10:40 AM | ||||||||||||||||
40 | 38 | Sharp, Terry | 100 | FLIGHT C | 3 | 10:08 AM | ||||||||||||||||
41 | 39 | Wehman, Carl | 100 | FLIGHT C | 10 | 11:04 AM | ||||||||||||||||
Enter Scores |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E3:E54 | E3 | =IF(F3="","",SEQUENCE(COUNTA(F3:F54))) |
F3:G54 | F3 | =IFERROR(SORT(FILTER(B3:C54,C3:C54<>"",""),{2,1}),"") |
J3:J54 | J3 | =IF(F3="","",IFERROR(XLOOKUP(F3:F54,$U$3#,$T$3:$T$15),IFERROR(XLOOKUP(F3:F54,$V$3#,$T$3:$T$15),IFERROR(XLOOKUP(F3:F54,$W$3#,$T$3:$T$15),IFERROR(XLOOKUP(F3:F54,$X$3#,$T$3:$T$15),"-"))))) |
S3:S15 | S3 | =SEQUENCE(COUNTA(T3:T15)) |
U3:U15 | U3 | =IFERROR(XLOOKUP(S3#,$I$3:$I$15,$F$3:$F$15,""),"") |
V3:V15 | V3 | =IFERROR(XLOOKUP(S3#,$I$16:$I$28,$F$16:$F$28,""),"") |
W3:W15 | W3 | =IFERROR(XLOOKUP(S3#,$I$29:$I$41,$F$29:$F$41,""),"") |
X3:X15 | X3 | =IFERROR(XLOOKUP(S3#,$I$42:$I$54,$F$42:$F$54,""),"") |
Dynamic array formulas. |