Excel Index/Vlookup - Search Column for ABSOLUTE closest or exact value and highlight

Question

I'm new to coding in Excel, so I apologize in advance if this sounds like something I should all ready know. VERY new!

This is what I'm trying to accomplish:

In, let's say, Cell A1 I have a numerical value. I want to search all of the values in Column D (none repeating/duplicate), and find the closest absolute value (be it exact, lesser than or greater than the value in A1). After the closest has been identified, I would like to have the cell Highlighted. How do I accomplish this using the Index Command.

After this was figured, I figured I can just run an incrementing count loop (like your typical Count++ used in C++) to do the same process over and over for A2, A3, A4, etc... Any feedback/validation that the incrementing count loop would work too.

Thanks in advance!

Answer 1

There are two parts to this. First in cell B1 enter the array formula:

=INDEX(D:D,MATCH(MIN(ABS(D:D-A1)),ABS(D:D-A1),0))

This first part gets the nearest value (that value for which the absolute value of the difference is smallest).

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.

For the second part, click on cell D1 and assign conditional formatting with the FormulaIs Option:

=D1=$B$1

Then copy D1's format down the column.

The proper cell in column D will now be formatted.