Google Sheets - Conditional Formatting on Closest Value

Question

I have a column of data and a target value and am wanting to apply a rule that will highlight the closest value to that target value from the column of data possible. I have tried a few different forumla and nothing has worked so far. This is what I am currently working with:

Target Number is in I3
Data is in I4:I24

=ABS($I$3-I4)=MIN(ABS($I$3-$I4:$I24))

This is all being done through Google Sheets (not sure if this is relivant but thought it couldn't hurt being included)

Answer 1

First of all, let's change $I4:$I24 to $I$4:$I$24, so that this array is the same for all cells that the formula is applied to.

The - operator only works for numbers, not arrays. So when you subtract an array from a number, it returns just the value minus the first number in the array. The same goes for the ABS function - it can't take an array.

To instead have the - operator and ABS function apply to each element of the array individually, you need to use the ARRAYFORMULA function. To do this, wrap the ABS($I$3-$I$4:$I$24) in ARRAYFORMULA - that is, ARRAYFORMULA(ABS($I$3-$I$4:$I$24)). Now, ABS and - apply to each element of the array individually, and return an array of all the results. This can then be passed into MIN.

Now we get

=ABS($I$3-I4)=MIN(ARRAYFORMULA(ABS($I$3-$I$4:$I$24)))