### 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` 