Thursday, January 14, 2010

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 theclosest match (greater than or equal to) toa value in a list? The list is sorted inascending 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 InputEnd IfEnd Function`

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

1 comment:

1. Bạn là chủ xe và đang cần tìm hàng vận chuyển? Bạn là người cần tìm xe vận chuyển hàng? Vậy bạn hãy ghé vào sàn vận tải nội địa đây là nơi sẽ giúp bạn tìm thấy thứ bạn đang cần tìm. Hiện nay, chúng tôi tự hào là một trong những đơn vị cung cấp giải pháp vận chuyển hàng đầu hiện nay. Với các dịch vụ vận chuyển hàng hóa nội địa, vận chuyển Bắc Trung Nam, vận chuyển hàng đông lạnh bắc nam,... Đến với chúng tôi bạn sẽ không cần lo lắng tìm hàng hay tìm xe để vận chuyển hàng. Hiện nay thì các tuyến vận chuyển chúng tôi đang có thể kể đến như: vận chuyển hàng đi bạc liêu, vận chuyển hàng đi vũng tàu, vận chuyển hàng đi bắc ninh, vận chuyển hàng đi bến tre,... Để biết thêm thông tin hãy liên hệ với chúng tôi nhé.