WPS Spreadsheet: Look Up an Alias from a Two-Column Excel Table
Question details
The user needs to match a value against one column of a two-column table and return the corresponding alias from the other column.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Values to match are in column E, lookup values are in column B, and aliases are stored in column A. The result should be populated in column F.
- Observed behavior
- The alias needs to be returned for each value in column E, with a fallback message when no matching lookup value exists.
Confirm that the lookup values in column B and the aliases in column A are aligned row by row. Check the first data row and adjust the formula ranges to cover the complete table before filling the formula down.
Use XLOOKUP to return the matching alias
Use this method in current WPS Spreadsheet versions for a clear lookup formula with a custom not-found result.
Open the worksheet and select cell F2, or the first cell where the alias should appear.
In the formula bar, enter =XLOOKUP(E2,$B$2:$B$4,$A$2:$A$4,"Not found") and press Enter. The formula searches for E2 in column B and returns the corresponding value from column A.
Select F2 and drag the fill handle down, or copy F2 and paste it into the remaining cells in column F. The relative reference E2 updates for each row, while the lookup ranges remain fixed.
Use INDEX and MATCH for older spreadsheet versions
Use this compatible alternative when XLOOKUP is unavailable or when you prefer a traditional lookup formula.
Find Matching Aliases with XLOOKUP or INDEX/MATCH in WPS Spreadsheet
WPS Spreadsheet lets you build the alias lookup directly with XLOOKUP or INDEX/MATCH, copy the formula down, and work with familiar Excel-compatible worksheets.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the two-column lookup table.
- 2. Select the output cell: Click F2, or the first cell in the output column where the alias should be displayed.
- 3. Enter the lookup formula: Type =XLOOKUP(E2,$B$2:$B$4,$A$2:$A$4,"Not found") in the formula bar and press Enter.
- 4. Fill results down: Copy F2 or drag its fill handle down the output column to populate an alias for each value in column E.
Frequently Asked Questions
Which cell should contain the alias lookup formula?
Enter the formula in F2 if the value to match starts in E2 and the output belongs in column F. Change the starting cell if your worksheet uses different columns or rows.
How do I change the lookup table range?
Replace $B$2:$B$4 with the full range containing the lookup values and replace $A$2:$A$4 with the corresponding alias range. Both ranges must contain the same number of rows.
What should I use if XLOOKUP is not available?
Use =IFERROR(INDEX($A$2:$A$4,MATCH(E2,$B$2:$B$4,0)),"Not found"). This combines INDEX and exact-match MATCH to return the alias.
Why does the formula show Not found even when the value appears in the table?
The values may contain extra spaces, different capitalization, or different data types such as text versus numbers. Compare the source values and clean or standardize them before looking them up.
