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
|
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é.
ReplyDeleteWe at instyle jackets make best leather jackets something like Clothing men . we make all designs efficiently and we are expert in it since last many years. visit to the website and get the best services.
ReplyDeleteWhat’s up, just wanted to tell you, I liked this article. It was funny. Keep on posting! There is definitely a great deal to know about this topic. Professional Essay Writers I like all of the points you’ve made.
ReplyDeleteCustom Capacity able to Tracking down Nearest Match Homework Help Services according to his necessity. This functioned as the rundown was arranged in rising request.
ReplyDeleteYou get a luxurious and warm Mauvetree men leather jacket at a price that you would never have imagined. This is one of the best yet affordable sheepskin jackets in the market today.
ReplyDelete