How to Return Codes from Multiple Excel Criteria and Tables in WPS Office
Question details
The user needs a lookup structure that returns the corresponding discipline, group, and sheet type abbreviation codes from dropdown selections and combines them into a file name.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user selects a discipline, group, and sheet type from dropdowns and needs the matching abbreviation codes to build a file name.
- Observed behavior
- The worksheet currently has dropdown selections but lacks a working lookup structure to return the matching codes automatically.
Before creating formulas or slicers, confirm that the dropdown values exactly match the lookup table entries—including spelling, spaces, and capitalization—and that the lookup data is arranged in one flat, normalized table.
Create a Normalized Lookup Table and Return Codes with XLOOKUP
Use this method when you want a reusable, formula-driven lookup that returns all three codes from the selected dropdown values.
A flat lookup table is easier to maintain than multiple separate tables. Repeat the parent values for each valid combination so a single match returns the correct discipline, group, and sheet type code.
Open your WPS Spreadsheets workbook and enter these column headers in a blank sheet: Discipline, Discipline Code, Group, Group Code, Sheet Type, Sheet Type Code. Add one row for each valid combination and repeat parent values where necessary.
Select the cell where the discipline code should appear, enter =XLOOKUP($B$2,$A$2:$A$100,$B$2:$B$100,"Not found"), and press Enter. Replace $B$2 with the dropdown cell for Discipline and adjust the ranges to match your table.
Use the same pattern for group and sheet type: =XLOOKUP($B$3,$C$2:$C$100,$D$2:$D$100,"Not found") and =XLOOKUP($B$4,$E$2:$E$100,$F$2:$F$100,"Not found").
In the file-name cell, enter =TEXTJOIN("_",TRUE,D2,D3,D4) or =D2&"_"&D3&"_"&D4 to join the returned codes with underscores.

Filter the Normalized Lookup Table with Slicers
Use this method when you want a visual, clickable way to see the matching row instead of using lookup formulas.
Build a Multi-Criteria Code Lookup in WPS Spreadsheets
Use WPS Spreadsheets to normalize your lookup data, return the correct discipline, group, and sheet type codes with XLOOKUP or FILTER, and combine them into a file name without switching to Microsoft Excel.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the .xlsx workbook that contains the dropdown selections and lookup data.
- 2. Build the normalized lookup table: Create columns for Discipline, Discipline Code, Group, Group Code, Sheet Type, and Sheet Type Code, then fill one row per valid combination.
- 3. Return each code with a lookup formula: Use XLOOKUP or FILTER to pull the discipline code, group code, and sheet type code from the lookup table based on the selected dropdown cells.
- 4. Combine codes into the final file name: Use =TEXTJOIN("_",TRUE,D2,D3,D4) or =D2&"_"&D3&"_"&D4 to assemble the returned codes into one file name.

Frequently Asked Questions
Can I use VLOOKUP instead of XLOOKUP for multiple criteria in WPS Spreadsheets?
Yes, but VLOOKUP looks up only one value. For multiple criteria you usually need a helper column or a normalized table with one row per valid combination. XLOOKUP or FILTER is the more direct approach.
Why does my XLOOKUP code return #N/A when the dropdown matches?
Check for extra spaces, different text case, or dropdown values that do not exactly equal the lookup column values. Use TRIM or create the dropdown validation directly from the lookup table values.
How do I combine three returned codes into one file name?
Use =TEXTJOIN("_",TRUE,D2,D3,D4) or =D2&"_"&D3&"_"&D4 after the returned code cells to join them with underscores.
Can slicers update the returned codes automatically?
Slicers filter the table visually, but they do not generate a formula result. Use XLOOKUP or FILTER formulas if you need automatic code return into a file-name cell.
Does WPS Office support the FILTER function from Excel?
Current versions of WPS Spreadsheets support XLOOKUP and FILTER. If your version does not, use INDEX/MATCH or a helper-column lookup as a fallback.




