## 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.

### Category: Conditional formatting

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

Comments(26) Filed in category: Conditional formatting, Count values, Excel, User defined functions (udf), VBA

Question: How do I highlight dates that meet a criteria using conditional formatting? Table of contents Highlight values in a […]

Comments(15) Filed in category: Conditional formatting, Excel

Conditional formatting formula to highlight max value in every month: =B2=MAX(IF(MONTH(A2)=MONTH(Date_rng), Close, "")) Conditional formatting formula to highlight min value […]

Comments(7) Filed in category: Conditional formatting, Dates, Excel

Question: I have a list that I keep adding rows to. How do i create a border that expands as […]

Comments(5) Filed in category: Conditional formatting, Excel

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

Comments(5) Filed in category: Conditional formatting, Cross reference table, Excel, Schedule, Templates

You can highlight duplicates in an excel defined table using conditional formatting. However, that won´t work if you only want […]

Comments(4) Filed in category: Conditional formatting, Duplicate values, Excel

I would like to show you how to search a table using conditional formatting. The criteria highlight matching column and […]

Comments(4) Filed in category: Conditional formatting, Excel

This blog post shows you how to highlight rows with multiple criteria using OR logic. The criteria is found in […]

Comments(3) Filed in category: Conditional formatting, Excel, Records

You can apply different conditional formatting formulas and formatting to a cell range, using a drop down list. Here is […]

Comments(3) Filed in category: Conditional formatting, Drop down lists, Excel

### 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:

https://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?