plz help for vlookup with duplicated item.

ZionX

New Member
Joined
Mar 17, 2011
Messages
11
Good days everyone, recently i writing the macro vba with vlookup, but the problem is a unique item might have 2 or more of specific value like this:

A a E 360
E F
B a F 480

the alphabeut with capital is the item, and the numeric is the value of it, "a" is assembly, above means that for the 1st "F", is under "E", and "E" is assembly of "A", so the value is 360. Then the 2nd "F" is assembly of "B", then the value sud be 480.

but now the problem is each time performed vlookup it only return the first matching only, value for "F" will always be 360, intead of 2nd "F" is 480.


Code:
Dim IngRows As Long
Dim rng As Range
IngRows = Range("G1").CurrentRegion.Rows.Count
 
Set rng = Range(Cells(2, 5), Cells(IngRows, 5))
rng = Application.VLookup(Application.VLookup(Range(Cells(2, 7), Cells(IngRows, 7)), Worksheets("COSTED_BOM").Range("C:AS"), 43, 0), Worksheets("CBOM").Range("A4:D10"), 4, 0)
above is the code for my vlookup.

i had been try with this:
Code:
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

but no luck.

Hope anyone can help me up, thanks so much.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Threads
1,108,911
Messages
5,525,591
Members
409,652
Latest member
strangelyangely

This Week's Hot Topics

Top