Extended VLOOKUP UDF

In my daily job we use a lot of VLOOKUP on external datasheets.

We don 't want to change these sheets but often we want to do a sort of VLOOKUP but with a negative column_index parameter,
so we can look up to the left of the lookup_value. I wrote a UDF to do just that. Can some of you guys comment on my code........ thnx

Public Function VLOOKUPPLUS(lookup_value, table_array As Range, column_index As Long, Optional range_lookup) As Variant

    Dim i As Long
   
    If column_index < 0 Then
   
       If table_array.Columns.Count + column_index < 0 Then VLOOKUPPLUS = CVErr(xlErrRef): Exit Function
      
       If range_lookup = 0 Then
      
          For i = 1 To table_array.Rows.Count
      
              If table_array.Cells(i, table_array.Columns.Count).Value = lookup_value Then
          
                 VLOOKUPPLUS = table_array.Cells(i, (table_array.Columns.Count + 1 + column_index)).Value: Exit Function
          
              End If
          
              VLOOKUPPLUS = CVErr(xlErrValue): Exit Function
      
          Next i
      
       Else
         
          For i = 1 To table_array.Rows.Count
         
              If table_array.Cells(i, table_array.Columns.Count).Value <= lookup_value Then
             
                 VLOOKUPPLUS = table_array.Cells(i, (table_array.Columns.Count + 1 + column_index)).Value
             
              End If
      
          Next i
      
       End If
   
    ElseIf column_index > 0 Then
   
       VLOOKUPPLUS = Application.WorksheetFunction.VLOOKUP(lookup_value, table_array, column_index, range_lookup)
   
    Else
   
       VLOOKUPPLUS = CVErr(xlErrNA): Exit Function
   
    End If

End Function
 

At a brief look, it appears ok, and well set out, but if you use the function a lot, it would be kind of slow (i.e. any resonably complex UDF is slower than worksheet functions).  I often use index(match()) combination for these purposes.

So if your lookup_range is in column D, and the return_values are in column B, you would enter something like:

index($B$1:$B$100,match(F1,$D$1:$D$100,lookup_type))

What's the best way to test the performance of both.....

Would this approach be a better way performance wise....?

 Public Function VLOOKUPPLUS(lookup_value, table_array As Range, column_index As Long, Optional range_lookup) As Variant

    Dim i As Long: Dim lookup_range As Range
   
    With Application.WorksheetFunction
   
    If column_index < 0 Then

       If table_array.Columns.Count + column_index < 0 Then VLOOKUPPLUS = CVErr(xlErrRef): Exit Function
      
       Set lookup_range = table_array.Offset(0, table_array.Columns.Count - 1).Resize(table_array.Rows.Count, 1)

       VLOOKUPPLUS = .Index(lookup_range.Offset(0, column_index + 1), .Match(lookup_value, lookup_range, 0))
  
    ElseIf column_index > 0 Then
   
       VLOOKUPPLUS = .VLookup(lookup_value, table_array, column_index, range_lookup)
   
    Else
   
       VLOOKUPPLUS = CVErr(xlErrNA)
   
    End If
   
    End With

End Function

Well, I would think that using the inbuilt functions should improve the performance, so I'd say yes. 

Although I don't know how much of a difference it makes running a WS function within code as opposed to in a cell formula.

-- Does anyone else have a comment here?

As a test, I created this macro,

Function addOne(v)
    addOne = v + 1
End Function

set A1= 1, A2= "=addOne(A1)" and filled down.  After a minute and a half of waiting I broke out of the calculation.  Reducing the number of formulas to 1000, the calculation time on my PC was about 7 seconds.  Compared to a complete column of the equivalent cell formula "=A1+1", which calculated in a flash of the screen, it's clear that the overheads involved in running VBA code can be significant.

Also: I just tried the same comparing 1500 vlookups with 1500 cells using this function,

Function myV(lookup_value, table_array, colindex, rangelookup)
    myV = Application.WorksheetFunction.VLookup(lookup_value, table_array, colindex, rangelookup)
End Function

again - the vlookups were pretty much instantaneous, while the myV function calculation took ~4 seconds.

While 4 seconds may not seem like much, these times are all based on simple dummy data, and with further dependencies, the differential in calculation time can grow to be significant (and frustrating!)

...But in any case, removing the looping thorugh cells in your original code (as you have done) is a good step.