How to Use a Dynamic Table Reference in VLOOKUP and MATCH
Question details
The user needs to make both the lookup array in VLOOKUP and the header range in MATCH dynamically update based on a table name selected from a drop-down list.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Building a flexible data dashboard or lookup template where selecting a different table name automatically updates the data retrieval range.
- Observed behavior
- The VLOOKUP formula successfully uses a dynamic table name, but the nested MATCH function remains restricted to a fixed, hardcoded table header range.
Ensure all your data tables have identical dimensions if you plan to use fixed height and width parameters, and verify that the drop-down list text perfectly matches your defined named ranges.
Use INDIRECT and OFFSET to Build Dynamic Ranges
Combine the INDIRECT function to read the table name from your drop-down and the OFFSET function to specify the exact dimensions for both the data array and the header row.
By nesting INDIRECT inside OFFSET, you can force Excel or WPS Spreadsheet to construct a proper reference array on the fly. You can define the height and width manually within the OFFSET formula so both VLOOKUP and MATCH look at the correct boundaries.
Locate the cell containing your drop-down list with the table name. For this example, assume it is cell $B$1, and the lookup value is in cell $B$2.
Write the OFFSET formula to define a 1-row high array for headers: OFFSET(INDIRECT($B$1),0,0,1,3). The '1' specifies a single row, and '3' specifies the number of columns in your table.
Define the full data table range: OFFSET(INDIRECT($B$1),0,0,22,3). The '22' represents the total rows in the table, and '3' represents the total columns.
Assemble the complete nested formula: =VLOOKUP(A4,OFFSET(INDIRECT($B$1),0,0,22,3),MATCH($B$2,OFFSET(INDIRECT($B$1),0,0,1,3),0),0). Press Enter to apply.
Use a Helper Cell for the Column Index
Calculate the column number dynamically in a separate cell to keep your primary VLOOKUP formula simple and easier to troubleshoot.
Build Dynamic Dashboards with WPS Spreadsheet
WPS Spreadsheet provides a robust formula engine that handles complex, nested functions like VLOOKUP, MATCH, INDIRECT, and OFFSET effortlessly, allowing you to build highly dynamic and responsive templates.
- 1. Open WPS Spreadsheet: Launch WPS Office, create a new spreadsheet, and input your raw data tables on separate sheets.
- 2. Create a Drop-Down Menu: Navigate to the Data tab, click Data Validation, and set the criteria to 'List' to create a selection of your table names.
- 3. Apply Nested Formulas: Select the target cell and type your combined =VLOOKUP(..., OFFSET(INDIRECT(...))) formula. WPS Spreadsheet will calculate the dynamic references instantly.
Frequently Asked Questions
Why does my INDIRECT formula return a #REF! error?
A #REF! error usually occurs if the text in the referenced cell contains spaces or invalid characters that do not perfectly match the named range, or if you are trying to use INDIRECT on a closed external workbook.
Can I use INDEX and MATCH instead of VLOOKUP for dynamic tables?
Yes. INDEX and MATCH offer even more flexibility than VLOOKUP. You can wrap the array arguments of both the INDEX and MATCH functions with INDIRECT to achieve the same dynamic table selection.
How do the height and width arguments work in the OFFSET function?
In the OFFSET function, the height determines how many rows the resulting range covers, and the width determines the columns. Setting height to 1 and width to the number of columns isolates just the header row for your MATCH function.
