Product Code Lookup in Date Range

Steve emailed me this week, to see if I could help with a lookup problem. He needed to find a discount rate in a lookup table, based on a product code and a date range.

Here is how I solved the problem in Excel 2010 (using fake data), and please let me know if you have a different solution.

Product Pricing Sheet

On a separate sheet, there is a list of products and their prices. This list is formatted as a named Excel table – tblProducts. The two columns in the table are also named – ProdCodeList and ProdPriceList.

Promotions Sheet

On another sheet, there is a list of the promotions that have been offered. This table is named tblPromotions, and each column is also named. We’ll use those names in the formulas.

In this table, I added an ID column at the left, and entered a unique number in each row. The existing Promo Code column has text entries, and for my solution I needed a numeric ID in each row.

Orders Worksheet

On the Orders sheet, a record is created for each new order, with the order date, product code and quantity entered. This table is named tblOrders.

NOTE: Because the formulas are being created in a table, you’ll see column references, like [@Qty], instead of cell references.

You can use an INDEX / MATCH formula to get the product price, based on the product code:

=INDEX(ProdPriceList,MATCH([@[Prod Code]],ProdCodeList,0))

For the subtotal, multiply the product price by the quantity

=[@Qty]*[@[Unit Price]]

Find the Applicable Promotion

The next step is to check the promotions table, to see if there was a promotion for the selected product, when the order was placed.

To do that, I used a SUMIFS formula (NOTE: only available in Excel 2007 and later versions):

=SUMIFS(PromoIDList,
PromoStartList,"<=" & [@[Order Date]],
PromoEndList,">=" & [@[Order Date]],
PromoProdList,[@[Prod Code]])

This formula returns a number from the PromoID column, if a promotion is found that matches the criteria:

• Promo start date is on or before the order date
• Promo end date is on or after the order date
• Promo product code matches the order product code

If no matching promotion is found, the sum will be zero. Otherwise, the Promo ID will be the sum.

NOTE: This solution depends on there not being any overlaps in dates for a product promotion. Each promotion ends before another one for the same product begins.

Find the Promo Code

Once the Promo ID has been found, the Promo Code can be looked up, using INDEX and MATCH, based on the Promo ID.

=IFERROR(INDEX(PromoCodeList,
MATCH([@[Promo ID]],PromoIDList,0)),"N/A")

This formula returns a promo code, if a promotion is found that matches the promo ID. If there is no match, the result is “N/A”.

Find the Promo Discount

The same type of INDEX and MATCH formula is used to find the discount rate, based on the Promo ID.

=IFERROR(INDEX(PromoDiscList,
MATCH([@[Promo ID]],PromoIDList,0)),0)

This formula returns a discount, if a promotion is found that matches the promo ID. If there is no match, the result is 0.

Calculate the Total

Finally, to calculate the total amount, we multiply the subtotal by 100% minus the discount rate.

=[@Subtotal]*(1-[@Disc])

Download the Sample File

To download the sample file, please visit my Contextures website: Excel Sample Files – Functions Section.

Look for FN0023 – Product Code Lookup in Date Range

_____________________