WPS Spreadsheet: Look Up Values from a User-Selected Excel Table
Question details
The user needs to retrieve a matching value from different Excel tables according to the table selected in each row.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Each row contains a selected table name and a quantity or lookup value. Named ranges should identify the relevant table columns so the formula can return the corresponding result, such as a box size.
- Observed behavior
- The lookup must dynamically reference the range named in the selected-table cell instead of using one fixed Excel table.
Create named ranges for the lookup columns and result columns before entering the formula. Confirm that every table uses the same headers, row structure, and compatible range sizes, and that the selected table name exactly matches the relevant named range.
Use named ranges with INDIRECT, INDEX, and SMALL
Use this method when each row selects a named lookup range and you need compatibility with older spreadsheet versions or legacy array formulas.
Assume G6 contains the name of the selected quantity range, H6 contains the quantity to test, and Box_size is the named range containing the values to return. The formula dynamically reads the range named in G6, finds the first row meeting the condition, and returns the corresponding result from Box_size.
In WPS Spreadsheet, select each table column and open the Formulas tab, choose Define Name, and assign a name such as Table_A_Quantity or Table_A_Box_Size. Make sure the names contain no spaces and refer to compatible ranges.
Select the result cell and enter =INDEX(Box_size,SMALL(IF(H6<=INDIRECT(G6),ROW(A1:A7)),1)). Replace Box_size, G6, H6, and A1:A7 with the named result range, selected-range cell, lookup-value cell, and actual row span used in your workbook.
If your WPS Spreadsheet version does not calculate the IF expression as a dynamic array, press Ctrl+Shift+Enter after editing the formula. In versions with dynamic-array support, press Enter and allow the formula to calculate normally.
Drag the fill handle from the completed result cell down the column. Keep the selected table name and lookup value references relative so each row uses its own selection.
Build Dynamic Named-Range Lookups in WPS Spreadsheet
WPS Spreadsheet can dynamically retrieve values from a user-selected table by combining defined names with INDIRECT, INDEX, and SMALL. Set up consistent named ranges, enter the lookup formula, and fill it down to calculate a result for every row.
- 1. Define the table ranges: Open the Formulas tab, select Define Name, and create named ranges for each table's lookup and result columns.
- 2. Reference the selected range: In the result cell, enter an INDEX, SMALL, and INDIRECT formula that uses the selected table name stored in the row.
- 3. Match the lookup condition: Set the formula to compare the row's quantity or lookup value with the dynamically referenced range.
- 4. Calculate and copy the result: Press Enter or Ctrl+Shift+Enter when required by your version, then drag the fill handle down the result column.
Frequently Asked Questions
How should the selected table name be stored for INDIRECT?
Place the exact defined range name in a cell such as G6. The text must match the name created through Formulas > Define Name or Formulas > Name Manager, including underscores and capitalization-insensitive spelling.
Why does the INDIRECT lookup return an error?
Check that the selected name exists, contains no extra spaces, and refers to a valid range. Also verify that the named range is available in the current workbook and that its size matches the result range.
Do all selected tables need the same structure?
Yes. The tables should use identical headers and compatible row counts or aligned ranges. Otherwise, INDEX may return the wrong row or produce a reference error.
Does WPS Spreadsheet require Ctrl+Shift+Enter for this formula?
Some older WPS Spreadsheet versions require Ctrl+Shift+Enter for the IF array calculation. In versions with dynamic-array support, entering the formula with Enter is generally sufficient.
Can I replace the legacy formula with XLOOKUP or FILTER?
If your WPS Spreadsheet version supports them, XLOOKUP, FILTER, or LET can make the formula easier to read. The same named-range and INDIRECT setup still applies, and the selected ranges must remain compatible.
