Forum Discussion

Copper Contributor

Oct 03, 2022

Solved

Xlookup not returning value for all cells that match criteria

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

excel

Formulas and Functions

office 365

Patrick2788
Oct 04, 2022
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))

Patrick2788

Silver Contributor

Oct 04, 2022

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

Peter9327

Copper Contributor

Oct 04, 2022

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!

Patrick2788
Silver Contributor
Oct 04, 2022
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.