How to Create an Excel IF Formula with Three Conditions Based on Two Cells
Question details
The user needs an Excel formula to evaluate two different cells and return specific text values based on three distinct date conditions.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking task or invoice statuses where outcomes ('P', 'PD', or 'TC') depend on whether a 'Paid On' date exists in one cell, or if a target date in another cell is past or in the future.
- Observed behavior
- The goal is to automatically return 'P' if paid, 'PD' if past due, and 'TC' if upcoming, ensuring the paid status overrides the other date conditions.
Ensure your target cells are properly formatted as Dates, and clearly identify which column holds your comparison date and which holds the completion date.
Use the IFS Function to Evaluate Multiple Conditions
The IFS function is the most straightforward way to test multiple conditions in order, stopping and returning a value at the first true condition without complex nesting.
The IFS function evaluates conditions sequentially from left to right. By placing the most important condition (whether the item is paid) first, you ensure it overrides the past or future date checks.
Click on the cell where you want the status (P, PD, or TC) to appear.
Type the formula: =IFS(B2<>"","P",A2<TODAY(),"PD",A2>TODAY(),"TC") into the formula bar. Assume B2 is the 'Paid On' date and A2 is the target date.
Press Enter to generate the result, then drag the fill handle down to apply this logical test to other rows.
Use the SWITCH Function with a TRUE Condition
You can use the SWITCH function combined with TRUE() as an alternative method to evaluate multiple distinct logical tests.
Use the LOOKUP Function for Advanced Array Matching
For specific mathematical differences between dates, the LOOKUP function can assign conditional values based on an array.
Easily Manage Complex Formulas with WPS Spreadsheet
WPS Office provides a highly compatible and user-friendly spreadsheet tool that seamlessly handles advanced logical functions like IFS, SWITCH, and complex array lookups out of the box.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new spreadsheet or open your existing Excel file.
- 2. Enter your customized formula: Type your chosen IFS, SWITCH, or IF formula directly into the formula bar.
- 3. Execute and drag: Press Enter to get your result, then double-click the cell's bottom-right corner to auto-fill the formula down the column.
Frequently Asked Questions
What is the difference between IF and IFS in Excel?
The standard IF function only evaluates a single condition and requires complex nesting to handle multiple criteria. The IFS function evaluates multiple conditions sequentially and returns a value for the first TRUE result it finds, making complex formulas much easier to read and troubleshoot.
Why does my IFS formula return an #N/A error?
An #N/A error occurs in an IFS formula if absolutely none of the provided conditions are met. To prevent this, you can add a final catch-all condition at the end of your formula, such as TRUE, "Default Value".
Are formulas like IFS and SWITCH supported in WPS Office?
Yes, WPS Spreadsheet fully supports modern logical functions including IFS and SWITCH. You can seamlessly open and edit spreadsheets containing these formulas created in Microsoft Excel without losing data integrity.
How does the TODAY() function work within these logical tests?
The TODAY() function dynamically returns the current date based on your system clock. By comparing another date to TODAY() (e.g., A2 > TODAY()), the formula automatically calculates if a deadline is in the past or the future without manual updates.
