Most Microsoft users familiar with the Excel spreadsheet already know and use the popular IF function, which is used to create queries by specifying one or more conditions to obtain a specific result. If you combine the IF and OR functions, just one of the conditions is true to display a result. We explain here how to do it with simple examples..
Excel's IF-OR function is an extension of the IF function , also known as the IF-THEN function. Thanks to its enormous versatility, it is one of the most used Excel functions. It is used to check if a certain value corresponds to an expected value. If both values are identical, a predefined state is activated. If not, another action occurs. To define whether a result is true or false, you must first set the conditions.
If you combine the IF function with the OR function , you have the possibility to establish several conditions. For the result to be TRUE, at least one of them must match the expected result. If conditions A or B are met, state C occurs. If neither of the two conditions is met, state D appears. The formula has the following structure:
=SI(O(algo es VERDADERO;algo diferente es VERDADERO);valor si es VERDADERO;valor si es FALSO)
In the above formula, the element? Something is TRUE? is condition A and? something different is TRUE? is condition B. Therefore, the? value if TRUE? corresponds to condition C and the? value if FALSE ?, to condition D..
An alternative to the IF-OR function is the IF-AND function. With this formula, both conditions A and B must be true for state C. to appear. The related function YES-NO, on the other hand, only accepts a single condition.
In the following table, column A lists the possible participants in a training program. To enroll in the educational center, people must have attended a preliminary basic course or have passed the entrance exam with a minimum of 600 points:
Since the conditions are clear, you can write the formula in the next step. First, select cell D2 . In this, the question about admission is answered with YES or NO. The YES status occurs if a participant has completed the basic course (value? Yes? In column B) or if they obtained more than 600 points in column C. The answer does NOT appear if neither condition is met. Now, enter the following formula in the edit bar:
=SI(O(B2="Sí";C2>599);"SÍ";"NO")
After confirming the formula with the return key, Excel displays the result in D2:
To also fill rows 3 through 6 of column D with the corresponding results, you do not need to re-enter the formula. Just mark cell D2 and drag the green frame to the last cell. Since the formula expresses a relative reference , B2 and C2 are automatically converted to B3 and C3..
The SI-O function has the advantage that it can be combined with other very useful functions, allowing other more complex queries to be carried out. The above examples are listed below in a table. In this case, the names and surnames of the possible participants are used in columns A and B:
In column E, do you want only the YES or NO answers to appear, but the complete sentence? [Name] [Surname] has been accepted? or? [Name] [Surname] has not been accepted ?. For this, you must integrate the CONCATENATE function in the SI-O function. Select cell E2, enter the following formula in the edit bar and drag it to the rest:
=CONCATENAR(A2;" ";B2; " ";SI(O(C2="Sí";D2>599);"ha sido aceptado/a";"no ha sido aceptado/a"))
The first part of the formula concatenates the contents of cells A and B. To prevent text elements from being inserted directly next to each other in the result, insert a blank space between quotation marks after A2 and B2. You then enter the IF-O function, which completes the sentence with the corresponding result.
Excel's SI-O function can be combined with many other functions as you need. However, make sure to put the correct amount of parentheses when nesting formulas, as if you don't close all of them, you will get an error message. To avoid these errors, you can use the formula generator from the menu item? Insert function ?, Which automatically generates the correct syntax.