Forum Discussion
pasayten
Dec 14, 2024Copper Contributor
Apparently, I broke xlookup() in excel
I created a large spreadsheet to help our local electrical coop set new rates. The spreadsheet is CV x 4200 cells, filled with multiple columns filled with equations. The original spreadsheet had member account numbers (all numeric) in a column, but not their member first/last names. Then they decided they wanted the names in the file also.
I figured to use xlookup and added a sheet 2 with the columns account number and names. Then I went back to the main sheet 1 and added a column with xlookup to scan sheet 2 and grab the names. The first 1000-2000 rows filled pretty well, then I started to get a lot of N/A's for the rest of the rows except for a few matches here and there. Tried many of the "usual" fixes ensuring the source data and lookup columns were truly numeric. No fix of the problem.
Then I figured it might be a memory stack overflow or something. I cleaned up many columns of equations by replacing them with there actual result numbers. Copy and paste "123". Suddenly, all the xlookups started to work as expected.
So, it appears there is some kind of memory or size limit involving xlookup without triggering an error exception.
Should this be reported somewhere?
I cannot share the spreadsheet as it contains sensitive data.
Thanks in advance!
pasayten
Most probably that's not due to resources as memory. That could be text instead of number, non printable character in the value, whatever. To be sure, enter in any empty cell (cell1) "123" and compare with the cell which returns result looks like "123" (cell2), entering in any other empty cell
= cell1 = cell2
if it returns TRUE or FALSE
Better if you share the sample, just few cells is enough. Copy/Paste as value (not re-write) any formula result and formula which xlookups it.