Combine Excel IF and AND Conditions in One Formula
Question details
Combine text searches and a numeric condition in one formula that returns Review when the specified conditions are met.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Check text in K4 for Spare, Salvage, or replace, and use the value in I4 to determine whether a matching row needs review.
- Observed behavior
- The formula should return Review when K4 contains Spare or Salvage, or when K4 contains replace and I4 is less than or equal to zero. Otherwise, it should return a blank.
Check that K4 contains the text to search and I4 contains the numeric value to compare. Choose an empty cell for the formula and adjust the references if your data is in different columns.
Combine IF, OR, AND, and SEARCH in Excel
Use one formula to check the text and numeric conditions and return Review when a match qualifies.
SEARCH finds text without matching case. ISNUMBER converts a successful search into TRUE and prevents a missing search term from producing an error.
OR accepts any qualifying condition, while AND requires both replace in K4 and a value of zero or less in I4.
In Excel, click an empty cell in row 4 where you want the result to appear.
Type =IF(OR(ISNUMBER(SEARCH("Spare",K4)),ISNUMBER(SEARCH("Salvage",K4)),AND(ISNUMBER(SEARCH("replace",K4)),I4<=0)),"Review","") and press Enter.
Drag the fill handle from the formula cell down the column to check additional rows.

Check the Same Conditions in WPS Spreadsheets
Use the same IF, OR, AND, ISNUMBER, and SEARCH formula in WPS Spreadsheets to flag rows that need review.
- 1. Open the workbook: Open WPS Spreadsheets and select Open to load your Excel workbook.
- 2. Select an output cell: Click an empty cell in row 4, alongside the values in I4 and K4.
- 3. Enter the formula: Enter =IF(OR(ISNUMBER(SEARCH("Spare",K4)),ISNUMBER(SEARCH("Salvage",K4)),AND(ISNUMBER(SEARCH("replace",K4)),I4<=0)),"Review","") and press Enter.
- 4. Fill and save: Drag the cell's fill handle down to check other rows, then select File > Save to save the workbook.

Frequently Asked Questions
Does the formula distinguish between uppercase and lowercase text?
No. SEARCH is not case-sensitive, so it matches Spare, spare, and SPARE. Use FIND instead of SEARCH if you need case-sensitive matching.
Why does the formula use ISNUMBER around SEARCH?
SEARCH returns a numeric position when it finds a term and an error when it does not. ISNUMBER turns those results into TRUE or FALSE so a missing term does not cause the formula to return an error.
Does I4 need to be zero or less when K4 contains Spare?
No. The I4 condition applies only to the replace search. Spare or Salvage in K4 returns Review regardless of the value in I4.
How can I return a different message when no condition is met?
Replace the final empty text value in the IF formula, "", with a message such as "No review".




