Looking up to a table with a non-exact date (effective date)

[borntorun75]

Hi,

I'm trying to do a lookup / match based on both on specific criteria (Product and Type), but also on a date that isn't exact.

Here is a sample of the table. It's a list of products, types and Effective Price Dates.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD]Type[/TD]
[TD]Effective Price Date[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]Hats[/TD]
[TD]Bobble[/TD]
[TD]01/01/2000[/TD]
[TD]20.00[/TD]
[/TR]
[TR]
[TD]Hats[/TD]
[TD]Bobble[/TD]
[TD]01/04/2010[/TD]
[TD]25.00[/TD]
[/TR]
[TR]
[TD]Hats[/TD]
[TD]Baseball[/TD]
[TD]01/01/2000[/TD]
[TD]15.00[/TD]
[/TR]
[TR]
[TD]Shirt[/TD]
[TD]Blue[/TD]
[TD]01/01/2000[/TD]
[TD]12.00[/TD]
[/TR]
[TR]
[TD]T-Shirt[/TD]
[TD]Spotted[/TD]
[TD]01/01/2000[/TD]
[TD]7.50[/TD]
[/TR]
</tbody>[/TABLE]


I'd like to do a lookup based on the first 3 input contents, and do a lookup/match to the price. The issue is that the price can be based on an Effective Date that isn't in the source data. So, while I want to have an exact match on the Product and Type, the Price Date in the table is an effective date. The formula result of the lookup/match is shown in red.

The first 2 examples below work fine as they have exact matches to the table.

The next 2 examples, however, have an Input Price Date that isn't in the table. So, the lookup needs to have that flexibility to work on a principal of on/after the date in the table.

The final example is for a product that doesn't exist in the table. So, accordingly, I'd need to trap that.

I'm trying to achieve this in formula rather than VBA.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Input Product[/TD]
[TD]Hats[/TD]
[/TR]
[TR]
[TD]Input Type[/TD]
[TD]Bobble[/TD]
[/TR]
[TR]
[TD]Input Price Date[/TD]
[TD]01/01/2000[/TD]
[/TR]
[TR]
[TD]Price (lookup)[/TD]
[TD]20.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Input Product[/TD]
[TD]Hats[/TD]
[/TR]
[TR]
[TD]Input Type[/TD]
[TD]Bobble[/TD]
[/TR]
[TR]
[TD]Input Price Date[/TD]
[TD]01/04/2010[/TD]
[/TR]
[TR]
[TD]Price (lookup)[/TD]
[TD]25.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Input Product[/TD]
[TD]Hats[/TD]
[/TR]
[TR]
[TD]Input Type[/TD]
[TD]Bobble[/TD]
[/TR]
[TR]
[TD]Input Price Date[/TD]
[TD]31/03/2009[/TD]
[/TR]
[TR]
[TD]Price (lookup)[/TD]
[TD]20.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Input Product[/TD]
[TD]Hats[/TD]
[/TR]
[TR]
[TD]Input Type[/TD]
[TD]Baseball[/TD]
[/TR]
[TR]
[TD]Input Price Date[/TD]
[TD]20/10/2010[/TD]
[/TR]
[TR]
[TD]Price (lookup)[/TD]
[TD]15.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Input Product[/TD]
[TD]Hats[/TD]
[/TR]
[TR]
[TD]Input Type[/TD]
[TD]Rain[/TD]
[/TR]
[TR]
[TD]Input Price Date[/TD]
[TD]01/02/2010[/TD]
[/TR]
[TR]
[TD]Price (lookup)[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

Any suggestions would be appreciated. For the table above, I can use helper columns to work a solution. I have flexibility.

I've tried using an INDEX / MATCH formula, but I can't get it to work with that effective date.

Best regards, Mike

 

[steve the fish]

The following assumes that the original table is in A1:D6, the product to search in F2, the type in G2 and the date in H2. You also need to make sure that the dates in the table are sorted from earliest at the top to latest at the bottom (which makes sense as you add new prices)

=LOOKUP(2,1/(($A$2:$A$6=F2)*($B$2:$B$6=G2)*($C$2:$C$6<=H2)),$D$2:$D$6)

 

[borntorun75]

The following assumes that the original table is in A1:D6, the product to search in F2, the type in G2 and the date in H2. You also need to make sure that the dates in the table are sorted from earliest at the top to latest at the bottom (which makes sense as you add new prices)

=LOOKUP(2,1/(($A$2:$A$6=F2)*($B$2:$B$6=G2)*($C$2:$C$6<=H2)),$D$2:$D$6)

Thank-you so much. This works beautifully from what I've seen. I note your comment about dates being in order - absolutely that would be the case. The data table itself, in my spreadsheet, is a pivot table which by default would sort the dates into ascending order. It's right to point it out though of course.

Thanks so much 'steve the fish' - this is exactly what I was looking for.

best regards, mike