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.microsoft.com/en-us/microsoft-365/blog/2020/02/10/find-what-you-need-faster-xlookup-now-generally-available/ 

Other articles confirm it such as this one: https://excelexciting.com/how-to-return-xlookup-column-dynamically-in-excel-for-mac-and-windows/ 

 

I could only find one post which says that, XLOOKUP is not available on Mac: https://techcommunity.microsoft.com/t5/excel/excel-on-windows-pc-to-macbook/m-p/1830850 

 

Is there a way to get this function?

Excel on Mac
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Jul 01, 2021

    Bsct23 XLOOKUP is available on "Excel for Microsoft 365 for Mac". The post you referred to stating that is was not relates to a user that used an other Excel version for the Mac.

     

    I attach a small workbook with a simple XLOOKUP formula, create on a Mac. Open it and see if it works on your end. 

    • 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?

Resources