I've included an example, but my issue is getting an Xlook-up formula to work when you need to exact match for 3 columns and a range look-up for the 4th column.
The formula that I'm using right now is =XLOOKUP(I1&I2&I3&I4,A:A&B:B&C:C&D:D,F:F,,-1) and the issue for the margin look-up to the far right is when I put 5,500 for the Quantity it returns the 500 quantity margin. It's almost like the exact match of the 1st digit takes precedent in the look-up over the next 3 digits of 5,500.
Is there another way to go about this or do I need to combine multiple X-lookups?
Thanks!
The formula that I'm using right now is =XLOOKUP(I1&I2&I3&I4,A:A&B:B&C:C&D:D,F:F,,-1) and the issue for the margin look-up to the far right is when I put 5,500 for the Quantity it returns the 500 quantity margin. It's almost like the exact match of the 1st digit takes precedent in the look-up over the next 3 digits of 5,500.
Is there another way to go about this or do I need to combine multiple X-lookups?
Thanks!
| Group1 | Group2 | Group3 | Quantity FROM | Quantity To | MARGIN | Group1 | A | Margin | 40% | ||
| A | A | A | - | 24 | 5.0% | Group2 | A | ||||
| A | A | A | 25 | 49 | 10.0% | Group3 | A | ||||
| A | A | A | 50 | 99 | 15.0% | Quantity | 5,500 | ||||
| A | A | A | 100 | 199 | 20.0% | ||||||
| A | A | A | 200 | 299 | 25.0% | ||||||
| A | A | A | 300 | 399 | 30.0% | ||||||
| A | A | A | 400 | 499 | 35.0% | ||||||
| A | A | A | 500 | 999 | 40.0% | ||||||
| A | A | A | 1,000 | 1,999 | 45.0% | ||||||
| A | A | A | 2,000 | 3,999 | 50.0% | ||||||
| A | A | A | 4,000 | 5,999 | 55.0% | ||||||
| A | A | A | 6,000 | 9,999 | 60.0% | ||||||
| A | A | A | 10,000 | 16,000 | 65.0% | ||||||
| A | A | A | 16,001 | 19,999 | 70.0% | ||||||
| A | A | A | 20,000 | 29,999 | 75.0% | ||||||
| A | A | A | 30,000 | 39,999 | 80.0% | ||||||
| A | B | B | - | 24 | 5.0% | ||||||
| A | B | B | 25 | 49 | 10.0% | ||||||
| A | B | B | 50 | 99 | 15.0% | ||||||
| A | B | B | 100 | 199 | 20.0% | ||||||
| A | B | B | 200 | 299 | 25.0% | ||||||
| A | B | B | 300 | 399 | 30.0% | ||||||
| A | B | B | 400 | 499 | 35.0% | ||||||
| A | B | B | 500 | 999 | 40.0% | ||||||
| A | B | B | 1,000 | 1,999 | 45.0% | ||||||
| A | B | B | 2,000 | 3,999 | 50.0% | ||||||
| A | B | B | 4,000 | 5,999 | 55.0% | ||||||
| A | B | B | 6,000 | 9,999 | 60.0% | ||||||
| A | B | B | 10,000 | 16,000 | 65.0% | ||||||
| A | B | B | 16,001 | 19,999 | 70.0% | ||||||
| A | B | B | 20,000 | 29,999 | 75.0% | ||||||
| A | B | B | 30,000 | 39,999 | 80.0% | ||||||
| B | A | A | - | 24 | 5.0% | ||||||
| B | A | A | 25 | 49 | 10.0% | ||||||
| B | A | A | 50 | 99 | 15.0% | ||||||
| B | A | A | 100 | 199 | 20.0% | ||||||
| B | A | A | 200 | 299 | 25.0% | ||||||
| B | A | A | 300 | 399 | 30.0% | ||||||
| B | A | A | 400 | 499 | 35.0% | ||||||
| B | A | A | 500 | 999 | 40.0% | ||||||
| B | A | A | 1,000 | 1,999 | 45.0% | ||||||
| B | A | A | 2,000 | 3,999 | 50.0% | ||||||
| B | A | A | 4,000 | 5,999 | 55.0% | ||||||
| B | A | A | 6,000 | 9,999 | 60.0% | ||||||
| B | A | A | 10,000 | 16,000 | 65.0% | ||||||
| B | A | A | 16,001 | 19,999 | 70.0% | ||||||
| B | A | A | 20,000 | 29,999 | 75.0% | ||||||
| B | A | A | 30,000 | 39,999 | 80.0% |
