Find closest value
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows a formula in cell E3 that calculates the nearest number in cell range B3:B13 to the number specified in cell E2.
Table of Contents
1. Find the 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
returns {-40; 34; ... ; 56}
Step 2 - Remove negative sign
The ABS function converts negative numbers to positive numbers.
ABS(number)
ABS(B3:B25-E2)
returns {40; 34; ... ; 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))
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)
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))
returns 42.
2. Find closest value - Excel 365
Dynamic array formula in cell E4:
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; ... ; 99} - 43
and returns {-40; 34; ... ; 56}
Step 2 - Convert negative numbers to positive numbers
The ABS function converts negative numbers to positive numbers.
ABS(number)
ABS(B3:B25-E2)
returns {40; 34; ... ; 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))
returns {42; 48; ... ; 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) returns 42 in cell E4.
3. How to find the 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
The minus sign lets you subtract numbers in an Excel formula.
$A$2:$A$24-$E$1
returns {-40; 34; ... ; 56}.
Step 2 - Convert negative numbers to positive numbers
The ABS function converts any negative values to positive values.
ABS(number)
ABS($A$2:$A$24-$E$1)
returns {40; 34;... ; 56}
Step 3 - Extract the k-th smallest number
The SMALL function returns the k-th smallest number in a cell range or array.
SMALL(array, k)
The ROW function returns a number representing the row based on a cell reference.
ROW(ref)
SMALL(ABS($A$2:$A$24-$E$1), ROW(A1))
returns 1. Number 1 is the smallest number in the array.
Step 4 - Count based on condition
The COUNTIF function counts values based on a condition, in this case, a cell reference. Not a regular cell reference but an expanding cell reference that grows when you copy the cell to the cells below.
COUNTIF($C$3:C3, $A$2:$A$24)
returns {0; 0; ... ; 0}.
Step 5 - Count based on conditions
The COUNTIF function counts values based on a condition, in this case, based on multiple values.
COUNTIF($A$2:$A$24, $A$2:$A$24)
returns {1; 2; ... ; 1}.
Step 6 - Compare counts
The less than character lets you check if a value is smaller than another value, the result is a boolean value TRUE or FALSE.
COUNTIF($C$3:C3, $A$2:$A$24)<COUNTIF($A$2:$A$24, $A$2:$A$24)
returns {TRUE; TRUE; ... ; TRUE}.
Step 7 - Replace TRUE with corresponding positive number
The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.
IF(logical_test, [value_if_true], [value_if_false])
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")
returns {40; 34; ... ; 56}.
Step 8 - Calculate the relative position
The MATCH function returns the relative position of an item in an array or cell reference that matches a specified value in a specific order.
MATCH(lookup_value, lookup_array, [match_type])
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)
returns 11. 1 is found in position 11 in the array or cell reference.
Step 9 - Get value
The INDEX function returns a value based on a row and column number.
INDEX(array, row_num, [column_num])
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))
becomes
INDEX($A$2:$A$24, 11)
and returns 44.
Get excel file
5. 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)
6. Find closest value based on criterion
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.
Array formula in cell E1:
Workbook Find closest value
Workbook find closest value criterion
Lookups category
Table of Contents How to perform a two-dimensional lookup Reverse two-way lookups in a cross reference table [Excel 2016] Reverse […]
This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one […]
Excel categories
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.
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 row
can 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