how to fix #NAME? in excel when you have tried lots of times

The most likely fix is to define or correct the named ranges Assigned_Level and Hourly, because Excel returns #NAME? when a formula refers to an unrecognized name.

Check the names used by the formula

1. In Excel, open Formulas → Name Manager.
2. Look for both:
   • Assigned_Level — the cells containing the levels to search.
   • Hourly — the corresponding cells containing the values to return.
3. Ensure neither name shows #REF! in Refers to, and ensure both ranges contain the same number of rows.
4. If either name is missing:
   • Select the appropriate cells.
   • Choose Formulas → Define Name.
   • Enter exactly Assigned_Level or Hourly.
   • Select OK.
5. Insert the names through Formulas → Use in Formula rather than typing them. This prevents spelling differences. Excel documents missing or misspelled defined names as causes of #NAME?. ^1^ Name Manager can create, edit, and inspect workbook names. ^2^

Then enter:

=XLOOKUP([@Level],Assigned_Level,Hourly,"",1)

The "" explicitly returns a blank if no result is found.

Check the table reference

[@Level] is a structured reference meaning “the Level value in this row.” It should be used inside an Excel table containing a column named exactly Level.

If the formula is outside that table, replace [@Level] with the relevant cell, for example:

=XLOOKUP(A2,Assigned_Level,Hourly,"",1)

Check whether Excel recognizes XLOOKUP

Start entering =XLOOKUP( and watch for XLOOKUP in Excel’s autocomplete list.

• If XLOOKUP appears: the function is recognized, so return to Name Manager and check Assigned_Level and Hourly.
• If it does not appear, or Excel changes it to _xlfn.XLOOKUP: the installed Excel is not currently recognizing the function. XLOOKUP is supported by Microsoft 365, but not by Excel 2016 or Excel 2019. ^3^

For Microsoft 365 on Windows:

1. Open File → Account.
2. Check that Office shows the expected Microsoft 365 product and is activated.
3. If it shows a different account, an expired license, or Product Deactivated, sign out of Office, close Excel, reopen it, and sign in with the Microsoft account that owns the Microsoft 365 license.
4. If _xlfn.XLOOKUP remains, open the workbook in Excel for the web through OneDrive. If XLOOKUP works there, save a fresh copy and reopen that copy in desktop Excel. This is a diagnostic fallback specifically for desktop Excel failing to recognize XLOOKUP. ^4^

Confirm the intended match behavior

Your final argument is 1, which means:

• Find an exact match; otherwise
• Return the next larger item.

If you require an exact level only, use 0 instead:

=XLOOKUP([@Level],Assigned_Level,Hourly,"Not found",0)

Excel uses exact matching by default, while match mode 1 requests the next larger value when no exact match exists. ^3^

Do not wrap this formula in IFERROR to hide #NAME?; correct the unrecognized name or function first. ^1^

References

1. How to correct a #NAME? error | Microsoft Support
2. Define and use names in formulas
3. XLOOKUP function
4. Excel XLOOKUP function no longer works in Office 2024 Home - Microsoft Q&A