Function Problems

How to Return Codes from Multiple Excel Criteria and Tables in WPS Office

Muhammad TalhaMuhammad Talha Nov 13, 2026 868 views

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.

How to Return Codes from Multiple Excel Criteria and Tables in WPS Office
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 you start

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.

Solution 1Recommended

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.

1
Create the normalized lookup table

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.

2
Return the discipline code

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.

3
Return group and sheet type codes

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").

4
Combine codes into a file name

In the file-name cell, enter =TEXTJOIN("_",TRUE,D2,D3,D4) or =D2&"_"&D3&"_"&D4 to join the returned codes with underscores.

Create a Normalized Lookup Table and Return Codes with XLOOKUP
Match dropdown values exactly: If a returned code shows #N/A, check for extra spaces or capitalization differences between the dropdown values and the lookup table.
WPS Spreadsheets lookup workflow

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. 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the .xlsx workbook that contains the dropdown selections and lookup data.
  2. 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. 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. 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.
Returns multiple criteria codes using one normalized lookup tableSupports .xlsx workbooks and familiar Excel lookup formulasLets you combine returned codes into a file name with TEXTJOIN or CONCATFree, lightweight interface with no Microsoft 365 subscription required
microsoft office alternative - wps office

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.