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 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

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é.

    ReplyDelete