Home > Excel, UDF > Extended VLOOKUP UDF

Extended VLOOKUP UDF

May 17th, 2011 admin

April 8th, 2008

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


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

   Dim i As Long

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

   If r_l = 0 Then
      For i = 1 To t_a.Rows.Count
         If t_a.Cells(i, t_a.Columns.Count).Value = l_v Then
            VLOOKUPPLUS = t_a.Cells(i, (t_a.Columns.Count + 1 + c_i)).Value
            Exit Function
         End If
         VLOOKUPPLUS = CVErr(xlErrValue)
         Exit Function
      Next i
   Else
      For i = 1 To t_a.Rows.Count
         If t_a.Cells(i, t_a.Columns.Count).Value <= l_v Then
            VLOOKUPPLUS = t_a.Cells(i, (t_a.Columns.Count + 1 + c_i)).Value
         End If
      Next i
   End If
   ElseIf c_i > 0 Then
      VLOOKUPPLUS = Application.WorksheetFunction.VLookup(l_v, t_a, c_i, r_l)
   Else
      VLOOKUPPLUS = CVErr(xlErrNA)
   End If

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