VLOOKUP formula written in English Language after open that file in in french Language then formula not working error #name

Question

we have one file that is created in English Language and VLOOKUP formula is added. this formula is working if we open file in English Language but some user using french Language and if that person open this file then formula is not working. error #name

We tried Formula Translator but that is also not working.

Answer 1

Hi @Mulik, Sandeep,

If using the VLOOKUP formula with the French equivalent function name RECHERCHEV under French language Excel, would this issue be fixed?

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,

This is likely due to the formula being language-specific. VLOOKUP has localized names in different language versions of Excel, and this can lead to compatibility issues when sharing files between users with different language settings.

You can use the INDEX and MATCH functions instead of VLOOKUP.

Here's how you can do it:

1. Assuming you can have this formula in vlookup: =VLOOKUP(A2, Sheet2!$A$2:$B$100, 2, FALSE)
2. Replace it with this formula: =INDEX(Sheet2!$B$2:$B$100, MATCH(A2, Sheet2!$A$2:$A$100, 0))

INDEX and MATCH are language-independent. So this should work for you.

Note- Please customize the code as per your needs.

Best Regards.