How to Return the First Nonblank Result from Four Excel Cells in WPS Office
Question details
The user needs a WPS Office Spreadsheet formula that evaluates four formula-driven cells in a specified order and returns the first meaningful value while ignoring blanks, empty strings, or the text "Null".
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user has four cells that contain formula results such as text values, empty strings, or "Null" values and wants to prioritize them in a particular order.
- Observed behavior
- A simple formula returns an empty or "Null" result instead of the first meaningful cell value.
Confirm whether the four cells contain true blanks, empty strings returned by formulas, or the literal text "Null", because the formula must match the actual returned value. Also verify the priority order of the four cells before entering the nested formula.
Use a nested IF formula to return the first meaningful value in priority order
Use this method when your four formula-driven cells may contain empty strings or the text "Null" and you need a straightforward, widely compatible formula in WPS Office Spreadsheet.
The nested IF formula checks each cell in the order you place it. The example below checks AL4 first, then AN4, then AM4, then AO4; replace these references with your own priority order.
If your cells only contain the literal text "Null" and not empty strings, you can simplify the test to AL4<>"Null".
Click the cell where you want the first meaningful result to appear, such as AP4, then click the formula bar.
Type or paste =IF(AND(AL4<>"",AL4<>"Null"),AL4,IF(AND(AN4<>"",AN4<>"Null"),AN4,IF(AND(AM4<>"",AM4<>"Null"),AM4,IF(AND(AO4<>"",AO4<>"Null"),AO4,"")))) and press Enter.
Replace AL4, AN4, AM4, and AO4 with your four cells in the required priority order, then test the formula by changing the source cells between values, empty text, and "Null".
Return the first nonblank value with FILTER, INDEX, and IFERROR
Use this dynamic array method when your WPS Office Spreadsheet version supports the FILTER function and you want a shorter formula for a contiguous or ordered range.
Return the First Nonblank Value in WPS Office Spreadsheet
Create a priority formula in WPS Office Spreadsheet that checks four cells in order and returns the first nonblank, non-Null result while keeping your Excel workbook fully compatible.
- 1. Open your workbook in WPS Office Spreadsheet: Launch WPS Office, click Menu > Open, and select the Excel workbook that contains the four formula-driven cells.
- 2. Select the result cell: Click the cell where you want the first nonblank result to appear, then click the formula bar.
- 3. Enter a priority formula: Type =IF(AND(AL4<>"",AL4<>"Null"),AL4,IF(AND(AN4<>"",AN4<>"Null"),AN4,IF(AND(AM4<>"",AM4<>"Null"),AM4,IF(AND(AO4<>"",AO4<>"Null"),AO4,"")))) and press Enter, adjusting the cell order to your priority.
- 4. Verify and save: Check the returned value against your four cells, then use Ctrl+S or Menu > Save to keep the workbook in Excel-compatible .xlsx format.
Frequently Asked Questions
What is the difference between a blank cell, an empty string, and the text "Null" in Excel?
A true blank contains no value, an empty string is returned by a formula as "", and "Null" is literal text. Use AND(AL4<>"",AL4<>"Null") to skip both empty strings and the text "Null", or ISBLANK for true blanks.
Can I use these formulas in both WPS Office Spreadsheet and Microsoft Excel?
Yes. Nested IF, IFERROR, INDEX, and FILTER formulas are supported in WPS Office Spreadsheet, and .xlsx files remain compatible when you move between WPS Office and Microsoft Excel.
How do I make the formula return a true blank instead of an empty string?
Excel formulas cannot create a true blank cell; the final "" in the formula returns an empty string. This empty string is treated as blank by many functions and is the standard way to display no visible result.
What should I check if the formula returns the wrong cell?
Check the order of the nested IF functions, because the first cell reference in the formula is evaluated first. Also use TRIM to remove hidden leading or trailing spaces that can make two text values look different but compare differently.
Does WPS Spreadsheet support the FILTER dynamic array function?
Current versions of WPS Office Spreadsheet support FILTER and other dynamic array functions. If FILTER is unavailable, use the nested IF formula instead or update WPS Office to the latest version.
