Formula looking up price based on sku and quantity

[whitelanser]

I am struggling to figure out the formula for doing this. I research index formula and alike and just can't pull it together. trying to make a quick look up sheet, which is the second picture. I want to type in the sku in row 3+ and then have formula's to pull the description and price. The description is easy. But I am trying to pull in the correct price based on the qty I type into column C of the second picture. The price formula in D3, would then go to the next worksheet, picture 1, and based on the name/sku then based on the qty return the appropriate price, which is in column E of the first picture.

Current formula returns a n/a and is in cell d3 of the second picture. =INDEX(sheet2!E43:E3048, MATCH(C3, COUNTIF(Sheet1!C3,sheet2!E43:E3048), 0))

thx for any help.

 

[RobP]

Hi,

you might find it easier to work out if you were able to have more columns, whereby across the top of each you have the Qty Range defined (say in Row1), then you put each price break underneath that.

So, Col D example has heading "1+", Col E has say "10+", Col F has "100+" etc. with relevant prices below in the columns.

This would remove duplicates in your data rows to start, and allow you to reference (or lookup) at a fixed point ie. Row 1 of each column which price you want to bring back .

Make sense ?

Rob

 

[whitelanser]

Hi,

you might find it easier to work out if you were able to have more columns, whereby across the top of each you have the Qty Range defined (say in Row1), then you put each price break underneath that.

So, Col D example has heading "1+", Col E has say "10+", Col F has "100+" etc. with relevant prices below in the columns.

This would remove duplicates in your data rows to start, and allow you to reference (or lookup) at a fixed point ie. Row 1 of each column which price you want to bring back .

Make sense ?

Rob

Yes makes sense. And I agree. Unfortunately I am using a vendor's sheet and don't have control over that.

 

[Anthony47]

Maybe a "User Function"
For example:
-copy the following code into a Standard module of your vba project:

Function GimmePrice(ByVal Sku As String, Qty As Long, ByRef PropTable As Range) As Single
Dim PrCol As Long, PnCol As Long, QtPr As Long, myMatch
Dim wArr, PropArr, hArr(0 To 1000) As Single
Dim I As Long, J As Long, mySplit, cCnt As Long
'
PnCol = 1       'Sku col
PrCol = 5       'Price col
QtPr = 6        'per qty col
'
PropArr = PropTable.Value
wArr = Application.WorksheetFunction.Index(PropTable, 0, PnCol)
myMatch = Application.Match(Sku, wArr, False)
If Not IsError(myMatch) Then
    For I = myMatch To UBound(PropArr)
        If PropArr(I, PnCol) = Sku Then
            mySplit = Split(Replace(PropArr(I, QtPr), "+", "-1000", , , vbTextCompare), "-", , vbTextCompare)
            For J = CLng(mySplit(0)) To CLng(mySplit(1))
                hArr(J) = PropArr(I, PrCol)
                cCnt = cCnt + 1
            Next J
        Else
            Exit For
        End If
    Next I
    If cCnt > 0 Then
        GimmePrice = hArr(Qty)
    End If
End If
End Function

Now the function GimmePrice is available for you with the following syntax:
GimmePrice(Sku, Qty, PropertyTable)
Sku is a string
Qty is a Integer
PropertyTable is the address of your first picture

For example:

=GimmePrice(A2;C2;Sheet2!A1:F100)

Try...