Modifying an INDEX and MATCH formula to advance if duplicate found

Hello,

I have a table of years with a corresponding dollar amount. I have written a function that will return the column header (offer) when given a dollar amount.

=INDEX($H$1:$N$1,MATCH(C8,$H$3:$N$3,0))

.. which is working just fine. However, it will only ever return the first lowest offer, and not the next offer with the same value. In my example, the lowest offer for item B is $18, found in offer 1. The 2nd lowest offer is also $18, found in offer 4.

Is there a way to rewrite this formula to give me the next, or nth next offer of the same amount?

My thought was to try an IF formula, so that if D3 = C3, it would advance the bound on the index by a certain amount, but could not figure out how to replace the letter in a column reference. Is this possible to do?

Sheet attached, and here is what I was thinking

=IF(INDEX(N$1:AB$1,MATCH(J32,N32:AB32,0))=C37,INDEX(N$1:AB$1,MATCH(J32,N32:AB32,0)),INDEX(N$1:AB$1,MATCH(J32,N32:AB32,0)))

-IF(INDEX(H1:N1,MATCH(D8,H3:N3,0)) = INDEX(H1:N1,MATCH(C8,H3:N3,0)), INDEX(H1:N1,MATCH(D8,(write something to replace the letter H using COLUMN maybe?)3:N3,0)), INDEX($H$1:$N$1,MATCH(D8,H3:N3,0))

Appreciate any help or ideas!

Thank you

Thank you for the attempt, but I think I did not explain correctly.

When I add a duplicate min value to row A or B, your formula only returns the first header for both, not the 2nd duplciate header.


Looking at my original document, the ideal formula would find return Offer 1 for cell C3, and return Offer 4 for D3.

Thank you for trying though!