Home > Excel, UDF > Extended VLOOKUP UDF Version 2.0 Beta

Extended VLOOKUP UDF Version 2.0 Beta

May 17th, 2011 admin

April 9th, 2008

Thinking about Rick Williams remarks I rewrote the VLOOKPLUS function to perform better.
For an even better performance I shall have to rewrite the function in C/C++ or VB.Net.
Here is the result for now.

( It’s only good for exact matches when working with a negative column_index ).

Public Function VLOOKUPPLUS(l_v, t_a As Range, c_i As Long, Optional r_l) As Variant

   Dim i As Long: Dim l_r As Range

   With Application.WorksheetFunction

      If c_i < 0 Then

         If t_a.Columns.Count + c_i < 0 Then VLOOKUPPLUS = CVErr(xlErrRef): Exit Function

         Set l_r = t_a.Offset(0, t_a.Columns.Count - 1).Resize(t_a.Rows.Count, 1)

         VLOOKUPPLUS = .Index(l_r.Offset(0, c_i + 1), .Match(l_v, l_r, 0))

      ElseIf c_i > 0 Then

         VLOOKUPPLUS = .VLookup(l_v, t_a, c_i, r_l)

      Else

         VLOOKUPPLUS = CVErr(xlErrNA)

      End If

   End With

End Function
Categories: Excel, UDF Tags: ,
Comments are closed.