multiple criteria vlookup using date (1 Viewer)

[coolhandphil]

Hello,

I'm using Excel 2010...

I have a large worksheet (about 12k rows) with three columns of products/prices. I need some formula which will find the price of a barcode at a given date. e.g. what is the price of 10009 on 3/1/15? (It would be at the last provided price, i.e. £1.10). Any ideas?

Here is my data:

Barcode......Date......Price
10009......1/1/15......£1
10009......2/1/15......£1.10
10009......4/1/15......£1.11
10009......5/1/15......£1.09
10010......1/1/15......£0.99
10010......3/1/15......£1.05
10010......4/1/15......£1.09
10010......5/1/15......£1.05


Thanks,

Phil

 

[CJ_London]

In a query you would use something like

 SELECT Price
 FROM myTable
 WHERE BDate=(SELECT Max(Bdate) FROM myTable as T WHERE Barcode=myTable.BarCode and BDate<=[Enter a Date here]) AND BarCode=[Enter a BarCode here]

 

[namliam]

That query wont quite work CJ, due to the placing of your barcode, it will break unfortunatly ...

But try something like:

SELECT Price
FROM myTable
Inner Join ( SELECT BarCode, Max(Bdate) MaxDate
             FROM myTable as T
             WHERE BDate<=[Enter a Date here]
               AND BarCode=[Enter a BarCode here] 
             Group by BarCode  ) as x on mytable.barcode = x.barcode
                                     and mytable.bdate = x.maxdate

Remember that the [Enter a date here] must be in US format (MM/DD/YYYY)

Note that its BDate and not Date, because date is a reserved word and shouldnt be used as a column name

 

[coolhandphil]

Sorry, I should have said... This is in Excel not Access. I've updated my first post.

That said, it gave me the idea to try using VBA rather than formula... I wrote the following which is very slow but working...

Function GetCostPrice(x, y As Range)
Dim lBC As String
Dim dDate As Date
Dim lCurrentRow As Long
Dim dbCostPrice As Double

lBC = x
dDate = y

With Worksheets("CatCostPrices").Range("B:B")
    Set C = .Find(lBC, LookIn:=xlValues)
    If Not C Is Nothing Then
        lCurrentRow = C.Row
        While Sheets("CatCostPrices").Range("B" & lCurrentRow) = lBC
            If Sheets("CatCostPrices").Range("C" & lCurrentRow) <= dDate Then
                dbCostPrice = Sheets("CatCostPrices").Range("D" & lCurrentRow)
            Else:
                lCurrentRow = lCurrentRow + 500
            End If
            lCurrentRow = lCurrentRow + 1
        Wend
        Debug.Print dbCostPrice
        GetCostPrice = dbCostPrice
    Else:
        Exit Function
    End If
End With

End Function

 

[NBVC]

Here is a formula you can try:

=LOOKUP(2,1/((A:A=F1)*(B:B<=G1)),C:C)

To make it more efficient you could replace whole column ranges with known defined ranges or dynamic named ranges (if they are growing).

 

[coolhandphil]

Here is a formula you can try:

=LOOKUP(2,1/((A:A=F1)*(B:B<=G1)),C:C)

To make it more efficient you could replace whole column ranges with known defined ranges or dynamic named ranges (if they are growing).

Thanks, this is working.