Lookup Heading Help

[KevCarter]

Hello all, looking for lookup help going a little different direction. AhoyNC was able to help me with some very cool dynamic array/XLOOKUP formulas.
My issue this time is finding a name inside a table that is multiple rows and columns, and bringing back the player's flight and tee time from the respective column and row headers.
To assist me in learning, I am looking for a formula in C to bring back the Flight Name from the column header, and a formula in Column D to bring back the Tee Time from the Row Header.
Thank you for your time!

2020 PAIRING FORMULA XLOOKUP HEADERS.xlsm
	A	B	C	D	E	F	G	H	I	J
1	SCORES					TUESDAY PAIRINGS
2	NAME	18 HOLE SCORE	TUESDAY FLIGHT	TEE TIME		TEE TIME	FLIGHT A	FLIGHT B	FLIGHT C	FLIGHT D
3	Allar, Vern	88				9:28 AM	Cragg, Mike	Eastwold, Conrad	Epple, John	McEathron, Paul
4	Anderson, Myron	100				10:00 AM	Solum, Craig	May, Steve	Scobie, Peter	Decker, Steve
5	Beguhn, Jerry	95				10:08 AM	Schell, John	Yoder, Preston	Sharp, Terry	Nymo, Mark
6	Bonneville, John	107				10:16 AM	Krautkramer, Randy	Mitchell, Scott	Ritchie, Steve	Thompson, Marv
7	Buergi, Scott	94				10:24 AM	Lucas, Gary	Epple, Dennis	Lorenz, Ardy	Bonneville, John
8	Collins, Mark	96				10:32 AM	Allar, Vern	Malom, Randy	Halverson, Paul	Schneider, Al
9	Cragg, Mike	90				10:40 AM	Morgan, Mike	Buergi, Scott	Anderson, Myron	Johnson, Gary
10	Decker, Steve	105				10:48 AM	Nichols, Rick	Beguhn, Jerry	Skinner, Doug	Missling, Bob
11	Degidio, Dennis	94				10:56 AM	Wick, John	Degidio, Dennis	Schuler, Dan	Vaccaro, Sam
12	Eastwold, Conrad	91				11:04 AM	Nelson, John	Olson, Chris	Wehman, Carl	Edming, Dave
13	Edming, Dave	107				11:12 AM	Schieffer, John Sr.	Nelson, ****	Marc, Terry	Schieffer, John Jr.
14	Epple, Dennis	93				11:20 AM	Leach, Greg	Marsh, Bob	Collins, Mark	Lefebvre, Larry
15	Epple, John	98				11:28 AM	Sevals, Jim	Koepp, Tom	Petersen, Ken	Sharp, Rick
16	Halverson, Paul	96
17	Johnson, Gary	103
18	Koepp, Tom	93
19	Krautkramer, Randy	84
20	Leach, Greg	85
21	Lefebvre, Larry	109
22	Lorenz, Ardy	99
23	Lucas, Gary	86
24	Malom, Randy	94
25	Marc, Terry	98
26	Marsh, Bob	91
27	May, Steve	90
28	McEathron, Paul	112
29	Missling, Bob	108
30	Mitchell, Scott	95
31	Morgan, Mike	84
32	Nelson, ****	91
33	Nelson, John	82
34	Nichols, Rick	89
35	Nymo, Mark	102
36	Olson, Chris	94
37	Petersen, Ken	98
38	Ritchie, Steve	97
39	Schell, John	86
40	Schieffer, John Jr.	117
41	Schieffer, John Sr.	89
42	Schneider, Al	112
43	Schuler, Dan	97
44	Scobie, Peter	98
45	Sevals, Jim	82
46	Sharp, Rick	112
47	Sharp, Terry	100
48	Skinner, Doug	96
49	Solum, Craig	89
50	Thompson, Marv	103
51	Vaccaro, Sam	108
52	Wehman, Carl	100
53	Wick, John	79
54	Yoder, Preston	91
Enter Scores

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J
1	SCORES					TUESDAY PAIRINGS
2	NAME	18 HOLE SCORE	TUESDAY FLIGHT	TEE TIME		TEE TIME	FLIGHT A	FLIGHT B	FLIGHT C	FLIGHT D
3	Allar, Vern	88	FLIGHT A	10:32:00		09:28:00	Cragg, Mike	Eastwold, Conrad	Epple, John	McEathron, Paul
4	Anderson, Myron	100	FLIGHT C	10:40:00		10:00:00	Solum, Craig	May, Steve	Scobie, Peter	Decker, Steve
5	Beguhn, Jerry	95	FLIGHT B	10:48:00		10:08:00	Schell, John	Yoder, Preston	Sharp, Terry	Nymo, Mark
6	Bonneville, John	107	FLIGHT D	10:24:00		10:16:00	Krautkramer, Randy	Mitchell, Scott	Ritchie, Steve	Thompson, Marv
7	Buergi, Scott	94	FLIGHT B	10:40:00		10:24:00	Lucas, Gary	Epple, Dennis	Lorenz, Ardy	Bonneville, John
8	Collins, Mark	96	FLIGHT C	11:20:00		10:32:00	Allar, Vern	Malom, Randy	Halverson, Paul	Schneider, Al
9	Cragg, Mike	90	FLIGHT A	09:28:00		10:40:00	Morgan, Mike	Buergi, Scott	Anderson, Myron	Johnson, Gary
10	Decker, Steve	105	FLIGHT D	10:00:00		10:48:00	Nichols, Rick	Beguhn, Jerry	Skinner, Doug	Missling, Bob
11	Degidio, Dennis	94	FLIGHT B	10:56:00		10:56:00	Wick, John	Degidio, Dennis	Schuler, Dan	Vaccaro, Sam
12	Eastwold, Conrad	91	FLIGHT B	09:28:00		11:04:00	Nelson, John	Olson, Chris	Wehman, Carl	Edming, Dave
13	Edming, Dave	107	FLIGHT D	11:04:00		11:12:00	Schieffer, John Sr.	Nelson, Dack	Marc, Terry	Schieffer, John Jr.
14	Epple, Dennis	93	FLIGHT B	10:24:00		11:20:00	Leach, Greg	Marsh, Bob	Collins, Mark	Lefebvre, Larry
15	Epple, John	98	FLIGHT C	09:28:00		11:28:00	Sevals, Jim	Koepp, Tom	Petersen, Ken	Sharp, Rick
16	Halverson, Paul	96	FLIGHT C	10:32:00
17	Johnson, Gary	103	FLIGHT D	10:40:00
18	Koepp, Tom	93	FLIGHT B	11:28:00
19	Krautkramer, Randy	84	FLIGHT A	10:16:00
20	Leach, Greg	85	FLIGHT A	11:20:00
21	Lefebvre, Larry	109	FLIGHT D	11:20:00
22	Lorenz, Ardy	99	FLIGHT C	10:24:00
23	Lucas, Gary	86	FLIGHT A	10:24:00
24	Malom, Randy	94	FLIGHT B	10:32:00
25	Marc, Terry	98	FLIGHT C	11:12:00
26	Marsh, Bob	91	FLIGHT B	11:20:00
27	May, Steve	90	FLIGHT B	10:00:00
28	McEathron, Paul	112	FLIGHT D	09:28:00
29	Missling, Bob	108	FLIGHT D	10:48:00
30	Mitchell, Scott	95	FLIGHT B	10:16:00
31	Morgan, Mike	84	FLIGHT A	10:40:00
32	Nelson, Dack	91	FLIGHT B	11:12:00
33	Nelson, John	82	FLIGHT A	11:04:00
34	Nichols, Rick	89	FLIGHT A	10:48:00
35	Nymo, Mark	102	FLIGHT D	10:08:00
36	Olson, Chris	94	FLIGHT B	11:04:00
37	Petersen, Ken	98	FLIGHT C	11:28:00
38	Ritchie, Steve	97	FLIGHT C	10:16:00
39	Schell, John	86	FLIGHT A	10:08:00
40	Schieffer, John Jr.	117	FLIGHT D	11:12:00
41	Schieffer, John Sr.	89	FLIGHT A	11:12:00
42	Schneider, Al	112	FLIGHT D	10:32:00
43	Schuler, Dan	97	FLIGHT C	10:56:00
44	Scobie, Peter	98	FLIGHT C	10:00:00
45	Sevals, Jim	82	FLIGHT A	11:28:00
46	Sharp, Rick	112	FLIGHT D	11:28:00
47	Sharp, Terry	100	FLIGHT C	10:08:00
48	Skinner, Doug	96	FLIGHT C	10:48:00
49	Solum, Craig	89	FLIGHT A	10:00:00
50	Thompson, Marv	103	FLIGHT D	10:16:00
51	Vaccaro, Sam	108	FLIGHT D	10:56:00
52	Wehman, Carl	100	FLIGHT C	11:04:00
53	Wick, John	79	FLIGHT A	10:56:00
54	Yoder, Preston	91	FLIGHT B	10:08:00
Master

