Vlookup with requirements

[jessiew84]

Hello,

I have searched the web and threads but cannot seem to locate an answer to what I'm trying to achieve.

Is there a way to have Excel narrow the searchable results for a vlookup if other parameters are met first?

I have a long list of pack products that have subproducts under them. I only want Excel to search for subproducts based on the pack that is selected. Further more, I then need Excel to return a part number based on the qty they want to order within that subproduct.

In the example below if the customer were to select Premium Tires and enter in qty 2, I need excel to return the part # AAAAA-2.

I think I want to accomplish this with a vLookup function but I cannot seem to figure out how to incorporate the multiple levels of validation. Your help is greatly appreciate!

Pack Product	SubProduct	QTY	Part #
Premium Tires	Multi-tread	1	AAAAA-1
Premium Tires	Multi-tread	2	AAAAA-2
Premium Tires	Multi-tread	4	AAAAA-3
Standard Tires	Multi-tread	1	CCCCC-1
Standard Tires	Multi-tread	2	CCCCC-2
Standard Tires	Multi-tread	4	CCCCC-3

<TBODY>
</TBODY>

 

[NeonRedSharpie]

=lookif($D$1:$D$7,$A$1:$A$7,$F$2,$B$1:$B$7,$G$2,$C$1:$C$7,$H$2)

Where your data begins in A1 and is the above table.

F2 is your Pack Product
G2 is your SubProduct
H2 is your QTY

Function lookif(returnRng As Range, prodRng As Range, prod As Range, subRng As Range, subP As Range, _        qtyRng As Range, qty As Range) As Variant




    For Each Cell In prodRng
        If Cell.Value = prod.Value Then
            If subRng(Cell.Row) = subP Then
                If qtyRng(Cell.Row) = qty Then
                    lookif = returnRng(Cell.Row)
                    Exit Function
                End If
            End If
        End If
    Next Cell
    


    lookif = "Not Found"


End Function

 

[jessiew84]

Thank you, Neon. Is this for VBA?

 

[NeonRedSharpie]

It's a user defined function. So you place it in a module or personal workbook and then can call it from the worksheet. I'm not sure of a formula (though there might be one) that is native to excel.

 

[jessiew84]

Thank you for your help. I have zero experience with VBA. :-/ I'll see if I can figure this out. Thank you for your help.

 

[NeonRedSharpie]

Getting Started with Macros and User Defined Functions


Look up "#havemacro"