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
For getting greater than equal to value
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.