From
exceljet.net
Email Address
contact@exceljet.net
Sent On
Fri, May 19, 2023 01:11 PM
Email Preheader Text
Hi - Dave here. Happy Friday! An interesting problem in Excel is how to calculate quantity-based dis
HTML
Hi - Dave here. Happy Friday! An interesting problem in Excel is how to calculate quantity-based discounts. In the worksheet below, the goal is to calculate a discount for each item based on quantity using the discounts in the table to the right. The purpose of the table is to allow each item to have its own set of discounts. Notice that Donuts have a different discount for a quantity of 24. The discounts for other items can be customized as well. This is a classic two-way lookup problem. The formula must perform an exact match on the item name and an approximate match on the quantity. The formula in E5 is: =XLOOKUP(B5,item,XLOOKUP(C5,quantity,discount,0,-1),0) Where item, quantity, and discount are named ranges as shown: [Calculating discounts based on item and quantity]( [[Click here for all the details](] Notice XLOOKUP is used twice in the formula above. Once for the quantity, and once for the item. This works very nicely, but it can be confusing to have one XLOOKUP inside another. Another way to solve this problem is with INDEX and MATCH, which will be more intuitive for many users. The article above explains both formulas in detail. Download the workbook and give it a try. Excel formulas The only way to be good with Excel formulas is to practice. We maintain a list of over 500 working formulas [here](. If you need more structure, we also offer [video training](. Have a great weekend! Dave PS - Just a reminder that if you want to learn how to automate tasks in Excel with VBA and Macros, [Victor's course]( is on sale until next Wednesday, May 24. [View email in browser]( [Exceljet Logo]( Exceljet
P.O. Box 4804
Salt Lake City, UT 84110 Copyright © 2023 Exceljet, All rights reserved.
You received this email because you are subscribed to our newsletter.
To unsubscribe, click the link below. [Unsubscribe](
EDM Keywords (51)
worksheet
works
workbook
well
way
want
vba
unsubscribe
table
subscribed
structure
solve
set
sale
right
reminder
received
quantity
purpose
problem
practice
nicely
newsletter
need
match
maintain
list
link
learn
items
item
intuitive
index
good
goal
give
formulas
formula
explains
excel
email
e5
donuts
discounts
discount
customized
course
confusing
calculate
article
allow
24