The IFS function in Excel is a logical function that allows you to test multiple conditions and return a value based on the first condition that is true. The syntax of the IFS function is:
=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
The IFS function can take up to 127 pairs of logical tests and values. If none of the logical tests are true, the function returns the #N/A error.
Procedures
To use the IFS function to compare two cells in Excel formula, you need to follow these steps:
- Select the cell where you want to enter the formula.
- Type
=IFS(and then specify the logical tests and values you want to compare. For example, if you want to compare the values in cells A1 and B1, and return “Equal” if they are equal, “Greater” if A1 is greater than B1, and “Lesser” if A1 is less than B1, you can use this formula:
=IFS(A1=B1, "Equal", A1>B1, "Greater", A1<B1, "Lesser")
- Press Enter to complete the formula and see the result in the selected cell.
- Copy the formula to other cells if needed.
Explanation
The IFS function is useful when you want to compare two or more values and return different results based on the comparison. For example, you can use the IFS function to assign grades based on scores, to categorize data based on ranges, or to perform calculations based on conditions.
The IFS function works by evaluating each logical test in the order they are entered, and returning the corresponding value if the test is true. If the test is false, the function moves on to the next pair of arguments, until it finds a true test or runs out of arguments. If all the tests are false, the function returns the #N/A error.
The IFS function can handle up to 127 pairs of logical tests and values, but you should use as few as possible to avoid complexity and errors. You should also make sure that the logical tests are mutually exclusive, meaning that only one of them can be true at a time. Otherwise, the function will return the first true value it finds, and ignore the rest.
Example
Suppose you have a table of sales data for different products and regions, and you want to use the IFS function to compare the sales of each product in each region, and return a rating based on the comparison. The rating criteria are:
- If the sales are equal to or above the average sales of the product, return “Good”.
- If the sales are below the average sales of the product, but above the minimum sales of the product, return “Average”.
- If the sales are below the minimum sales of the product, return “Poor”.
The table looks like this:
| Product | Region | Sales | Average Sales | Minimum Sales |
|---|---|---|---|---|
| A | X | 100 | 90 | 50 |
| A | Y | 80 | 90 | 50 |
| A | Z | 60 | 90 | 50 |
| B | X | 120 | 100 | 70 |
| B | Y | 90 | 100 | 70 |
| B | Z | 80 | 100 | 70 |
| C | X | 150 | 130 | 100 |
| C | Y | 140 | 130 | 100 |
| C | Z | 110 | 130 | 100 |
To use the IFS function to compare the sales of each product in each region, and return a rating, you can use this formula in cell F2:
=IFS(C2>=D2, "Good", C2>=E2, "Average", C2<E2, "Poor")
This formula compares the sales in cell C2 with the average sales in cell D2, and the minimum sales in cell E2, and returns a rating based on the criteria. You can copy the formula to the other cells in column F to get the ratings for all the products and regions.
The result looks like this:
| Product | Region | Sales | Average Sales | Minimum Sales | Rating |
|---|---|---|---|---|---|
| A | X | 100 | 90 | 50 | Good |
| A | Y | 80 | 90 | 50 | Average |
| A | Z | 60 | 90 | 50 | Poor |
| B | X | 120 | 100 | 70 | Good |
| B | Y | 90 | 100 | 70 | Average |
| B | Z | 80 | 100 | 70 | Average |
| C | X | 150 | 130 | 100 | Good |
| C | Y | 140 | 130 | 100 | Good |
| C | Z | 110 | 130 | 100 | Average |
Other approaches
There are other ways to compare two cells in Excel formula, such as using the IF function, the CHOOSE function, or the VLOOKUP function. Here are some examples of how to use these functions to achieve the same result as the IFS function in the previous scenario.
- Using the IF function:
=IF(C2>=D2, "Good", IF(C2>=E2, "Average", "Poor"))
This formula uses nested IF functions to test the conditions and return the ratings. The IF function has the syntax:
=IF(logical_test, value_if_true, value_if_false)
The IF function can only handle one logical test and two values, so you need to nest multiple IF functions to test more than one condition. This can make the formula long and complex, and prone to errors.
- Using the CHOOSE function:
=CHOOSE((C2<D2)+(C2<E2)+1, "Good", "Average", "Poor")
This formula uses the CHOOSE function to return the ratings based on the index number. The CHOOSE function has the syntax:
=CHOOSE(index_num, value1, [value2], ...)
The CHOOSE function can return up to 254 values based on the index number, which must be a number between 1 and 254. To get the index number, this formula uses a clever trick of adding the results of two logical tests, which return either 0 or 1. For example, if C2 is greater than or equal to D2, and greater than or equal to E2, the index number will be (0+0+1) = 1, and the CHOOSE function will return “Good”. If C2 is less than D2, but greater than or equal to E2, the index number will be (1+0+1) = 2, and the CHOOSE function will return “Average”. If C2 is less than D2, and less than E2, the index number will be (1+1+1) = 3, and the CHOOSE function will return “Poor”.
The CHOOSE function can be shorter and faster than the IFS function, but it requires some mathematical logic and may not be intuitive to understand.
- Using the VLOOKUP function:
=VLOOKUP(C2, G2:H4, 2, TRUE)
This formula uses the VLOOKUP function to look up the sales in cell C2 in a table of ratings, and return the corresponding rating. The VLOOKUP function has the syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The VLOOKUP function can look up a value in the first column of a table, and return a value from another column in the same row. To use the VLOOKUP function, you need to create a table of ratings, where the first column contains the minimum sales for each rating, and the second column contains the rating names. The table looks like this:
Table
| Minimum Sales | Rating |
|---|---|
| 0 | Poor |
| 50 | Average |
| 90 | Good |
The VLOOKUP function uses the lookup value in cell C2, the table array in G2:H4, the column index number 2, and the range lookup TRUE, which means approximate match. The function will find the largest value in the first column that is less than or equal to the lookup value, and return the value from the second column in the same row. For example, if C2 is 100, the function will find 90 in the first column, and return “Good” from the second column.
The VLOOKUP function can be simpler and more flexible than the IFS function, but it requires an extra table of ratings, and it may not work well with non-numeric values.
