Forum Discussion

Bsct23's avatar
Bsct23
Copper Contributor
Jul 01, 2021

XLOOKUP on a Mac

Hello,   My question regards XLOOKUP not running in Excel on a Mac. I use Office 365 in French. On microsoft.com, they say that XLOOKUP should 'generally' be available on a Mac: https://www.micro...
Excel on Mac
Bsct23's avatar
Bsct23
Copper Contributor
Jul 01, 2021

Riny_van_Eekelen 
Hi Riny,

Very kind of you!
The answer is yes and no.

Yes, when I open the XLOOKUP file you sent me, it does work, and I get '3' as a result.

But when I noticed that you added '_xlfn.' in the beginning of the formula (=_xlfn.XLOOKUP(F3;B4:B9;C4:C9;""), I wanted to copy it, in order to ask you why, but XLOOKUP stopped working and returned #NAME? instead of '3'.
So no, it does not work.
Note that if I type your formula in your spreadsheet, it returns #NAME?.

And if I drag drop down with the cross from the right bottom corner your XLOOKUP cell, the new cells at the bottom also return #NAME?
What is more, when I type the French equivalent of XLOOKUP, which is RECHERECHEX in the formula bar, the pop up box suggests RECHERCHE, RECHERCHEH and RECHERCHEV, but not RECHERECHEX, as if it  was unknown.
Though, your XLOOKUP file does work when opening it anew and doing nothing with it.

1. Do you know what causes this weird behavior, and how to solve it?
2. Why did you use '_xlfn.' before XLOOKUP?

mtarler's avatar
mtarler
Silver Contributor
Jul 01, 2021
he didn't add the _xlfn to the function, EXCEL did because it doesn't recognize that function name. The reason the correct value of '3' was there is probably because that is how it was saved. It sounds like your version does not have XLOOKUP. What version of EXCEL are you running? Just because it is available on the MAC doesn't mean the version you have has it. If you have 365 subscription, have you checked for and allowed updates?
  • Bsct23's avatar
    Bsct23
    Copper Contributor
    Jul 01, 2021
    Thanks for the _xlfn explanation.
    I of course did previously this update check and according to Microsoft Update, I do run the latest version, which is 16.50.
    What do you suggest?
    I run
    • Bsct23's avatar
      Bsct23
      Copper Contributor
      Jul 01, 2021
      Riny_van_Eekelen mtarler
      I rebooted my Mac and it fixed the issue.
      I hadn't not done it yet since installing Microsoft 365 the day before yesterday..
      Good to remember…
      Many thanks for your quick reactions!
      • EdgarTovar's avatar
        EdgarTovar
        Copper Contributor
        Sep 02, 2021

        Bsct23 I have exactly the same problem you had, I'm on Microsoft® Excel for Mac Version16.52 (21080801), and the XlookUp function doesn't work at all 😕 Any suggestion? 

Resources