'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix XLOOKUP #NAME? Error with Dynamic Formula References
Question details
The user needs to fix an XLOOKUP formula that fails to recognize dynamically built cell references and returns a #NAME? error.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Attempting to perform an XLOOKUP search across different worksheets where the target sheet name or lookup array is dynamically generated from another cell's formula.
- Observed behavior
- The formula fails to evaluate the constructed text as a valid range, returning a #NAME? error instead of looking up the intended data.
Verify that your current spreadsheet software version supports the XLOOKUP function, as opening a workbook with XLOOKUP in older versions will automatically result in a #NAME? error regardless of formula syntax.
Wrap Dynamic References with the INDIRECT Function
Use the INDIRECT function to convert concatenated text strings into valid worksheet and cell references that XLOOKUP can process.
When you build a range reference dynamically using the ampersand (&) operator, the spreadsheet reads it as a standard text string, not a usable range. This causes lookup functions to fail. Wrapping the text string in the INDIRECT function forces the software to evaluate the text as an actual cell or range reference.
Locate the portion of your XLOOKUP formula that dynamically builds the sheet name. For example, if cell C19 contains the sheet name, your string might look like "'"&C19&"'!$I3:$I33".
Wrap the constructed text for the lookup array in the INDIRECT function. Type INDIRECT("'"&C19&"'!$I3:$I33").
Similarly, wrap the return array reference in the INDIRECT function. Type INDIRECT("'"&C19&"'!$E3:$E33").
Combine these elements into your XLOOKUP formula. Enter =XLOOKUP(1,INDIRECT("'"&C19&"'!$I3:$I33"),INDIRECT("'"&C19&"'!$E3:$E33"),"-",0,1) and press Enter.
Correct Dynamic Date String Generation
Ensure that the dynamic text generating worksheet names based on dates perfectly matches the actual sheet tab names without syntax errors.
Use WPS Spreadsheet for Advanced Lookup Functions
WPS Office fully supports advanced dynamic array functions like XLOOKUP and INDIRECT out of the box. Its powerful calculation engine ensures that your complex, formula-based dynamic references work smoothly across all your reports.
- 1. Open your spreadsheet: Launch WPS Spreadsheet and open the workbook containing the #NAME? error.
- 2. Locate the error: Select the cell with the broken XLOOKUP formula and click into the formula bar at the top.
- 3. Wrap with INDIRECT: Modify your concatenated text references by typing INDIRECT() around them.
- 4. Evaluate the formula: Press Enter to execute the corrected formula and retrieve the correct dynamic data.
Frequently Asked Questions
Why does XLOOKUP return a #NAME? error even when I'm not using dynamic references?
A #NAME? error typically occurs if there is a typo in the function name itself (for example, typing XLOOKP instead of XLOOKUP), or if you are using an older version of the spreadsheet software that does not recognize the XLOOKUP function.
Can I use INDIRECT with XLOOKUP across closed workbooks?
No, the INDIRECT function requires the referenced external workbook to be actively open in your spreadsheet application. If the target workbook is closed, INDIRECT will return a #REF! error, which subsequently causes the XLOOKUP function to fail.
What does the "0,1" at the end of the XLOOKUP formula mean?
In the formula =XLOOKUP(..., 0, 1), the '0' specifies the match mode, meaning it will look for an exact match. The '1' specifies the search mode, instructing the function to search from the first item to the last item in the array.
