Forum Discussion

Copper Contributor

Dec 28, 2023

Xlookup and different regions

Hello there! I've found some problem opening files that have been edited by other people using Excel configured in other regions with Xlookup formula, because it always appears as "XLOOKUP" instead ...

excel

Excel on Mac

Excel on mobile

Formulas and Functions

office 365

JKPieterse

Silver Contributor

Dec 29, 2023

Does it help to press control+shift+alt+F9 ?

Josue_Blasco

Copper Contributor

Dec 29, 2023

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.

HansVogelaar
MVP
Dec 29, 2023
Josue_Blasco

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.
- Josue_Blasco
  Copper Contributor
  Dec 29, 2023
  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.
  Catalan - Working.mp416 MB
  Spanish - Not working.mp417 MB
  - HansVogelaar
    MVP
    Dec 29, 2023
    Josue_Blasco
    
    I'm sorry, I have no idea what causes this. I have never seen that happen before.