XLNS

Archive for the ‘VLOOKUP’ Category

Extended VLOOKUP UDF Version 2.0 Beta

Wednesday, 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

Here is the link more readable code file.

Posted in UDF, VLOOKUP | 2 Comments »

Alternative for my Extended VLOOKUP UDF

Tuesday, April 8th, 2008

Rick Williams had a interesting comment on my Extended VLOOKUP UDF post.
He uses a index(match()) combination for these purposes.
I will look in to the performance comparison.

Here is the link to his reply on the Excel User Group.

Posted in UDF, VLOOKUP | No Comments »

Extended VLOOKUP UDF

Tuesday, 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