BUY EXCEL BOOKS ONLINE: 1. VBA & Macros      2. VBA for Modelers      3. Excel 2013 VBA and Macros     
4. Excel VBA for Dummies      5. Excel with VBA & .NET      6. Mastering VBA      7. Excel 2013 Programming

Nth VLookup and HLookup Formulas (User Defined Function)

Problem: Many of us know "How to use VLOOKUP formula?" and are always troubled when we have more than 1 value in lookup column with the same name. Since VLOOKUP by default always return the value corresponding to first match.

Now you can use this user defined function to return Nth value in case of duplicate values.

Solution:

1. Copy the following VLOOKUPN or HLOOKUPN as desired in a module.
2. Use these formulas as desired like this:(only for reference)
VLOOKUPN(D1,A1:C500,3,2)

So, it will find second value of D1 in column A and return corresponding value from column C.




Function VLOOKUPN(Lookup_Value As Variant, Table_Array As Range, _
                  Col_Index As Integer, Nth_value)
   Dim nRow As Long 
   Dim nVal As Integer 
   Dim bFound As Boolean 
   VLOOKUPN = "Not Found"
   With Table_Array
      For nRow = 1 To .Rows.Count
         If .Cells(nRow, 1).Value = Lookup_Value Then nVal = nVal + 1
         If nVal = Nth_value Then 
            VLOOKUPN = .Cells(nRow, Col_Index).Text
            Exit Function 
         End If 
      Next nRow
   End With
End Function

Function HLOOKUPN(Lookup_Value As Variant, Table_Array As Range, _
                  Row_Index As Integer, Nth_value)
   Dim nCol As Long
   Dim nVal As Integer
   Dim bFound As Boolean
   HLOOKUPN = "Not Found"
   With Table_Array
      For nCol = 1 To .Columns.Count
         If .Cells(1, nCol).Value = Lookup_Value Then nVal = nVal + 1
         If nVal = nth_value Then
            HLOOKUPN = .Cells(Row_Index, nCol).Text
            Exit Function
         End If
      Next nCol
   End With 
End Function



Anonymous said...

Wonderful macro. A must keep for Personal workbook. Thanks ...