## Find closest value

*Article last updated on February 18, 2018*

**Table of Contents**

Find closest value

Find closest values

Find closest values and return adjacent values

Find closest value with a criterion

### Find closest value

**Question: **How to find closest number in a list?

**Answer**:

**Array formula in cell C2:**

Recommended article

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

**How to create an array formula**

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once
- Release all keys

Recommended article

Array formulas allows you to do advanced calculations not possible with regular formulas.

**Explaining array formula**

INDEX(A2:A26,MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0))

**Step 1 - Return absolute values of numbers in an array, numbers without its sign**

INDEX(A2:A26,MATCH(MIN(**ABS(A2:A26-C1)**),**ABS(A2:A26-C1)**,0))

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

ABS(A2:A26-C1)

becomes

ABS({3, 77, 7, 62, 66, 48, 83, 57, 69, 10, 86, 42, 71, 90, 34, 17, 73, 77, 20, 37, 59, 50, 99} - 43)

becomes

ABS{-40, 34, -36, 19, 23, 5, 40, 14, 26, -33, 43, -1, 28, 47, -9, -26, 30, 34, -23, -6, 16, 7, 56})

and returns

{40, 34, 36, 19, 23, 5, 40, 14, 26, 33, 43, 1, 28, 47, 9, 26, 30, 34, 23, 6, 16, 7, 56}

**Step 2 - Return the smallest number**

INDEX(A2:A26,MATCH(**MIN(ABS(A2:A26-C1))**,ABS(A2:A26-C1),0))

MIN(number1,[number2]) returns the smallest number in a set of values. Ignores logical values and text

MIN(ABS(A2:A26-C1))

MIN({40, 34, 36, 19, 23, 5, 40, 14, 26, 33, 43, **1**, 28, 47, 9, 26, 30, 34, 23, 6, 16, 7, 56}

returns 1.

**Step 3 - Return the relative position of an item in an array**

INDEX(A2:A26,**MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0)**)

MATCH(lookup_value;lookup_array; [match_type]) returns the relative position of an item in an array that matches a specified value

MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0)

becomes

MATCH(1, {40, 34, 36, 19, 23, 5, 40, 14, 26, 33, 43, **1**, 28, 47, 9, 26, 30, 34, 23, 6, 16, 7, 56}, 0)

returns 12.

**Step 4 - Return a value or reference of the cell at the intersection of a particular row and column**

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

INDEX(A2:A26,MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0))

becomes

INDEX(A2:A26,12)

and returns 42.

*EDIT: Added excel workbook to this blog post*

**Download excel example file**

find-closest-value.xls

(Excel 97-2003 Workbook *.xls)

### How to find closest values

**Array formula in cell C4:**

**How to create an array formula**

- Select cell C4
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once
- Release all keys

**How to copy array formula**

- Select cell C4
- Copy cell c4 (Ctrl + c)
- Select cell range C4:C20
- Paste (Ctrl + v)

**Download excel file**

### How to find closest values and return adjacent values

**Array formula in cell D5:**

**Array formula in cell E5:**

**How to create an array formula**

- Select cell D5
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once
- Release all keys

**How to copy array formula**

- Select cell D5
- Copy (Ctrl + c)
- Select cell range D6:D28
- Paste (Ctrl + v)

### Find closest value with criterion

*Basically I have two columns of data containing the same entries so assume column A and column C are two such columns.and column B and column D have certain dates corresponding to these entries.I need a formulae which will first match the entries in column A wih entries in column C .then compare the date in column D with the dates in column B and then throw up the nearest date.So my data sheet looks something like this:*

Col A Col B Col C Col D Col E(result)

A 1/2/2013 A 5/1/2013 4/3/2013

A 2/2/2013 B 5/2/2013 5/5/2013

A 4/3/2013

B 5/5/2013

