Forum Discussion
Xlookup not returning value for all cells that match criteria
It sounds like there's a calculation crunch in your workbook.
I'd start with using specific references in your XLOOKUP:The bolded parts need to be updated. Even if you point them at 10,000 rows, it's going be faster than having Excel check the entire column.
=XLOOKUP(G434,Pricing!E:E,Pricing!G:G,XLOOKUP('Full Ticket Listing'!E434&'Full Ticket Listing'!F434,Pricing!T:T,Pricing!W:W))
Unfortunatelly it is telling me that I cannot upload images or files. My nested xlookups however are below:
=XLOOKUP(G434,Pricing!E:E,Pricing!G:G,XLOOKUP('Full Ticket Listing'!E434&'Full Ticket Listing'!F434,Pricing!T:T,Pricing!W:W))
Whenever I change something in the sheet, be it apply or unapply a filter or change data in the lookup fields from my pricing tab, the issue moves. Previous cells that were returning as $0.00 will now return with the correct price, and other cells will have the problem of returning $0.00 when they were previouls showing the correct pricing. It will also grow and shrink, from only 9 cells being effected to 26, then shrink again to 12.
Apologies again that I am unable to upload any pictures or documents, I just barely made this account.
Unfortunatelly it is telling me that I cannot upload images or files.
I had suggested that you post on OneDrive or GoogleDrive if you weren't able to post here. You can still do that (again, making sure no confidential data is included)
=XLOOKUP(G434,Pricing!E:E,Pricing!G:G,XLOOKUP('Full Ticket Listing'!E434&'Full Ticket Listing'!F434,Pricing!T:T,Pricing!W:W))
If you have a new enough version of Excel, you might be able to do something different using the FILTER function. FILTER itself returns an array (using multiple criteria, as desired), so it can be nested within an XLOOKUP function that does its "looking" in the temporary virtual array returned by the FILTER function.
If you'll post your file (again, on OneDrive or GoogleDrive), we can take a look at whether that would work.
It sounds like there's a calculation crunch in your workbook.
I'd start with using specific references in your XLOOKUP:The bolded parts need to be updated. Even if you point them at 10,000 rows, it's going be faster than having Excel check the entire column.
=XLOOKUP(G434,Pricing!E:E,Pricing!G:G,XLOOKUP('Full Ticket Listing'!E434&'Full Ticket Listing'!F434,Pricing!T:T,Pricing!W:W))
- This turned out to be the solution.
I ended up converting the pricing sections that I was using as my lookup and return arrays into tables and now have excel looking at the columns in the tables rather than the entire columns themselves. As a bonus, my worksheet is now running much faster whenever I have to adjust a value.
Thank you!- Glad it worked! From what I've read, XLOOKUP is at its fasted when the lookup array is sorted or the match is approximate. That may explain the crunch with the column references.
