Formula Errors

How to Use IF or IFS to Check Two Conditions in Excel

Emma BrownEmma Brown Nov 10, 2026 870 views

Question details

The user needs an Excel formula to retrieve employee information if either a school name or a school code matches specific criteria, but is facing issues with IFS and incorrectly sized XLOOKUP ranges.

How to Use IF or IFS to Check Two Conditions in Excel
Product
Excel
Device & OS
not provided
Scenario
Retrieving data conditionally based on an "either/or" logic scenario involving two separate columns (e.g., matching a school name in column K or a school code in column AL).
Observed behavior
The current IFS and XLOOKUP combination is returning an error because the lookup array and return array in the XLOOKUP function do not have the same number of rows.
Before you start

Ensure your dataset does not have hidden spaces in the school name or school code columns, and verify that any ranges referenced in lookup functions span the exact same number of rows.

Solution 1Recommended

Use IF combined with OR for Same-Row Data Retrieval

This is the most efficient solution when the employee information you want to return is located on the exact same row as your conditions.

When evaluating two conditions where only one needs to be true (either the school name OR the school code matches), wrapping the OR function inside an IF function is much simpler than using IFS. The IFS function evaluates sequentially and requires pairs of tests and values, which can overcomplicate a simple "either/or" scenario.

1
Select the target cell

Click on the cell where you want the employee information to be displayed.

2
Enter the IF and OR formula

Type the formula: =IF(OR(K2="SCHOOL NAME", AL2="SCHOOL CODE"), E2, ""). Replace "SCHOOL NAME" and "SCHOOL CODE" with your actual search criteria.

3
Apply the formula

Press Enter to execute the formula. If either K2 or AL2 matches your criteria, the value in E2 is returned; otherwise, it leaves the cell blank.

4
Fill the remaining rows

Drag the fill handle at the bottom-right corner of the cell down to apply this logic to the rest of the column.

Use IF combined with OR for Same-Row Data Retrieval
Tip: Using IF(OR(...)) is easier to maintain and read than repeating the output value multiple times within an IFS statement.
Work Smarter with WPS Spreadsheet

Easily Manage Advanced Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical and lookup functions like IF, IFS, OR, and XLOOKUP. You can seamlessly open your existing spreadsheets, quickly identify formula array mismatches with built-in error checking, and safely retrieve complex data.

  1. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the employee and school data.
  2. 2. Select your output cell: Click on the cell where the evaluated employee information should be generated.
  3. 3. Insert the combined formula: Type the standard Excel formula exactly as needed, such as =IF(OR(K2="Name",AL2="Code"), E2, ""), and hit Enter.
  4. 4. Check for array errors: If you are using XLOOKUP and the ranges differ in size, WPS Spreadsheet will flag the cell so you can quickly adjust the lookup and return columns to match.
Fully compatible with Microsoft Excel formulas (.xlsx and .xls)Built-in error checking highlights mismatched array sizes instantlySupports modern functions like XLOOKUP, IFS, and dynamic arraysLightweight, fast to load, and completely free to use
microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP formula return a #VALUE! error?

This usually occurs when the lookup array and the return array do not have the same number of rows. For example, if your lookup array is A2:A50 but your return array is B2:B40, XLOOKUP cannot match the items. Ensure both ranges are exactly the same size.

How does the IFS function evaluate multiple conditions?

The IFS function evaluates its conditions from left to right. As soon as it finds the first condition that evaluates to TRUE, it returns the corresponding value and stops checking any subsequent conditions.

Can I use IFS instead of IF and OR to check two conditions?

Yes, but for an 'either/or' scenario where both conditions result in the exact same output, IF combined with OR is more efficient. If you use IFS, you would have to write out the return value twice: =IFS(K2="NAME", E2, AL2="CODE", E2).

Is XLOOKUP available in all versions of spreadsheet software?

XLOOKUP is a modern function available in Microsoft 365, Excel 2021, and recent versions of WPS Spreadsheet. If you are sharing the file with users on older software (like Excel 2016), it is safer to use INDEX and MATCH instead.