Forum Discussion

Josue_Blasco's avatar
Josue_Blasco
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 of its equivalent (BUSCARX in Spanish) so Excel returns #NAME error

Also, if someone edits it with Excel in English, then someone with other language (e.g. Spanish) opens and saves it again, the person in English will find the #NAME error as well while the formula hasn't changed. Clicking the cell in this case will make Excel "rethink" the formula and work, but needs to be done on every cell.

Any ideas?

    • Josue_Blasco's avatar
      Josue_Blasco
      Copper Contributor

      JKPieterse  Here you have an example File: Buscarx.xlsx

      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's avatar
        HansVogelaar
        MVP

        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.

Resources