How to Use INDIRECT to Reference a Table Name from a Lookup Result in Excel
Question details
The user wants to know how to use an Excel lookup result as a dynamic reference to a table or defined range within another formula.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Building dynamic formulas where a lookup function returns the text name of a table or named range, and that name needs to be evaluated as an actual functional reference.
- Observed behavior
- The lookup result successfully returns the table name, but treats it as a text string rather than a valid cell or table reference, preventing further calculations.
Ensure that the named ranges or tables you want to reference already exist in your workbook and that their defined names match exactly with the text returned by your lookup formula.
Wrap the Lookup Formula with the INDIRECT Function
Use the INDIRECT function to dynamically convert the text string returned by your lookup into a valid table or range reference.
When a lookup function like VLOOKUP or XLOOKUP returns a table name, the spreadsheet software treats it as plain text. The INDIRECT function bridges this gap by actively reading the text string and resolving it as a valid range or table reference.
Check your existing data sets by going to the Formulas tab and opening the Name Manager. Ensure your tables or named ranges have correct names without spaces.
In an empty cell, write your lookup function to retrieve the table name. For example: =VLOOKUP(A1, D1:E5, 2, FALSE). Verify that it returns the exact name of your target table.
Edit your formula and wrap the lookup function inside the INDIRECT function. It should look like this: =INDIRECT(VLOOKUP(A1, D1:E5, 2, FALSE)).
Integrate the INDIRECT statement into your broader formula. If you want to sum the dynamic table, write: =SUM(INDIRECT(VLOOKUP(A1, D1:E5, 2, FALSE))) and press Enter.
Easily Manage Dynamic Data with WPS Spreadsheet
WPS Spreadsheet fully supports the INDIRECT function and advanced data lookup capabilities. Build dynamic data models and reference tables seamlessly, enjoying the same powerful formula environment as Excel.
- 1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your spreadsheet file containing the lookup tables and data.
- 2. Define your named ranges: Select your data range, navigate to the Formulas tab, and click 'Define Name' to create easily referable table names.
- 3. Construct the dynamic lookup: Use your preferred lookup function (like VLOOKUP or INDEX/MATCH) to retrieve the table name.
- 4. Complete the formula with INDIRECT: Wrap the lookup formula with the INDIRECT function, e.g., =SUM(INDIRECT(VLOOKUP(...))), and hit Enter to instantly evaluate your dynamic table.
Frequently Asked Questions
Why does my INDIRECT formula return a #REF! error?
A #REF! error usually occurs if the text string returned by the lookup does not exactly match an existing table or named range, or if the referenced name is inside a closed external workbook. INDIRECT requires external workbooks to be open to evaluate references.
Is the INDIRECT function volatile?
Yes, INDIRECT is a volatile function. This means it recalculates every time any change is made to the workbook. Overusing INDIRECT in very large spreadsheets can slow down calculation performance.
Can I use INDIRECT to reference an entire column dynamically?
Yes. If your lookup returns a text string like 'C:C', you can wrap it in INDIRECT to reference the entire column. For instance, =SUM(INDIRECT("C:C")) will sum all values in column C.
Does INDIRECT work with XLOOKUP as well?
Absolutely. Any formula that returns a text string representing a valid reference can be wrapped in INDIRECT. You can use =INDIRECT(XLOOKUP(A1, B1:B5, C1:C5)) to successfully direct the function.
