## Highlight closest value in excel

This post decribes how to highlight records with closest value to a criterion. You can also choose to highlight the second closest value (or more).

### Conditional formatting formula

This is not working in excel 2010 and later versions probably, see this work-around.

### How to apply conditional formatting formula

Make sure you adjust cell references to your excel sheet.

- Select cells B6:D10
- Click "Home" tab
- Click "Conditional Formatting" button
- Click "New Rule.."
- Click "Use a formula to determine which cells to format"
- Type
*=OR(ABS($C6-$C$2)=SMALL(ABS($C$6:$C$10-$C$2), ROW($A$1:INDEX($A:$A, $C$3))))*in "Format values where this formula is TRUE" window. - Click "Format.." button
- Click "Fill" tab
- Select a color for highlighting cells.
- Click "Ok"
- Click "Ok"
- Click "Ok"

### How the conditional formatting formula works in cell C6

=OR(ABS($C6-$C$2)=SMALL(ABS($C$6:$C$10-$C$2), ROW($A$1:INDEX($A:$A, $C$3))))

*Step 1 - Calculate age differences*

=OR(ABS($C6-$C$2)=SMALL(**ABS($C$6:$C$10-$C$2), **ROW($A$1:INDEX($A:$A, $C$3))))

ABS(number) returns the absolute value of a number, a number without its sign.

ABS($C$6:$C$10-$C$2)

becomes

ABS({25;46;33;26;47}-30)

becomes

ABS({-5;16;3;-4;17})

and returns {5;16;3;4;17}

**Step 2 - Create an array with the same size as chosen records to highlight in cell C3.**

=OR(ABS($C6-$C$2)=SMALL(ABS($C$6:$C$10-$C$2),**ROW($A$1:INDEX($A:$A,$C$3)))**)

ROW($A$1:INDEX($A:$A,$C$3)))

becomes

ROW($A$1:INDEX($A:$A,2)))

becomes

ROW($A$1:$A$2)

and returns {1;2}

*Step 3 - Return the two smallest numbers in array*

=OR(ABS($C6-$C$2)=**SMALL(ABS($C$6:$C$10-$C$2),ROW($A$1:INDEX($A:$A,$C$3)))**)

SMALL(array,k) returns the k-th smallest number in this data set.

SMALL(ABS($C$6:$C$10-$C$2),ROW($A$1:INDEX($A:$A,$C$3)))

becomes

SMALL({5;16;3;4;17};{1;2})

and returns {3;4}

*Step 4 - Is cell value in C6 equal to any number in array?*

=OR(ABS($C6-$C$2)=SMALL(ABS($C$6:$C$10-$C$2),ROW($A$1:INDEX($A:$A,$C$3))))

becomes

OR(logical1, logical2, ...) checks whether any of the arguments are TRUE, and returns TRUE or FALSE. Returns FALSE if only all arguments are FALSE.

$C6 is a relative AND absolute cell reference.

=OR(ABS($C6-$C$2)={3;4})

becomes

=OR(ABS(25-30)={3;4})

becomes

=OR(ABS(-5)={3;4})

becomes

=OR(5={3;4})

becomes

=OR({5=3,5=4})

becomes

=OR({FALSE,FALSE})

and returns FALSE. Row 6 is not highlighted.

**Download excel example file**

Highlight closest values.xls

(Excel 97-2003 Workbook *.xls)

highlight-closest-values3.xlsx

(Excel 2007-2016 Workbook *.xlsx)

**Functions:**

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**ABS(**number**)**

Returns the absolute value of a number, a number without its sign.

**SMALL(**array,k**)** returns the k-th smallest number in this data set.

**OR(**logical1, logical2, ...)

Checks whether any of the arguments are TRUE, and returns TRUE or FALSE. Returns FALSE if only all arguments are FALSE.

### 10 Responses to “Highlight closest value in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

I duplicated the spreadsheet but the formula did not work for me on Excel 2007. It says there's an error. Could you check and explain? Thx.

Lyon,

You probably have to adjust cell references in conditional formatting formula to your spreadsheet.

As I mentioned, I recreate the sample spreadsheet and copy paste the formula. But Excel 2007 says there's an error in the formula. Something is not right.

Lyon,

You are right, I found errors. I have corrected them.

Thanks for pointing them out.

It's now working! Great! Thanks a lot for your quick response.

Absoultely great

only small problem

when I enter value of 40 in search value in example given

it highlights 3 values (wendy also) I accepted it to highlight

only richard & sarah

I used this formula, however, I received errors. I see this post is quite old. is there an updated formula for excel 2010?

kris

You are right, it is not working for excel 2010.

I created a named range with the same formula and used that named range in a CF formula.

See this workbook:

http://www.get-digital-help.com/wp-content/uploads/2011/03/Highlight-closest-values3.xlsx

Please can you elaborate as to how to get this done in excel 2013.

Ranjan

See my answer to kris above, it should work in excel 2013 also?