Cell Formulas
Range	Formula
C3:C54	C3	=INDEX($A$2:$J$2,SUMPRODUCT(($G$3:$J$15=A3)*(COLUMN($G$3:$J$3))))
D3:D54	D3	=FILTER($F$3:$F$15,FILTER($G$3:$J$15,$G$2:$J$2=C3)=A3)

 

[KevCarter]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J
1	SCORES					TUESDAY PAIRINGS
2	NAME	18 HOLE SCORE	TUESDAY FLIGHT	TEE TIME		TEE TIME	FLIGHT A	FLIGHT B	FLIGHT C	FLIGHT D
3	Allar, Vern	88	FLIGHT A	10:32:00		09:28:00	Cragg, Mike	Eastwold, Conrad	Epple, John	McEathron, Paul
4	Anderson, Myron	100	FLIGHT C	10:40:00		10:00:00	Solum, Craig	May, Steve	Scobie, Peter	Decker, Steve
5	Beguhn, Jerry	95	FLIGHT B	10:48:00		10:08:00	Schell, John	Yoder, Preston	Sharp, Terry	Nymo, Mark
6	Bonneville, John	107	FLIGHT D	10:24:00		10:16:00	Krautkramer, Randy	Mitchell, Scott	Ritchie, Steve	Thompson, Marv
7	Buergi, Scott	94	FLIGHT B	10:40:00		10:24:00	Lucas, Gary	Epple, Dennis	Lorenz, Ardy	Bonneville, John
8	Collins, Mark	96	FLIGHT C	11:20:00		10:32:00	Allar, Vern	Malom, Randy	Halverson, Paul	Schneider, Al
9	Cragg, Mike	90	FLIGHT A	09:28:00		10:40:00	Morgan, Mike	Buergi, Scott	Anderson, Myron	Johnson, Gary
10	Decker, Steve	105	FLIGHT D	10:00:00		10:48:00	Nichols, Rick	Beguhn, Jerry	Skinner, Doug	Missling, Bob
11	Degidio, Dennis	94	FLIGHT B	10:56:00		10:56:00	Wick, John	Degidio, Dennis	Schuler, Dan	Vaccaro, Sam
12	Eastwold, Conrad	91	FLIGHT B	09:28:00		11:04:00	Nelson, John	Olson, Chris	Wehman, Carl	Edming, Dave
13	Edming, Dave	107	FLIGHT D	11:04:00		11:12:00	Schieffer, John Sr.	Nelson, Dack	Marc, Terry	Schieffer, John Jr.
14	Epple, Dennis	93	FLIGHT B	10:24:00		11:20:00	Leach, Greg	Marsh, Bob	Collins, Mark	Lefebvre, Larry
15	Epple, John	98	FLIGHT C	09:28:00		11:28:00	Sevals, Jim	Koepp, Tom	Petersen, Ken	Sharp, Rick
16	Halverson, Paul	96	FLIGHT C	10:32:00
17	Johnson, Gary	103	FLIGHT D	10:40:00
18	Koepp, Tom	93	FLIGHT B	11:28:00
19	Krautkramer, Randy	84	FLIGHT A	10:16:00
20	Leach, Greg	85	FLIGHT A	11:20:00
21	Lefebvre, Larry	109	FLIGHT D	11:20:00
22	Lorenz, Ardy	99	FLIGHT C	10:24:00
23	Lucas, Gary	86	FLIGHT A	10:24:00
24	Malom, Randy	94	FLIGHT B	10:32:00
25	Marc, Terry	98	FLIGHT C	11:12:00
26	Marsh, Bob	91	FLIGHT B	11:20:00
27	May, Steve	90	FLIGHT B	10:00:00
28	McEathron, Paul	112	FLIGHT D	09:28:00
29	Missling, Bob	108	FLIGHT D	10:48:00
30	Mitchell, Scott	95	FLIGHT B	10:16:00
31	Morgan, Mike	84	FLIGHT A	10:40:00
32	Nelson, Dack	91	FLIGHT B	11:12:00
33	Nelson, John	82	FLIGHT A	11:04:00
34	Nichols, Rick	89	FLIGHT A	10:48:00
35	Nymo, Mark	102	FLIGHT D	10:08:00
36	Olson, Chris	94	FLIGHT B	11:04:00
37	Petersen, Ken	98	FLIGHT C	11:28:00
38	Ritchie, Steve	97	FLIGHT C	10:16:00
39	Schell, John	86	FLIGHT A	10:08:00
40	Schieffer, John Jr.	117	FLIGHT D	11:12:00
41	Schieffer, John Sr.	89	FLIGHT A	11:12:00
42	Schneider, Al	112	FLIGHT D	10:32:00
43	Schuler, Dan	97	FLIGHT C	10:56:00
44	Scobie, Peter	98	FLIGHT C	10:00:00
45	Sevals, Jim	82	FLIGHT A	11:28:00
46	Sharp, Rick	112	FLIGHT D	11:28:00
47	Sharp, Terry	100	FLIGHT C	10:08:00
48	Skinner, Doug	96	FLIGHT C	10:48:00
49	Solum, Craig	89	FLIGHT A	10:00:00
50	Thompson, Marv	103	FLIGHT D	10:16:00
51	Vaccaro, Sam	108	FLIGHT D	10:56:00
52	Wehman, Carl	100	FLIGHT C	11:04:00
53	Wick, John	79	FLIGHT A	10:56:00
54	Yoder, Preston	91	FLIGHT B	10:08:00
Master

