How to Use IF or IFS to Check Two Conditions in Excel
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.

- 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.
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.
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.
Click on the cell where you want the employee information to be displayed.
Type the formula: =IF(OR(K2="SCHOOL NAME", AL2="SCHOOL CODE"), E2, ""). Replace "SCHOOL NAME" and "SCHOOL CODE" with your actual search criteria.
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.
Drag the fill handle at the bottom-right corner of the cell down to apply this logic to the rest of the column.

Combine IF, OR, and XLOOKUP for Cross-Referenced Data
Use this solution when the matching employee information needs to be pulled from a different table or range rather than the same row.
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. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the employee and school data.
- 2. Select your output cell: Click on the cell where the evaluated employee information should be generated.
- 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. 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.

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.




