How to Use Conditional VLOOKUP in WPS Spreadsheets When a Cell Contains Null
Question details
User needs a conditional VLOOKUP formula that retrieves a server name only when the current cell contains "null" and leaves existing values unchanged.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with a spreadsheet containing server names and lookup values, where null cells need to be filled from another sheet.
- Observed behavior
- A formula entered in a cell cannot replace its own value, so a helper column is required to preserve existing values.
Make sure the lookup table is on a separate sheet and note the column index number you need to return. Identify the helper column next to the current server column before entering the formula.
Use a Helper Column with IF and VLOOKUP to Conditionally Fill Null Server Cells
Use this when you need to return a value only for cells showing "null" and keep the current value in all other rows.
A formula cannot overwrite the cell where it is entered, so enter the calculation in an adjacent helper column. The IF function checks the server cell, and VLOOKUP retrieves the server name from the lookup table.
Select the cell next to your first server value, for example D11 if the current server value is in C11.
Type =IF(C11="null",VLOOKUP(B11,other_table,column_index,FALSE),C11) and replace other_table and column_index with your lookup range and return column number.
Press Enter, then drag the fill handle down the helper column to apply the formula to the remaining rows.
Replace the Original Server Column with the Helper Results as Static Values
Use this after the helper column returns the correct values, when you want to clean up the helper column and keep only the final server names.
Fill Null Server Cells with Conditional VLOOKUP in WPS Spreadsheets
Create a helper column with IF and VLOOKUP, then paste the results as values to preserve existing server names and fill only "null" cells.
- 1. Open your workbook: Open your workbook in WPS Spreadsheets.
- 2. Select the helper cell: Click the cell where you want the helper formula, such as D11.
- 3. Enter the formula: Type =IF(C11="null",VLOOKUP(B11,other_table,column_index,FALSE),C11) and press Enter.
- 4. Fill the formula down: Drag the fill handle down the helper column to calculate all rows.
- 5. Replace the original values: Copy the helper results and paste them as values over the original server column.
Frequently Asked Questions
Can a formula replace the value in the same cell?
No. A cell can contain either a value or a formula, not both, so use an adjacent helper column to calculate the new value and then paste it over the original cell as a value.
How do I keep the existing server value when the cell is not null?
Use the IF function with the C11 reference as the value_if_false argument, such as =IF(C11="null",VLOOKUP(...),C11). It returns the current cell value whenever the condition is false.
Does VLOOKUP work across different sheets in WPS Spreadsheets?
Yes. In WPS Spreadsheets, include the sheet name and range in the lookup table argument, for example Sheet2!$A$2:$B$100, and use the correct column index number.
How do I replace the original server column without breaking formulas?
Copy the helper column cells, right-click the original server column, choose Paste Special > Values, and then delete the helper column if it is no longer needed.
What should I do if my cells contain the text 'null' but not exactly lowercase?
Use the TRIM or UPPER function around C11, for example =IF(UPPER(C11)="NULL",VLOOKUP(...),C11), so the formula matches NULL, Null, or null.
