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

---