Excel Formula : Closest Match ~ Yogesh Gupta's Excel Tips

Saturday, January 16, 2010

Excel Formula : Closest Match

Arun Singla one of my blog reader has sent me Excel Formula in response to my previous post Custom Function : Excel Closest Match using VBA.

Formula sent by Arun gives similar results as achieved by Custom Function using VBA. I am sharing the formula with you.

For getting less than equal to value
=IF(COUNTIF(Data,Target)>=1,Target,SMALL(Data,COUNTIF(Data,"<"&Target)))
For getting greater than equal to value
=IF(COUNTIF(Data,Target)>=1,Target,LARGE(Data,COUNTIF(Data,">"&Target)))

In above formula : Data is unsorted data range name, target is the named range having target value.

Now let me explain how does this formula works. I will explain first formula which calculates less than equal to value.

Step 1 - Formula calculates the number of values matching target value using countif function of excel. COUNTIF(Data,Target) gives the total number of values matching exactly with the target. In case you get even one value matching with the target, you return the target as closest match, no need for further working.

Step 2 - In case formula does not get matching value at step 1 , it moves to find the closest match. SMALL(Data,COUNTIF(Data,"<"&Target)) Returns the k-th smallest value in a data set. COUNTIF(Data,"<"&Target) gives us total number of data points less than target. We are using this as k-th smallest value which is closest match less than the target value. I hope this explanation is not confusing to you. Check out the file with this formula to find closest match in excel to understand it better.

10 comments:

sam said...: =INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0)) array entered. - for the closest match

sam; January 18, 2010 4:30 PM
Vipul said...: Same can be achieved using the lookup formulae and also a comination of index() and match() formulae by using the appropriate value in the last argument of lookup/match formulae.; January 18, 2010 6:08 PM
Vipul said...: Limitation being - the data has to be sorted by the lookup value.; January 18, 2010 6:09 PM
Yogesh Gupta said...: Hello Sam

Thanks for sharing this formula, however this does not work on the data I have in file uploaded along with this post. I entered it as CSE (Array) formula but results into error.

You wanna give it a try ? share your file if you succeed
Regards//Yogesh Gupta; January 18, 2010 9:29 PM
Yogesh Gupta said...: Hello Vipul

Thanks for your inputs, however we are dealing with unsroted range here. Some times it is not possible to sort a range just to make a particular formula work.

Regards//Yogesh Gupta; January 18, 2010 9:30 PM
sam said...: Yest it does...I have uploaded the file. I have been using it for many years

http://cid-af0f671fc64e8122.skydrive.live.com/self.aspx/New%20folder/CMATCH.xls

sam; January 19, 2010 10:17 AM
Yogesh Gupta said...: Hello Sam

Thanks for sharing file with the formula. Now I realised that why this was not working when I tried to use this formula.

This array formula fails to work in case the Data range in more than one column. I was trying it with data in two column and was getting error.

Thanks
Yogesh Gupta; January 19, 2010 9:35 PM
sam said...: Hi Yogesh..you are right my formula does not handle multiple columns...
May be we can modify your formulas a bit
=SMALL(Data,COUNTIF(Data,"<="&Target)))
will work. So we dont need the IF if an exact match is available

sam; January 20, 2010 1:34 PM
Rickard said...: Can I some how use the same formula to get the top 10 orders with customer name, value etc. Ie the hole row?; February 5, 2010 1:07 PM
Yogesh Gupta said...: Hello Rickard

What you are aksing for is possible technically and will need lots of formulas. Instead I will go for a PivotTable and filter it for TOP 10 values. This will be more robust and easy way to do what you need.

Let me know in case you need any help in making PivotTables and filtering them on TOP 10 Values.; February 5, 2010 2:30 PM