borntorun75
Board Regular
- Joined
- Jul 12, 2010
- Messages
- 57
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.
<tbody>
</tbody>
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.
<tbody>
</tbody>
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
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.
A | B | C | D |
Product | Type | Effective Price Date | Price |
Hats | Bobble | 01/01/2000 | 20.00 |
Hats | Bobble | 01/04/2010 | 25.00 |
Hats | Baseball | 01/01/2000 | 15.00 |
Shirt | Blue | 01/01/2000 | 12.00 |
T-Shirt | Spotted | 01/01/2000 | 7.50 |
<tbody>
</tbody>
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.
Input Product | Hats |
Input Type | Bobble |
Input Price Date | 01/01/2000 |
Price (lookup) | 20.00 |
Input Product | Hats |
Input Type | Bobble |
Input Price Date | 01/04/2010 |
Price (lookup) | 25.00 |
Input Product | Hats |
Input Type | Bobble |
Input Price Date | 31/03/2009 |
Price (lookup) | 20.00 |
Input Product | Hats |
Input Type | Baseball |
Input Price Date | 20/10/2010 |
Price (lookup) | 15.00 |
Input Product | Hats |
Input Type | Rain |
Input Price Date | 01/02/2010 |
Price (lookup) | #N/A |
<tbody>
</tbody>
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