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

Comments

Post a Comment

Popular posts from this blog

Remove Duplicates

Image
One Way to do this is to use the UNIQUE funciton, which is what I often use. For example, I have several vendor codes for different invoice number. Use the Unique function and it will remove all duplicates and give you only the unique vendor codes. This is helpful when I do entry in the accounting software, especially when there is over 500+ lines of vendor codes. Alternatively, you can use ALT + A + M to remove duplicates, so definitely will start using that more!
Read more

Hlookup & Right

Image
HLOOKUP Per the website, "HLOOKUP stands for Horizontal Lookup and can be used to retrieve information from a table by searching a row for the matching data and outputting from the corresponding column. While VLOOKUP searches for the value in a column, HLOOKUP searches for the value in a row." Below, I wanted to return the number of Basis invoices paid on week 1, week 2, week 3, etc. I used the right formula here because the row index number is always one more than the week. Alternatively, on another sheet, I also just used an array constant that allowed me to use just one single formula to output the same result.
Read more

Index & Match - Reporting Billing

Index & Match "To summarize, INDEX gets a value at a given location in a range of cells based on numeric position. When the range is one-dimensional, you only need to supply a row number. When the range is two-dimensional, you'll need to supply both the row and column numbers." "The MATCH function is designed for one purpose: find the position of an item in a range" Now, using index and match together, I can find the billing numbers for particular client and month. Feel free to utilize the data validated dropdown to change client and month to find its respective billing numbers.
Read more