Improving the function of CDF in Excel

Reading the publication Simplify the binary search in Excel has led to an additional improvement in the function of the CDF compared to the one given in the article.

What was not taken into account, and what I would like to add:

1. Versatility, i.e. the ability to call a function for a sorted array, and for unsorted.

2. Eliminate the need to call the binary search function (CDF) twice.

3. Both the initial CDF function and the one proposed in the article have the following disadvantage: the column number is usually fixed when the function is called. Usually life does not stand still, and in the table you have to add columns to an arbitrary place. If the function arguments are specified as cell references, then Excel can independently modify the links so that the link continues to refer to the same data as before. If the link is specified by the column number, then when adding columns to the middle of the table, such renumbering does not occur and you have to search for the used CDF functions and change the calls manually. Therefore, it is advisable to specify a column with a key and a column with the desired value in the form of two separate arguments.

4. Make a fool proof - check the arguments passed for correctness.

In this regard, it is advisable to separate the indication of how the array is sorted (ascending, descending or not sorted) from the desired result (an exact result or an approximate result is needed). Here is the resulting code with comments.

' VPR -       (VLookup & HLookup) ' key -   () ' a -     ' b -    ,    ' Ordered -    : 1 -  , 0 -  , -1 -    ' NotStrict - ,     : False - , True -  . '     (Ordered = 0),      Function VPR(key As Variant, ByRef a As Range, ByRef b As Range, Optional Ordered As Integer = 0, Optional NotStrict As Boolean = False) As Variant '    a  b -   ,     '      If (b.Areas.Count <> 1) Or ((b.Columns.Count > 1) And (b.Rows.Count > 1)) Then VPR = CDbl("") Exit Function End If If (a.Areas.Count <> 1) Or ((a.Columns.Count > 1) And (a.Rows.Count > 1)) Then VPR = CDbl("") Exit Function End If If (a.Count <> b.Count) Or (a.Count < 1) Then VPR = CDbl("") Exit Function End If If Ordered = 0 Then NotStrict = False End If Dim index As Long index = Application.WorksheetFunction.Match(key, a, Ordered) If (Not NotStrict) And (a(index).value <> key) Then VPR = CDbl("") Else VPR = b(index).value End If End Function 

Extra buns

1. The ability to search in an array, sorted in descending order (Ordered = -1).
2. The function allows you to search if any (or both) of the arguments a and b are horizontal rows (that is, it also generalizes the function of the horizontal viewing of the PGR).

Comments

1. The CDbl ("") call is needed to generate an error (issue # VALUE #).
2. It is not the CDF that is used, but the Match function (the Russian equivalent is MATCH).

Thanks for attention!