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

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

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.

  1. Select cells B6:D10
  2. Click "Home" tab
  3. Click "Conditional Formatting" button
  4. Click "New Rule.."
  5. Click "Use a formula to determine which cells to format"
  6. 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.
  7. Click "Format.." button
  8. Click "Fill" tab
  9. Select a color for highlighting cells.
  10. Click "Ok"
  11. Click "Ok"
  12. 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.