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.
Most Popular
Saturday, January 16, 2010
Subscribe to:
Post Comments (Atom)
=INDEX(Data,MATCH(MIN(ABS(Data-Target)),ABS(Data-Target),0)) array entered. - for the closest match
ReplyDeletesam
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.
ReplyDeleteLimitation being - the data has to be sorted by the lookup value.
ReplyDeleteHello Sam
ReplyDeleteThanks 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
Hello Vipul
ReplyDeleteThanks 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
Yest it does...I have uploaded the file. I have been using it for many years
ReplyDeletehttp://cid-af0f671fc64e8122.skydrive.live.com/self.aspx/New%20folder/CMATCH.xls
sam
Hello Sam
ReplyDeleteThanks 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
Hi Yogesh..you are right my formula does not handle multiple columns...
ReplyDeleteMay 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
Can I some how use the same formula to get the top 10 orders with customer name, value etc. Ie the hole row?
ReplyDeleteHello Rickard
ReplyDeleteWhat 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.
I have been trying to use this formula for a sequence of time values, which are unsorted, but which include values which pass from before midnight to after midnight. I have also tried using your VBA function cmatch, which produces a NAME! error. The difficulty seems to lie in the values after midnight, which excel does not recognise. Any ideas?
ReplyDelete