Use INDEX and MATCH with One of Two Excel Tables in WPS Spreadsheets
Question details
The user needs a formula that uses a drop-down selection to look up a value in either the MAIN_EQUIPMENT table or the INSTRUMENT_FIELD table.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A drop-down cell determines whether the workbook should look up a value in MAIN_EQUIPMENT or INSTRUMENT_FIELD.
- Observed behavior
- The existing IFERROR/IF/INDEX/MATCH formula works but is long and messy; the goal is to use a dependable table-switching lookup.
Confirm that the named ranges MAIN_EQUIPMENT and INSTRUMENT_FIELD exist in the workbook, and that the drop-down cell contains exactly the table names used in the formula.
Use IFERROR, IF, INDEX, and MATCH to Switch Between Two Excel Tables
Use this formula when a drop-down selection should choose between two lookup tables and return a blank instead of an error when no match is found.
This formula checks the value in D3. If D3 equals MAIN_EQUIPMENT, INDEX and MATCH search the MAIN_EQUIPMENT named range; otherwise they search INSTRUMENT_FIELD. IFERROR returns an empty string when the lookup value is not found.
Click the cell where you want the lookup result to appear, for example E3.
Type =IFERROR(IF(D3="MAIN_EQUIPMENT",INDEX('System & FL Codes'!$B$3:$B$112,MATCH(H3,MAIN_EQUIPMENT,0)),INDEX('System & FL Codes'!$F$6:$F$466,MATCH(H3,INSTRUMENT_FIELD,0))),"") and press Enter.
Change the drop-down in D3 between MAIN_EQUIPMENT and INSTRUMENT_FIELD and confirm that the formula returns the matching value or a blank when no match exists.
Look Up Values Across Two Excel Tables in WPS Spreadsheets
Use WPS Spreadsheets to complete this lookup directly with the same IFERROR, IF, INDEX, and MATCH functions. The formula reads your existing Excel named ranges and returns results without rewriting the workbook.
- 1. Open your workbook: Start WPS Spreadsheets and open the workbook that contains MAIN_EQUIPMENT, INSTRUMENT_FIELD, and the drop-down cell.
- 2. Verify named ranges: Click Formulas > Name Manager and confirm that MAIN_EQUIPMENT and INSTRUMENT_FIELD are listed.
- 3. Enter the formula: Select the result cell and type =IFERROR(IF(D3="MAIN_EQUIPMENT",INDEX('System & FL Codes'!$B$3:$B$112,MATCH(H3,MAIN_EQUIPMENT,0)),INDEX('System & FL Codes'!$F$6:$F$466,MATCH(H3,INSTRUMENT_FIELD,0))),"") and press Enter.
- 4. Test the drop-down: Change the drop-down cell between MAIN_EQUIPMENT and INSTRUMENT_FIELD to verify that the formula returns the corresponding value or a blank.
Frequently Asked Questions
Can I use this formula in WPS Spreadsheets if my workbook was created in Excel?
Yes. WPS Spreadsheets supports IFERROR, IF, INDEX, and MATCH, and it opens .xlsx workbooks with named ranges, so the formula works without changes.
Why does the formula return blank instead of #N/A?
The outer IFERROR returns an empty string when no match is found, which hides the #N/A error and keeps the worksheet clean.
What should I do if the drop-down value has an extra space?
Wrap the drop-down cell in TRIM, for example IF(TRIM(D3)="MAIN_EQUIPMENT",...), so the text comparison is not affected by leading or trailing spaces.
Can I add a third table to this formula?
Yes. You can nest additional IF conditions or use IFS if your version of WPS supports it, with a separate INDEX/MATCH block for each table choice.
Do MAIN_EQUIPMENT and INSTRUMENT_FIELD need to be named ranges?
They can be named ranges or direct cell references, but named ranges make the formula easier to read and maintain.
