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**

_____________________

For those with 2003 or earlier (and so no SumIfs), use SumProduct like this:

=SUMPRODUCT(PromoIDList,--(PromoStartList=[@[Order Date]]),--(PromoProdList=[@[Prod Code]]))

--(Some TRUE FALSE Array) gives you 1's and 0's that SumProduct will multiply together and then add up for you.

For example, SUMPRODUCT(--(PromoStartList=7) gives you the number of time PromoStartList equals 7. The -- part is what turns the TRUE or FALSE results into 1's and 0's.