B 9/10/2013 As you can see the formulae first compares the entries in col C (A) with the entries in col A (all the A's) then it matches the date in col D (5/1/2013) with the dates pertaining to value A in col B.The closest date then is 4/3/2013 which is the answer.

Col A Col B Col C Col D Col E(result)

A 1/2/2013 A 5/1/2013 4/3/2013

A 2/2/2013 B 5/2/2013 5/5/2013

A 4/3/2013

B 5/5/2013

B 9/10/2013 As you can see the formulae first compares the entries in col C (A) with the entries in col A (all the A's) then it matches the date in col D (5/1/2013) with the dates pertaining to value A in col B.The closest date then is 4/3/2013 which is the answer.

**Array formula in cell E1:**

**Download excel *.xlsx files**

find-closest-values-return-adjacent-value.xlsx

Find closest value with criterion.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

**MATCH(**lookup_value, lookup_array, [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

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

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

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

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

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

### 74 Responses to “Find closest value”

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

looks like B.S. and does not work.

To prove that the formula works I have uploaded an excel workbook with the exact same values and formula as in this blog post.

Maybe you forgot to press CTRL + SHIFT + ENTER after you typed the formula?

It works, comment one needs to learn how to do their own work...

before calling BS on A CORRECT FORMULA.

Thank you for the help on this forumula this is exaclty what i was looking for

Could someone help me use this formula in German? Using excel in a different in language is kinda challenging =D

Translate excel functions from english to german: https://www.piuha.fi/excel-function-name-translation/index.php?page=deutsch-english.html

In the formula there are some colons (,) that should be semi-colons (;) if I am right;

--> =INDEX(A2:A26;MATCH(MIN(ABS(A2:A26-C1));ABS(A2:A26-C1);0))

hey,

I think that has to do with your regional settings. See this post: https://www.get-digital-help.com/2007/11/08/excel-regional-settings/

Junk... Does not work

James,

Send me your excel file (without sensitive data) and I´ll see what I can do: https://www.get-digital-help.com/contact/

Hey, I don't know if this is specific to Excel 2010, however I found an error in your equation when attempting this in excel 2010, here is the corrected version of the equation:

=INDEX(D5:D36,MATCH(MIN(ABS(D5:D35-W8)),ABS(D5:D35-W8),0),0)

Ben Personick,

Your formula contains two different cell references, D5:D36 and D5:D35. What if the closest value is in cell D36?

Just a Typo. should have been D35 in each, D36 is blank.

Here it is using your original cell ranges instead:

=INDEX(A2:A26,MATCH(MIN(ABS(A2:A26-C1)),ABS(A2:A26-C1),0),0)

Hope that makes it clearer.

In excel 2010, an index can have two dimensions, and you must have a numerical value for both.

Ben Personick,

Thanks!

I want to find the two closest values (one above and one below) to my search value.

I would like a function that will perform this automatically as data is entered in to the search value cell.

The cntl+shift+enter is not a good solution for my spreadsheet. I need to see the results as the user enters the data and without any special key combinations.

Update.

Found two good solutions:

The first:

To find the closest larger number or an exact match in column A, use the SMALL and COUNTIF functions as shown in the following formula:

=SMALL($A$2:$A$7,COUNTIF($A$2:$A$7,"< "&B2)+1) To find the closest smaller number or an exact match in column A, use the LARGE and COUNTIF functions as shown in the following formula: =LARGE($A$2:$A$7,COUNTIF($A$2:$A$7,">"&B2)+1)

(https://www.exceltip.com/st/Retrieving_the_Closest_Larger_/_Closest_Smaller_Values_from_a_List_when_there_is_No_Exact_Match)/993.html)

It worked a treat, and then this solution from Eng-tips:

The second:

A1 is the search value. A2 to A11 the array and A12 and A13 the nearest values below and above:

In B1 put =MATCH(A1,A2:A11)

In A12 put =INDEX($A$2:$A$11,$B$1)

In A13 put =INDEX($A$2:$A$11,$B$1+1)

(https://eng-tips.com/viewthread.cfm?qid=280609&page=1)

To go one step further and choose the value closest to the search value is a simple IF statement:

{=IF((A1-A12)<(A13-A1),A12,A13)}

Note: The first of the two solutions works with unsorted data and the second using the MATCH function, with ordered data.

Hi,

The solution was veryvery helpful. Thanks for that!

However, facing a problem with decimal values.

I have a data say :: {C3:C5}{0.456,0.567,.678}

Data enterd in D1 cell : {0.560}

When trying to find out the closest value for it, the formula entered is :

=INDEX(C3:C5,MATCH(MIN(ABS(C3:C5-D1)),ABS(C3:C5-D1),0))

The value returned is always the first value from C3 i.e. 0.456.

Is there something i am missing out on???

Pradnya.Karmarkar,

I tried your example and the value returned here is 0,567. Maybe you forgot CTRL + SHIFT + ENTER?

Fantastic! Excellent Thank you

You are welcome!! Thanks for commenting!

Thanks, exactly what I needed

Hi,

Yes, now the example works. My cell given was wrong.

Thanks a lot !!!

Penny and Pradnya Karmarkar,

thanks for commenting!

Thanks for this.

Just in case others are as stupid as me:

It took me a while to figure out you need to press Ctrl SHIFT ENTER after typing (or pasting) the formula. Ctrl SHIFT ENTER is not part of the formula....

Stein,

Thanks!

I have edited this post and tried to explain how to create an array formula.

Thanks, this worked perfect after figuring out ctrl+shift+enter. Genious!

excellent tips. thanks for the help Oscar.

eben,

you are welcome!

Brilliant example - very many thanks.

Richard Moore,

Thank you for commenting!!

Excellent solution! Thanks a lot for this elegant tip...

Anurag,

Thanks!!

Excellent...

Thanks for sharing!

It did not work until I used CMD+SHIFT+ENTER (on a mac).

This looks like something I could use - I'm comparing two worksheets of school names and I need to get the closest match from to the other. Can this formula be altered to search in text strings? Thx

Hopefully, yes?

Jenna,

No, it can´t but maybe these blog posts can help you out:

https://www.get-digital-help.com/2011/04/04/excel-udf-fuzzy-lookups/

https://www.get-digital-help.com/2011/03/24/fuzzy-vlookup-excel-array-formula/

Hi,

I like the look of the formula and I have played around with the spreadsheet you updloaded but I cant get ABS to return multiple values. This is the problem I think people have been having. I have tried pasting your formula (ctrl+shift+enter) and rejig the cell references to suit my needs but I cant seem to get it to work. I worked through it with the evaluate function and the ABS function was returning a single value whereas with your it returns them all. How do I make it do this?

Anyone who is having trouble with this, research array formulas.

https://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx

There is a section that states you have to select all the cells that it applies to.

heath,

I have added a new section "How to find closest values" to this blog post. Check it out, I believe it answers your question.

execellent formula, thank you! i need some extra help though. i would like to return values corresponding to the nearest value.

For example, my data set is in column C and i returned the value nearest to what i was looking for; and found it to be in C6 for example. But i would also like to return the corresponding value in cell A6. Is there any way to do this?

Thanks again,

Cormac

Cormac,

read this: Find closest values and return adjacent values

THANK YOU VERY MUCH!

This really helps :-)

Awesome stuff, Oscar. Is there a way to use the version that returns the Adjacent Cell values with Excel 2003?

Thanks!

KK

KK,

No, I have no solution for you.

OK, thanks for the response.

I have a large array of numbers and a row of numbers. I want each number in the array to become higlighted one color if it is within 4 of any number in the row and another if it is not. Is this possible? Something like Abs(anynumber in array-any number in row < 4, Highlight green, highlight red) I think I have to utilize index or match functions but im having difficulty. Thanks!

Merit,

read post: Highlight values within specific ranges

thanks for sharing, I need help writing a formula that will return the closest higher (not less) value. thanks again

Agil,

just curious, why is an array formula needed here? I used a much simpler formula to acheive the same result. (unless i have missed something ofcourse.)

LOOKUP(E1,D:D,E:E)

This looks for the value in E1, finds the closest value or = to value in row D and returns the coorisponding value in row E.

Am i missing something that your formulas are doing?

Jeff :

For the LOOKUP function to work correctly, the data being looked up must be sorted in ascending order. If this is not possible, consider using the VLOOKUP, HLOOKUP, or MATCH functions.

And for the VLOOKUP and the HLOOKUP, the option range_lookup to find an approximate match needs the table_array to be placed in ascending sort order; otherwise, VLOOKUP might not return the correct value.

(Comes from Excel Help)

Jeff,

Sorry, I somehow forgot to answer your comment.

Jack is right,LOOKUP won´t work, even if you sort the values.

Let´s say you want to find the closest value to 43.

Values:

39

41

44

45

Lookup returns 41. 44 is closer to 43.

In the section "How to find closest values " . you said that the formula in C4 is

" =INDEX($A$2:$A$24, MATCH(SMALL(ABS($A$2:$A$24-$E$1), ROW(A1)), IF(COUNTIF($D$4:D4, $A$2:$A$24)<COUNTIF($A$2:$A$24, $A$2:$A$24), ABS($A$2:$A$24-$E$1), "A"), 0)) "

But, what is D4 refer to ?

And the function " COUNTIF " should have a condition after the set of values. while tou wrote " COUNTIF($A$2:$A$24, $A$2:$A$24) ". So, what is the condition ?

Simply you did a great work. But, i want to make the nearest value that appeared in C4 depend on a condition . If that condition is true, C4 will contain the closest value, if not, it will contain the next close value.

Thanks in advance

Amgad,

But, what is D4 refer to ?You found an error, I have changed the formula.

And the function " COUNTIF " should have a condition after the set of values. while tou wrote " COUNTIF($A$2:$A$24, $A$2:$A$24) ". So, what is the condition ?The countif function makes sure that the correct numbers are returned.

i want to make the nearest value that appeared in C4 depend on a condition . If that condition is true, C4 will contain the closest value, if not, it will contain the next close value.See attached file:

find-closest-values-Amgad.xls

Took me forever trying to figure out why my formula wasn't working. All it took was CTRL+SHIFT+ENTER at the end of the formula in the formula bar. I never would have known that had I not found this site! Thanks Oscar!

Seth,

Thanks for commenting!

I have used the find closest values and it is working properly, but the find adjacent is not. I have gone over the formula mulitple times, but I cannot find an issue.

Find closest values - {=INDEX($E$3:$E$601,MATCH(SMALL(ABS($E$3:$E$601-$S$11),ROW(E1)),IF(COUNTIF($Z$2:Z2,$E$3:$E$601)<COUNTIF($E$3:$E$601,$E$3:$E$601),ABS($E$3:$E$601-$S$11),"A"),0))}

Find closest adjacent values - {=INDEX($A$3:$A$601,MIN(IF((Z3=$E$3:$E$601)*(COUNTIFS($Z$2:Z2,$E$3:$E$601,$V$2:V2,$A$3:$A$601)<COUNTIFS($E$3:$E$601,$E$3:$E$601,$A$3:$A$601,$A$3:$A$601)),MATCH(ROW($E$3:$E$601),ROW($E$3:$E$601)),"A")))}

Values in column E are the searched values, and column A is one of many adjacent values that I would like to display. Any help would be appreciated.

Edit* - I downloaded the sample spreadsheet and found that I am running an older version of excel that does not support the COUNTIFS function. I am not sure if I need to stack COUNTIF functions within a singular IF function to get the desired result or if there is a easier option?

Roland,

You are right, countifs does not work in 2003 and earlier versions.

This array formula works and it is in fact smaller than the excel 2007 version.

Array formula in cell E5:

Thanks for commenting!

Dear Oscar,

If I would like to use the closet number to choose number in the same row (instead of Text as your example). How can I write the formula?

Please guide me. I am really in beginner of using Excel formula.

Thank you for your help,

Note S

Note S,

I would like to use the closet number to choose number in the same rowcan you explain in greater detail?

Hello OScar ,

I am trying to get a solution to find the 5 closest values . In the above example what if we have a multiple values in Column E . Could you please advise how to expand the scope .

Hari,

I am not sure I understand. Can you provide an example?

Hi Oscar, great job on the formula!

Just one question if you dont mind:

IN what corresponds to you A and B rows, I have something like that:

AAA 100

ABC 3

ABD 6

BBB 100

BCD 8

BDC 98

CCC 100

when i search the closest values and return adjacent values, i get it all right, except for the 100s. let say i search the closest values to 1, i would get, in the end of the list:

AAA 100

AAA 100

AAA 100

instead of AAA, BBB and CCC (whatever order).

Thanks in advance for your help,

S

SV,

I think you have the cell references wrong.

See attached file:

SV.xlsx

Hi Oscar,

Great formula, very useful! I was just wondering, what would the formula look like if you want to search the closest value in a range of values in two columns? That is, in your example above, if the values were not in the range A2:A24 but in A2:B24. The formula doesn't work in that case as the index requires a row number and a column number. Any idea how to extend the formula so that is works on a larger range of values?

Many thanks!

Hi Oscar,

Is there a way to do this to find the closest value like in the first example but have excel return the closest number that is higher (even if it is not quite as close as another alternative that is lower than the value). I tried different websites for a solution to this and have had no luck. I'd really appreciate the help! Thanks.

Hi, I have a scenario where in I have different rates for may different product IDs which keeps on changing i.e. for a product I have several dates associated with rates. Now, I want to know the closest rate to a date per product. can you help me with that?

If you have two criteria to search

Cell C1 = value1

Cell C2 = value2

-the RESULT will be the index of A3:A30

-value2 will be located somewhere in a table B3:Q30

-you must first find the column(B3:Q3) of value1

-then using the column of value1, look for the closest match for value2 within that column

How would this formula be written?

Hi Oscar,

Require your help for finding the next closes negative and positive value based on the last value of the array.

Array (17 values)

=================

5182.47 4432.65 5285.95 3259.14 1731.73 1011.25 66.45 -203.18 -926.70 -1857.41 -3488.99 -4006.90 -4804.79 -5339.44 -6046.62 -6414.55 -6392.52

The last value is -6392.52, how do I get the next lowest (-6414.55) and the next highest (-6046.62) in relation to the last value of the array ? Please help.

Thanks,

S Srikanth

hi oscar,

I have this scenario, I have around 500 numeric values for example at column D, this values are composed of +/- value...then i sort it from lowest to largest...from this, if i have a reference number which is zero "0" how i can automatically select 200 closest value to my reference number? help appreciated....thanks

Hi Oscar,

How to get the closest value & it X and Y or may be in word (e.g. R3 & C6) in Table (2 Dimensional)?

E.g. of the Table, & the lookup value is 83

C1 C2 C3 C4 C5 C6

R1 03 66 82 34 12 87

R2 15 27 82 33 55 72

R3 29 53 57 32 65 44

R4 72 46 62 08 38 19

R5 26 99 44 97 80 78

R6 84 65 38 47 53 98

Thanks

CH

Hi,

so this is the case that I'm working on

XX 1/1/2016

XX 1/3/2016

XX 1/8/2016

YY 1/2/2016

YY 1/8/2016

ZZ 1/3/2016

ZZ 1/4/2016

ZZ 1/8/2016

So what I need is the closest or the same exact date

ex: XX 1/4/2016 -----> 1/3/2016 (out of 3 dates that has XX)

YY 1/8/2016 -----> 1/8/2016

ZZ 1/7/2016 -----> 1/8/2016

Thank you!