Xlookup and different regions

Copper Contributor

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?

8 Replies
Does it help to press control+shift+alt+F9 ?
No, it doesn't solve it.

@Jan Karel Pieterse  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.

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

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

@Josue_Blasco 

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.

@Josue_Blasco 

Does it work if change nested XLOOKUP on something like

=IF(
    C3="", "",
    IFNA( XLOOKUP(
        C3,
        'Registro TPV Online'!A:A,
        'Registro TPV Online'!I:I),
    IFNA( XLOOKUP(
            D3,
            'Registro TPV Online'!A:A,
            'Registro TPV Online'!I:I ),
    IFNA( XLOOKUP(
            D3,
            'Registro TPV Online'!B:B,
            'Registro TPV Online'!I:I),
        "No"
            )
        )
    )
)