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
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.
Another option
=IF($N14="","",INDEX(SORT(FILTER($C$2:$C$17,($B$2:$B$17=$N14)*($G$2:$G$17<=O$13),"NA")),1))
Sorry i hadnt been back on here. thanks for your responses - Fluff that works great - now i'll try and figure out how it works!! thanks again
Glad to help & thanks for the feedback
