Formula to Find the Lowest Point Between Values

Hello,
I need a formula that uses the lowest value in one column and finds the which point it’s less than between multiples columns with the result being the column heading of the said column. But it’s not simply finding which value it is less than but which value it is nearest too.

The formulas reside in the columns “Open Color” and “Close Color” and is the finding the value the “Open” or “Close” value columns are less than but greater than the one below. The value is looking for this result by comparing it to the values in the columns with the headings Yellow, Purple, Ivory, Aqua, Gold, and Red and the result is the heading of the column that is less than.

If the value in the “Open” or “Close” column is greater than all of the colored heading columns the result is "Above All".

Example:
Row 1 Open Column equals .8929 the values its closes too is .8944 which is in the yellow column, so the result is the columns heading "Yellow".

Please see spreadsheet for a better understanding. Examples of what the value should be have been manually entered.

Any and all help you could provide for any of the formulas would be greatly appreciated. Thank you.

Hi.

Try this in O3:

=IF(C3=MAX(C3,I3:N3),"Above All",LOOKUP(1,0/FREQUENCY(-10^9,1/(C3-I3:N3)),I$2:N$2))

Copy down as required.

Regards

You're welcome!