Reverse Index Match (Find column row labels based on a value)

I have a max/min formula on a table that returns the desired valu, but I also need formulas to determine where in the table that value is. If i do my usual index match, i get caught in a loop since I don't know one of the values at the outset.

In the table below, if my Min value is the bottom left, (419,621), how can i find the (4.13) and 6% values too that will be returned in two separate cells alongside the Min value?



(4.13) (2.75) (1.38) 0.00 1.38 2.75 4.13
(6%) (355,206) (248,021) (138,501) (22,435) 106,842 258,218 448,501
(4%) (368,787) (255,334) (139,024) (15,174) 123,195 284,626 485,316
(2%) (380,865) (261,544) (138,853) (7,625) 139,302 310,038 520,208
0% (391,735) (266,931) (138,228) 0 154,981 334,344 553,193
2% (401,671) (271,725) (137,328) 7,560 170,134 357,526 584,361
4% (410,904) (276,109) (136,283) 14,970 184,727 379,624 613,844
6% (419,621) (280,216) (135,175) 22,189 198,768 400,712 641,793



Capture.PNG

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

One way:

=INDEX($B$2:$H$2,SUM(INDEX(($B$3:$H$9=$B15)*COLUMN($B$3:$H$9),))-COLUMN($B$3)+1)

similar for the %. See file.

Or :

Shift 1

Formula:

Please Login or Register  to view this content.

Shift2

Formula:

Please Login or Register  to view this content.

You're welcome... Next time, please include a sample file in Post 1.