## Find closest value

**Table of Contents**

## 1. Find closest value

The image above demonstrates a formula in cell E4 that extracts the closest number to the given number in cell E2.

Array formula in cell E4:

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

### 1.2 Explaining array formula

#### Step 1 - Subtract numbers with search number

The minus sign lets you subtract numbers in an Excel formula.

B3:B25-E2

becomes

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

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 - Remove negative sign

The ABS function converts negative numbers to positive numbers.

ABS(number)

ABS(B3:B25-E2)

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 3 - Find smallest number

The MIN function extracts the smallest number from in a cell range or array.

MIN(ABS(B3:B25-E2))

becomes

MIN({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 1.

#### Step 4 - Find the relative position of the smallest number

The MATCH function returns the relative position of a given value in a cell range or array.

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

MATCH(MIN(ABS(B3:B25-E2)),ABS(B3:B25-E2),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)

and returns 12.

#### Step 5 - Get the number

The INDEX function returns a value based on a row and column number.

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

INDEX(B3:B25,MATCH(MIN(ABS(B3:B25-E2)),ABS(B3:B25-E2),0))

becomes

INDEX(A2:A26, 12)

and returns 42.

## 2. Find closest value - Excel 365

Dynamic array formula in cell E4:

=INDEX(SORTBY(B3:B25, ABS(B3:B25-E2)),1)

### 2.1 Explaining formula

#### Step 1 - Calculate the difference between numbers and search number

The minus sign allows you to subtract numbers in an Excel formula.

B3:B25-E2

becomes

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

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 - Convert negative numbers to positive numbers

The ABS function converts negative numbers to positive numbers.

ABS(number)

ABS(B3:B25-E2)

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 3 - Sort numbers based on difference

The SORTBY function allows you to sort values from a cell range or array based on a corresponding cell range or array. It sorts values by column but keeps rows.

It is located in the Lookup and reference category and is only available to Excel 365 subscribers.

SORTBY(*array, by_array1, [sort_order1], [by_array2, sort_order2]*,…)

SORTBY(B3:B25, ABS(B3:B25-E2))

becomes

SORTBY({3; 77; 7; 62; 66; 48; 83; 57; 69; 10; 86; 42; 71; 90; 34; 17; 73; 77; 20; 37; 59; 50; 99}, {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

{42; 48; 37; 50; 34; 57; 59; 62; 66; 20; 69; 17; 71; 73; 10; 77; 77; 7; 3; 83; 86; 90; 99}

#### Step 4 - Get first value in array

The INDEX function returns a value from a cell range, you specify which value based on a row and column number.

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

INDEX(SORTBY(B3:B25, ABS(B3:B25-E2)),1)

becomes

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

and returns 42 in cell E4.

## 3. How to find closest values

The following array formula returns a list of numbers closest to the search number sorted from small to large.

Array formula in cell C4:

How to create an array formula

### 2.1 How to copy array formula

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

### 2.2 Explaining formula

#### Step 1 - Subtract numbers with search number

$A$2:$A$24-$E$1

#### Step 2 - Convert negative numbers to positive numbers

ABS($A$2:$A$24-$E$1)

#### Step 3 - Extract the k-th smallest number

SMALL(ABS($A$2:$A$24-$E$1), ROW(A1))

#### Step 4 - Count based on condition

COUNTIF($C$3:C3, $A$2:$A$24)

#### Step 5 - Count based on conditions

COUNTIF($A$2:$A$24, $A$2:$A$24)

#### Step 6 - Compare counts

COUNTIF($C$3:C3, $A$2:$A$24)<COUNTIF($A$2:$A$24, $A$2:$A$24)

#### Step 7 - Replace TRUE with corresponding positive number

IF(COUNTIF($C$3:C3, $A$2:$A$24)<COUNTIF($A$2:$A$24, $A$2:$A$24), ABS($A$2:$A$24-$E$1), "A")

#### Step 8 - Calculate the relative position

MATCH(SMALL(ABS($A$2:$A$24-$E$1), ROW(A1)), IF(COUNTIF($C$3:C3, $A$2:$A$24)<COUNTIF($A$2:$A$24, $A$2:$A$24), ABS($A$2:$A$24-$E$1), "A"), 0)

#### Step 9 - Get value

INDEX($A$2:$A$24, MATCH(SMALL(ABS($A$2:$A$24-$E$1), ROW(A1)), IF(COUNTIF($C$3:C3, $A$2:$A$24)<COUNTIF($A$2:$A$24, $A$2:$A$24), ABS($A$2:$A$24-$E$1), "A"), 0))

**Get excel file**

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

### 3.1 How to copy array formula

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

## 4. Find closest value based on criterion

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

Array formula in cell E1:

**Workbook Find closest value**

### Workbook find closest value criterion

Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]

This article demonstrates a formula that returns the last matching value based on a given condition. The above image shows […]

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

Paste image link to your comment.

**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 opened 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!

Assume, I have 3 column like A, B,c.and (D for using formula)

In column "A" there are some value between "B" and "c". and some are out of A- B range.

I need to fill the D column (if the value is between A and B and if not exist in A or B it takes value closest from A or closest from B

Hello sir

Can you please help me how to write this formula in vba with worksheet function?

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

Thank you