Return Col/Row Header for multiple values without FILTER

[GeeWhiz7]

Hi folks,
I have a few co-users of a worksheet that uses FILTER and SEQUENCE, but as they are on older versions of Excel they lose these results.

The worksheet has something similar to below where,

• For a given array (B2:E13) of values
• Return a dynamic list of Column and Row "Header" Values based on the cut-off entered (G3).

I know a few ways to get the single values with using lookups or index/match, but not multiple values that can change depending on the cutoff used. FILTER made things easier, but then I can't share backwards with users until they get a later version of excel so any ideas are appreciated. Thanks!

I thought of looping it in a vba/udf, but it seems like there should be a simpler way using an existing set of functions.

Book1
	A	B	C	D	E	F	G	H	I	J
1		Diego	Jeff	Sandy	Mark		Desired Result
2	January	45	29	72	36		>Cutoff	Column	Row
3	February	51	77	4	89		88	Deigo	July
4	March	78	89	78	65			Jeff	March
5	April	19	35	39	39			Jeff	September
6	May	8	13	78	61			Jeff	December
7	June	76	48	20	74			Mark	February
8	July	94	0	78	25
9	August	18	13	8	34
10	September	85	95	52	46
11	October	7	16	51	52
12	November	28	67	27	38
13	December	24	92	13	74
14
15
Sheet1

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1		Diego	Jeff	Sandy	Mark		Desired Result
2	January	45	29	72	36		>Cutoff	Column	Row
3	February	51	77	4	89		88	Diego	July
4	March	78	89	78	65			Jeff	March
5	April	19	35	39	39			Jeff	September
6	May	8	13	78	61			Jeff	December
7	June	76	48	20	74			Mark	February
8	July	94	0	78	25
9	August	18	13	8	34
10	September	85	95	52	46
11	October	7	16	51	52
12	November	28	67	27	38
13	December	24	92	13	74
14
Lists

Cell Formulas
Range	Formula
H3:H9	H3	=IFERROR(INDEX($B$1:$E$1,AGGREGATE(15,6,(COLUMN($B$1:$E$1)-COLUMN($B$1)+1)/($B$2:$E$13>$G$3),ROWS(H$3:H3))),"")
I3:I9	I3	=IF(H3="","",INDEX($A$2:$A$13,AGGREGATE(15,6,(ROW($A$2:$A$13)-ROW($A$2)+1)/($B$1:$E$1=H3)/($B$2:$E$13>$G$3),COUNTIFS(H$3:H3,H3))))

 

[GeeWhiz7]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1		Diego	Jeff	Sandy	Mark		Desired Result
2	January	45	29	72	36		>Cutoff	Column	Row
3	February	51	77	4	89		88	Diego	July
4	March	78	89	78	65			Jeff	March
5	April	19	35	39	39			Jeff	September
6	May	8	13	78	61			Jeff	December
7	June	76	48	20	74			Mark	February
8	July	94	0	78	25
9	August	18	13	8	34
10	September	85	95	52	46
11	October	7	16	51	52
12	November	28	67	27	38
13	December	24	92	13	74
14
Lists

Cell Formulas
Range	Formula
H3:H9	H3	=IFERROR(INDEX($B$1:$E$1,AGGREGATE(15,6,(COLUMN($B$1:$E$1)-COLUMN($B$1)+1)/($B$2:$E$13>$G$3),ROWS(H$3:H3))),"")
I3:I9	I3	=IF(H3="","",INDEX($A$2:$A$13,AGGREGATE(15,6,(ROW($A$2:$A$13)-ROW($A$2)+1)/($B$1:$E$1=H3)/($B$2:$E$13>$G$3),COUNTIFS(H$3:H3,H3))))

What is impressive to me is I can stew on something to get a working solution for a while and even then what usually gets proposed on this forum is more straightforward and works better. This works perfectly for going backwards in Excel versions. Guess I still have a lot to learn. Thank you Fluff!

 

[Fluff]

You're welcome & thanks for the feedback.