Forum Discussion
Xlookup and different regions
JKPieterse Here you have an example File: https://1drv.ms/x/s!AjwgDFM7OKYTg8AHMkW-H7ySHzkWAw?e=ZxGpqY
Password: BuscarX
You may find that the problem also happens in Excel online. But if you just click on the cells affected by #NAME error, it reprocesses it and shows the correct value.
Please don't Edit "Registro TPV Online" sheet nor "Bolcado", use "Bolcado para modificar" sheet to try.
Why have the formulas been saved as array formulas by pressing Ctrl+Shift+Enter? That isn't necessary anymore in Excel Online, nor in Excel in Microsoft 365/Office 2021.
You can update F3:G3, then fill down. That way you don't have to update ALL cells manually.
HansVogelaar array was the previous try to solve it.
The problem is that clicking and enter works in some languages, such as English or Catalan, but doesn't in some other languajes such as Spanish or French.
I'm sorry, I have no idea what causes this. I have never seen that happen before.
HansVogelaar The thing is that must be related to how Excel analizes Xlookup. I've seen that in Catalan in English the name for the function is XLOOKUP, but in Spanish it's BUSCARX. However, the file shows always XLOOKUP in every language, and it doesn't recognize it as XLOOKUP formula in any of them until clicked, because when clicked in English or Catalan it matches XLOOKUP=XLOOKUP but in Spanish it doen't match XLOOKUP=BUSCARX. Somehow, Excel detect the XLOOKUP parts of the formula as text, not as a function.
Other spreadsheets editors recognize it correctly, but always showing XLOOKUP instead of its localized term, and Google Sheets even shows the localized equivalent.