Forum Discussion

TeresaDF's avatar
TeresaDF
Copper Contributor
Jan 29, 2024

Xlookup - not found on the desktop version

Hi, 

 

I have just changed my Mac and browsed everything to the new one. Suddlenly, in the desktop version I do not have the XLOOPUP formula. 

 

And all my files are broken now, I see the formula only when using excel on the browser, but not in the normal version. 

 

The license is upgraded - 

 

Can you please help me?

 

Thanks

 

excel
Excel on Mac

5 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jan 31, 2024

    TeresaDF 

    If you have confirmed that you are using a version of Microsoft Excel that should support the XLOOKUP function (Excel 2019 or later, including Excel for Microsoft 365), and you are still experiencing issues, there are a few additional steps you can take:

    1. Check Formula Language:

      • Ensure that you are using the correct formula language. Formulas can sometimes be language-specific. The correct syntax for XLOOKUP in English is:

    =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

     

    Or in Portuguese…

    =PROCX(lookup_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode])

     

      • Make sure that your Excel language settings are set to English if in Portuguese use the second formula.

    2. Language Settings:

    • Go to "Excel" > "Preferences."
    • Under "International," check the "Preferred languages" settings.
      • Check your Excel language settings to make sure it is set to a language that supports the XLOOKUP function.

    3. Test with New Workbook:

      • Create a new, blank workbook and try using the XLOOKUP function. This helps determine if the issue is specific to your existing files.

    4. Repair Excel Installation:

    • Close Excel.
    • Open the "Applications" folder and find the "Microsoft Excel" application.
    • Hold down the "Control" key and click on "Microsoft Excel."
    • Choose "Show Package Contents."
    • Navigate to "Contents" > "Library" > "Application Support" > "Microsoft" > "Office."
    • Run the "Office Setup Assistant" or "Microsoft Database Utility" and choose the option to repair Excel.
      • Try repairing your Excel installation:

    I hope one of these steps helps resolve the issue. The text and the steps with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

    • Maxmardig's avatar
      Maxmardig
      Copper Contributor
      Jul 07, 2024

      NikolinoDE 

      Now Microsoft makes that formula available to Microsoft365 users only.

      It happened the same to me, and now it does not work. 

      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        Jul 07, 2024

        Maxmardig 

        If the XLOOKUP function is not available because it is restricted to Microsoft 365 users and you need a non-VBA alternative that works on Excel versions prior to Microsoft 365, you can use a combination of INDEX and MATCH functions to achieve similar functionality. Here is how you can replace XLOOKUP with these functions:

        Example Scenario

        Assuming you have the following data:

        • lookup_value is in cell A1
        • lookup_array is in range B1:B10
        • return_array is in range C1:C10

        XLOOKUP Equivalent Using INDEX and MATCH

        The general formula for XLOOKUP:

        =XLOOKUP(A1, B1:B10, C1:C10, "Not Found")

        Equivalent Formula Using INDEX and MATCH

        You can use the following formula to achieve the same result:

        =IFERROR(INDEX(C1:C10, MATCH(A1, B1:B10, 0)), "Not Found")

        Explanation

        • MATCH(A1, B1:B10, 0) finds the position of A1 in the range B1:B10.
        • INDEX(C1:C10, ...) uses this position to find the corresponding value in the range C1:C10.
        • IFERROR(..., "Not Found") handles cases where the lookup value is not found, similar to the [if_not_found] argument in XLOOKUP.

        Steps to Implement the Formula

        1. Identify the ranges for your lookup value, lookup array, and return array.
        2. Use the INDEX and MATCH formula with the ranges identified.
        3. Wrap the formula with IFERROR to handle not found cases.

        Example with Real Data

        Here is an example with some data:

        • Lookup value is in cell A1 with the value "apple"
        • Lookup array is in cells B1:B5 containing {"apple", "banana", "cherry", "date", "elderberry"}
        • Return array is in cells C1:C5 containing {1, 2, 3, 4, 5}

        In cell D1, you would enter:

        =IFERROR(INDEX(C1:C5, MATCH(A1, B1:B5, 0)), "Not Found")

        Handling Multiple Conditions (Advanced Use Case)

        If you need to perform a lookup with multiple conditions, you can use an array formula (CTRL+SHIFT+ENTER on Windows or CMD+RETURN on Mac) to handle the criteria.

        For example, if you need to find the value in C1:C10 based on two criteria in A1 and A2 corresponding to ranges B1:B10 and D1:D10:

        =IFERROR(INDEX(C1:C10, MATCH(1, (B1:B10=A1)*(D1:D10=A2), 0)), "Not Found")

        Remember to enter this as an array formula.

        Conclusion

        Using INDEX and MATCH functions provides a versatile and powerful way to perform lookups in Excel, which is compatible with older versions of Excel that do not support XLOOKUP. This method ensures that your formulas remain functional regardless of the Excel version you or your colleagues are using. The text, steps and functions were created with the help of AI.

         

        My answers are voluntary and without guarantee!

         

        Hope this will help you.

        Was the answer useful? Mark as best response and Like it!

        This will help all forum participants.

Resources