Category: Excel Formulas

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

_____________________

Recently, my daughter, Sarah, broke her foot, and has had two surgeries, with orthopaedic specialists trying to put all the pieces back together.

I stayed with her for a few days after the latest surgery, to help her out.
One night, just as I was dozing off, I got a message on my iPhone. It was my daughter, texting from the next room.

• “Are you still awake?”
• “Yes, what do you need?”
• “I need help with Excel.”

That made me laugh, and I went in to see what help she needed.

Event Planning with Excel

Sarah is the event planner at Movember Canada, and is organizing launch parties for this year’s Movember events, across Canada.

“During November each year, Movember is responsible for the sprouting of moustaches on thousands of men’s faces, in Canada and around the world. With their “Mo’s”, these men raise vital funds and awareness for men’s health, specifically prostate cancer and male mental health initiatives.”

Of course, event planning is more difficult when you’re confined to your bed, but she’s been going non-stop, despite her injuries.

Apparently the surgical staff had to pry the phone from her hand in the last seconds before she was wheeled into the operating room. Now that’s job dedication!

Excel List of Invitees

Sarah had a list of invitees to the Movember events, and was trying to match email addresses with a short list of people who had not responded to their invitations.

The short list did not include the city name, and she wanted to pull that data from the original list.

Here, using some fake data that I created, is an example of the Excel worksheet, with the long list, and short list.

Solving Problem With Index and Match

Like many other Excel problems, this one can be solved with a combination INDEX/MATCH formula.

• The MATCH function finds the email in the original list, based on an exact match
• The INDEX function pulls the City from that email address’ row.

INDEX/MATCH Formula

In cell J4, I entered the following formula:

=INDEX($D$4:$D$1000,MATCH(I4,$E$4:$E$1000,0))

The formula looks for the city in column D, in the row where the matching email address was found in column E.

Copy Formula Down

Next, I copied the formula down to the last row in the short list, and all the cities showed up in the short list.

Finally, I copied the City cells in the short list, and pasted them as values, because the formulas weren’t needed any more.

Back to Sleep

It only took a couple of minutes to fix my daughter’s Excel problem, and she thanked me for coming to the rescue.

It warms a mother’s heart to know that her children grew up to be productive adults, who use Excel every day.  Sarah might not remember that formula later, but that will give her a reason to call me the next time that she’s stuck in Excel.

After helping out, I headed back to the guest room, and fell asleep. I’m not sure how much longer Sarah kept working, but probably way too long.

And if you’re growing a moustache in support of Movember, please let me know!

_____________________

Last year, I shared a technique for selecting a function name from a drop down list, and that changed the formulas in a summary row on the worksheet.

Select Function Name

For example, from the drop down list in cell C2:

• Choose the MAX function, to see the highest amounts in a lists of sales orders.
• Next, choose the SUM function, to see the total amount in the lists of orders
• Or, choose the COUNT function, to get the total count

This technique runs on formulas, not macros, and is a great way to show different information in a small amount of worksheet space.

It could be perfect for an interactive Excel dashboard, where people can choose the information they want to see.

Watch the Video

I’ve finally made a video that shows how to create this changeable summary, and create a drop down list of functions.

You can watch the video here, and for written instructions, and the sample file download, visit this page: Change Excel Function With Subtotals

As I show in the video, to alternate between functions, such as SUM, AVERAGE or MAX in a summary row, use the Subtotal function in your formulas.

Then, add a drop down list of functions on the worksheet, using the Excel data validation feature. Next, select the function you want, to see those results in the summary.

This technique does not require macros — the formulas create the changing summaries.

________________