I am looking for a formula that searches a range for MAX or MIN value(s) and returns the corresponding column header(s). I can write the formula in such a way that it returns one answer (the first MAX/MIN in the range), but I want it to return ALL the MAXs/MINs, not just the first one. I would like the returns to be in one cell, whether or not they're concatenated with a [comma] or just put together [Q1Q2].
I've embedded a photo for reference but I've also attached a sheet.
Capture6.JPG
N6/O6 is looking for the MAX/MIN from AJ18:AN18, respectively, and returning the respective column header from AJ3:AJ18. P6/Q6 is returning the MIN/MAX from AO18:AS18, respectively, and returning the appropriate column header from AO3:AS3.
Problem is, as you can see, AJ18:AN18 has multiple max values (2@22), but my formula in N6 (=INDEX($AJ$3:$AN$3,0,MATCH(MAX($AJ$18:$AN$18),$AJ$18:$AN$18,0)) only returns the header of the first 22 (Q2). I'd like a formula that returns EACH of the MAX/MIN value headers. The appropriate return in this case would be: Q2/Q3 or Q2Q3, something like that.
There may be instances where there is no duplicate, in which case the formula should be able to account for that.
Bookmarks