How to Use a Formula Instead of Conditional Formatting to Display Values in WPS Spreadsheet
Question details
The user wants cells in a table grid to display matching RF unit port names instead of only changing colors through conditional formatting.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A table or grid must show the port name that corresponds to each numeric RF unit port value.
- Observed behavior
- Conditional formatting can change colors, fonts, and styles, but it cannot normally replace the displayed value in a cell.
Before you write the formula, make sure the lookup table uses one row per numeric value. If your port data uses text ranges such as “4-5”, split them into separate minimum and maximum columns or individual entries so XLOOKUP can find a numeric match.
Use a LET and XLOOKUP formula to display the matching value in each grid cell
Use this when the grid values are predictable and can be calculated from the current row and column, because the formula returns the matched port label directly.
Conditional formatting cannot replace cell values because it only applies appearance rules. A lookup formula calculates the number at each grid position and returns the matching port label, so the values update automatically when the lookup table changes.
Open the worksheet in WPS Spreadsheets and click the first cell where the displayed value should change. In the example formula, this is the top-left cell of the output grid.
Type =LET(lookup_value,COLUMN()-5+(ROW()-8)*8,XLOOKUP(lookup_value,$B$2:$B$100,$A$2:$A$100,lookup_value,0,1)) and press Enter. Adjust -5 and -8 to match the starting row and column of your grid.
Drag the fill handle across the rows and columns, or copy and paste the formula into the remaining output cells. Each cell calculates its own lookup value based on ROW and COLUMN.
Review cells that should display port names. If a cell still shows the numeric lookup value, that number was not found in column B, so inspect the lookup table for missing entries or text-formatted values.
Normalize range-based lookup values before using the formula
Use this when the lookup table stores port ranges such as “4-5”, because numeric grid values will not match that text directly.
Display Lookup Values in WPS Spreadsheets Without Conditional Formatting
Use WPS Spreadsheets formulas such as LET and XLOOKUP to calculate a lookup value from each cell position and return the matching port name directly. This keeps your workbook lightweight, formula-driven, and compatible with Excel .xlsx files.
- 1. Download and open WPS Office: Download WPS Office from the official WPS website, install it, and open WPS Spreadsheets.
- 2. Open your workbook: Open the existing Excel workbook or create a new spreadsheet. WPS Spreadsheets opens .xlsx files in a familiar tabbed interface.
- 3. Enter the lookup formula: In the first output grid cell, enter the LET and XLOOKUP formula that calculates the lookup value from ROW and COLUMN and returns the matching port name.
- 4. Fill the grid and save: Drag the fill handle across the output grid, check the results, and save the file as .xlsx to maintain compatibility with Microsoft Excel.
Frequently Asked Questions
Can conditional formatting change the displayed value in WPS Spreadsheets?
No. Conditional formatting only changes a cell’s font, fill, border, or number format. To show a different value based on a lookup, enter a formula such as XLOOKUP or use VBA for interactive entry.
What formula can display matching RF unit ports when the grid has predictable numbers?
Use a LET and XLOOKUP formula that calculates the lookup value from the cell’s ROW and COLUMN, for example =LET(lookup_value,COLUMN()-5+(ROW()-8)*8,XLOOKUP(lookup_value,$B$2:$B$100,$A$2:$A$100,lookup_value,0,1)). Adjust the offsets to match your grid layout.
Why does XLOOKUP return the original number instead of the port name?
XLOOKUP returns the not-found value when the lookup number does not match the lookup range. Check that the lookup number exists in column B and that the source values are numeric, not text.
How do I handle lookup values stored as ranges like 4-5?
Split the range into separate minimum and maximum numeric columns or expand each range into individual numeric entries. Otherwise a numeric grid value cannot match a text range such as “4-5”.
Does WPS Office support the LET and XLOOKUP functions?
Yes. Current versions of WPS Spreadsheets support LET and XLOOKUP, and you can save files as .xlsx to maintain compatibility with Microsoft Excel.