Cell Formulas
Range	Formula
C3:C54	C3	=INDEX($A$2:$J$2,SUMPRODUCT(($G$3:$J$15=A3)*(COLUMN($G$3:$J$3))))
D3:D54	D3	=FILTER($F$3:$F$15,FILTER($G$3:$J$15,$G$2:$J$2=C3)=A3)

Fluff, thank you very much. These 2 formulas work perfectly. I've been trying to figure this out on my own forever. Very much appreciated!!!

 

[mumps]

Macro approach:

Sub GetFlightTeeTime()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rName As Range, bottomF As Long, lCol As Long, fnd As Range
    lCol = Cells(2, Columns.Count).End(xlToLeft).Column
    bottomF = Range("F" & Rows.Count).End(xlUp).Row
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rName In Range("A3:A" & LastRow)
        Set fnd = Range("G3").Resize(bottomF - 2, lCol - 6).Find(rName, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            rName.Offset(, 2) = Cells(2, fnd.Column)
            rName.Offset(, 3) = Cells(fnd.Row, 6)
        End If
    Next rName
    Application.ScreenUpdating = True
End Sub

 

[Fluff]

Whilst it's probably not a problem in this situation, if the golfer appears twice in the pairings the sumproduct formula will fail.
Another option would be
=INDEX($G$2:$J$2,AGGREGATE(15,6,(COLUMN($G$2:$J$2)-COLUMN($G$2)+1)/($G$3:$J$15=A3),1))

 

[KevCarter]

Whilst it's probably not a problem in this situation, if the golfer appears twice in the pairings the sumproduct formula will fail.
Another option would be
=INDEX($G$2:$J$2,AGGREGATE(15,6,(COLUMN($G$2:$J$2)-COLUMN($G$2)+1)/($G$3:$J$15=A3),1))

Thank you for that Fluff. Love having alternatives. Using your formula for column C, I was able to replicate the same idea in column D in case I need to look up the time without knowing the flight. I really appreciate your help!

=INDEX($F$1:$F$15,SUMPRODUCT(($G$3:$J$15=A3)*(ROW($F$3:$F$15))))

 

[KevCarter]

Macro approach:

Sub GetFlightTeeTime()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rName As Range, bottomF As Long, lCol As Long, fnd As Range
    lCol = Cells(2, Columns.Count).End(xlToLeft).Column
    bottomF = Range("F" & Rows.Count).End(xlUp).Row
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rName In Range("A3:A" & LastRow)
        Set fnd = Range("G3").Resize(bottomF - 2, lCol - 6).Find(rName, LookIn:=xlValues, lookat:=xlWhole)
        If Not fnd Is Nothing Then
            rName.Offset(, 2) = Cells(2, fnd.Column)
            rName.Offset(, 3) = Cells(fnd.Row, 6)
        End If
    Next rName
    Application.ScreenUpdating = True
End Sub

Mumps, I am going to take some time and study this code. Very nice. I really appreciate the help!

 

[mumps]

Glad to help.

 

[Fluff]

Glad to help & thanks for the feedback.