Vlookup Duplicate Value issue

Hi,

To ensure a better response please upload workbooks not pictures - see guidance in the rules area of the forum. People are reluctant to recreate your workbook when you already have a copy.

Try this array formula** entered in F2 and copied down:

=INDEX(B:B,SMALL(IF(A$2:A$10=D3,ROW(A$2:A$10)),COUNTIF(D$3:D3,D3)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

use VlookupNth:

Function VLOOKUPNTH(lookup_value, table_array As Range, _
    col_index_num As Integer, nth_value)
    ' Extension to VLOOKUP function. Allows for finding
    ' the "nth" item that matches the lookup value.

    Dim nRow As Long
    Dim nVal As Integer
    Dim bFound As Boolean
    VLOOKUPNTH = "Not Found"
    With table_array
        For nRow = 1 To .Rows.Count
            If .Cells(nRow, 1).Value = lookup_value Then
                nVal = nVal + 1
                End If
                If nVal = nth_value Then
                    VLOOKUPNTH = .Cells(nRow, col_index_num).Text
                    Exit Function
                End If
            Next nRow
        End With
    End Function

with the formula:

Formula:

=VlookupNth(D3,$A$2:$B$10,2,COUNTIF($D$3:D3,D3))

Please see attached spreadsheet with an array formula

Thank you for your feedback!

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.