## Highlight closest number

*Article last updated on January 11, 2018*

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.

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

The image above demonstrates an array formula in cell E4 that searches for the closest date in column A to the […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

Converts negative numbers to positive numbers, in other words, the ABS function removes the sign. Excel Function Syntax ABS(number) Arguments […]

Counting conditionally formatted cells (vba)

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

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

Highlight dates in a date range

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

This blog post shows you how to easily identify duplicate rows or records in a list. Conditional formatting formula: =COUNTIFS($B$3:$B$15, […]

### 10 Responses to “Highlight closest number”

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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?