2 Way Lookup using Xlookup to find Discount Price for Range of Quantities

Xlookup Syntax: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929

Below is an example of using a 2 way Xlookup that I learned from Linkedin Learning. 

1) Use xlookup to find the price of item

• xlookup(item we are looking for, item array from price grid, output price array from price grid)

2) Use 2 way xlookup to find the discount

• First xlookup the item against the item array from price grid
• Nested within is another xlookup the quantity against the quantity array from price grid
• Output the discount table where item and quantity intersect 
• match mode is -1 (exact match or next smaller numbe

3) Find the cost by finding the product of the price, quantity, and discount