Custom Function : Excel Closest Match using VBA

Some days back I got a mail from Kevin asking for how to get closest match in a list sorted in acending order

Hi Yogesh, is there a way for me to find the
closest match (greater than or equal to) to
a value in a list? The list is sorted in
ascending order and cannot be changed.
Regards,
Kevin

I responded to him with a Custom Function capable to Finding Closest Match as per his requirement. This worked as the list was sorted in ascending order. He sent me a thanks note for this.

However this left certain questions in my mind such as

1. What if the list is not sorted, Custom function should be able to do this in unsorted list.

2. He wanted greater than equal to value, however it should be able to handle less than equal to value also

With these things in my mind I worked out another custom function which works with unsorted data range and is capable of finding both type of closest match in excel.

I am sharing updated Custom Function with all of you. You will need to copy this code to regular VBA module of your workbook

Function cmatch(ByVal n, l As Range, Optional Switch As Integer = 1) '************************************************************************************** '* Custom Function by Yogesh Gupta , yogesh@yogeshguptaonline.com * '* Custom Function to find Closest Match in an unsorted data * '* use Switch value as -1 in case you want less than equal to closest match * '* Default value for Switch is 1, and will find greater than equal to closest match * '************************************************************************************** If Switch = 1 Then ' This will get greater than equal to value a = Application.Max(l) If a < n Then ' Error in case list does not have value greater than or equal to n cmatch = "#N/A" Else For Each c In l If c >= n Then If c < a Then a = c End If Next c cmatch = a End If ElseIf Switch = -1 Then ' This will get less than equal to value a = Application.Min(l) If a > n Then ' Error in case list does not have value less than or equal to n cmatch = "#N/A" Else For Each c In l If c <= n Then If c > a Then a = c End If Next c cmatch = a End If Else cmatch = "#N/A" ' Error in case of Invalid Input End If End Function

Download Excel file with Custom Function to Find Closest Match to see how it works