Xlookup not returning value for all cells that match criteria

[Peter9327]

I am handling data on a month to month basis of around 800-1300 tickets so I needed an easy way to edit prices that change on a month to month basis for both a Sale and Purchase side of transactions.

I am using an xlookup nested within another xlookup to find pricing for tickets based off of multiple criteria. 

The first xlookup I use a single criteria to lookup my prices, at the end of this xlookup I have nested another xlookup should the first have no value found. I am using merged cells with the "&" to find a unique identifer for this second xlookup that I am referencing to another list to find my ultimate price. 

This method has worked for me for around 3 months. However, in the latest sheet that I have created, the xlookup is not returning prices for cells that have the same criteria as other cells with the same criteria. When I filter down to show cells with a 0 value, excel takes a moment to update and will place a price in cells after the filter. 

I've never encounted this issue before with any kind of lookup I've used in the past. 

[#2]

@Peter9327 

As of this writing, you've had over 50 views and no replies. Let me suggest that you help us help you by finding a way to post your file (or an accurate mockup, if the actual contains confidential data), so that we can see the actual layout of the data AND the formula itself. You refer to it as a formula that contains an XLOOKUP function nested in yet another XLOOKUP,  but don't actually show the formula. That makes it hard to offer any help beyond a platitude--"hang in there."

If you are not able to post the file here, post it in OneDrive or GoogleDrive (or some comparable cloud service), and post a link here. Make sure to grant access as well so that we can open it.

[#3]

You need to specify more details about this issue, I suggest you have some files or screenshots attached. Seems more like there is some mistyping error in XLOOKUP function.

[#4]

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. 

[#5 · Accepted solution]

@Peter9327 

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))

[#6]

@Peter9327 

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.

[#7]

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!

[#8]

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.