How to Create a Conditional Formula Based on Text in Another Sheet in WPS Office Spreadsheets
Question details
The user needs a formula in WPS Office Spreadsheets that returns a value from Sheet2 only when a specified text value appears in another cell, and returns a blank otherwise.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Pulling data from another sheet based on a text condition in a referenced cell.
- Observed behavior
- The formula should conditionally display the corresponding Sheet2 value or remain blank when the text condition is not met.
Confirm that the source sheet and the target cell exist, and note the exact cell references you want to compare and return.
Create an IF Formula That Returns a Value from Another Sheet When Text Matches
Use this method when you need a simple, non-case-sensitive text match across sheets and want a blank result when the condition is false.
This formula checks whether Sheet2!A1 equals the specified text. If it does, WPS Office Spreadsheets returns the value from Sheet2!B1; if it does not, the formula returns an empty string, leaving the cell visually blank.
Click the cell where you want the conditional result to appear, such as Sheet1!B2.
Type =IF(Sheet2!A1="some text",Sheet2!B1,"") into the formula bar and replace some text with the exact text condition you need.
Press Enter to calculate the formula. The cell now shows the corresponding Sheet2 value when the condition is met, otherwise it remains blank.
Create a Case-Sensitive Conditional Formula with EXACT
Use this method when the text comparison must be case-sensitive and an exact match is required.
Use WPS Office Spreadsheets to Build Cross-Sheet Conditional Formulas
Create IF and EXACT formulas in WPS Office Spreadsheets to return values from another sheet only when your text condition is met, with an empty-cell result when the condition fails.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office and open the workbook containing Sheet1 and Sheet2.
- 2. Select the target cell: Click the cell where you want the returned value to appear, such as Sheet1!B2.
- 3. Enter a cross-sheet IF formula: Type =IF(Sheet2!A1="some text",Sheet2!B1,"") in the formula bar, replacing the text and references with your own.
- 4. Press Enter and review the result: Press Enter to calculate the formula and verify that the correct value appears only when the specified text condition is true.
Frequently Asked Questions
How do I return a blank instead of 0 when the text does not match?
Use an empty string as the IF false value, for example =IF(Sheet2!A1="some text",Sheet2!B1,""). This returns a blank-looking cell instead of 0 when the condition is false.
Can I compare text in a cell rather than typing the text directly in the formula?
Yes. Replace "some text" with a cell reference, such as =IF(Sheet2!A1=Sheet1!A1,Sheet2!B1,""). The formula then compares the value in Sheet2!A1 with the value in Sheet1!A1.
How can I make the text comparison case-sensitive?
Wrap the comparison in EXACT, for example =IF(EXACT(Sheet2!A1,"some text"),Sheet2!B1,""). EXACT returns TRUE only when both text values match exactly, including uppercase and lowercase letters.
Will this formula work if Sheet2 is in another workbook?
Cross-sheet formulas work within the same workbook. If the source is in another workbook, ensure that workbook is open and use the full workbook and sheet reference, such as =IF([Book2.xlsx]Sheet2!A1="some text",[Book2.xlsx]Sheet2!B1,"").
