Log In
Register
Home
New Member
February 11, 2022
Offline
Hoping to get some guidance on attached workbook, i'm trying to use multiple criteria XLOOKUP formula =XLOOKUP($N15&O$13,$B:$B&$G:$G,$C:$C,,-1,) to get some tiered pricing data out of MS Access into a pricelist table with qty columns and a row for each code showing the matching price in each cell for the qty column. Some items have the same price for any qty other items have a minimum qty and different prices for different quantities. I've never used multiple criteria before and the issue im having is if the qty for the column is below minimum qty it should show NA but is bringing in some other number and also when it isnt an exact match it isnt looking to the next smaller qty as it should be.
Glad of any help on this
Or should i look at using an INDEX MATCH formula - i haven't used these since XLOOKUP came along.
Al
See Answer
Trusted Members
Moderators
Power BI
January 31, 2022
Offline
Keep in mind that when you concatenate values like 229/057 & 25, you create a text "229/05725". Compare that to 229/057 & 5 which becomes the text "229/0575" and then the latter is greater than the first. So, it's actually a coincidence that some of the values returned are correct.
Perhaps you can use FILTER or XMATCH to return the correct values. And example for FILTER could be:
=FILTER(C2:C17,(B2:B17=N16)*(G2:G17<=O13))
This will return a #CALC error as there is no match. Something you expect in O16, isn't it.
July 31, 2020
Offline
Another option
=IF($N14="","",INDEX(SORT(FILTER($C$2:$C$17,($B$2:$B$17=$N14)*($G$2:$G$17<=O$13),"NA")),1))
Answers Post
1 Guest(s)
