Conditional Formatting highlight closest value in range without going under

Hi there,

I am looking to find out how to highlight one cell of a group of cells where it is closest to a target number without going under said number.

Here is my example.


...... R . S . T . U . V . W
34 | 28 58 68 . . . . 57.556
35 | 56 86 96
36 | .. 116 126
37 | . . . . 136


I would like to know how I can conditionally format to highlight the closest number to W34 from the selection of R34:R35,S34:S36,T34:T37 without going under. So right now S34(58) would be highlighted, and if W34 changed to 59, then T34(68) would highlight instead.

I am having a hard time figuring this out, and I have googled my brain out.

Can anyone help me? Thank you in advance!

Hi, welcome to the forum.

If your range is from R34 to T37, like in your example, highlight R37:T37 > Conditional Formatting > New Rule > Use a formula

=R34=MIN(IF($R$34:$T$35>$W$34,$R$34:$T$35))

Format: Fill color of your choice > OK > OK.

Wow yes thank you so much! This helped perfectly.

I don't understand the =R34= part of the formula. So I kept getting stuck. Is there an explanation on how this works? I wouldn't mind knowing for future, and I love learning new formulas.

Great! Happy to help.

The MIN IF section returns the minimum value in R34:T35 that is greater than the value in W34.

The formula returns TRUE if the value in R34 is equal to the value from the MIN IF formula.
You use the top left cell of the selected range in a Conditional Formatting formula.

The Conditional Formatting formula runs for all of the cells in the range (R34, S34, T34, R35, S35, and T35) and highlights any cell where the formula returns TRUE.

I just realized that there should be an equal sign added to the MIN IF formula from post #2 in the event that a value in the range is equal to W34.

That is:
=R34=MIN(IF($R$34:$T$35>=$W$34,$R$34:$T$35))

If that solved your question, please mark this thread as SOLVED.