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!


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


enter image description here

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:


enter image description here

Then copy D1's format down the column.

The proper cell in column D will now be formatted.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.