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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,980
Members
430,100
Latest member
namhnz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top