compare two lists for matches excel 2016 mac

If either one of the criteria is fulfilled, the student will be declared as Pass else, he/she will be declared as Fail. Suppose we have two criteria’s under this example:Ĭriteria 1: Column B >= 20 and Column C >= 23Ĭriteria 2: Column B >= 15 and Column C >= 18 In order to check whether the student has passed the test or he failed, we can use a combination of IF statement with AND/OR operator to combine the multiple criteria. We wanted to check whether the student has passed the exam or not. Suppose we conduct two tests in a class of 10 students and have Marks in both tests for each student stored under column Marks 1 and Marks 2 respectively. However, with E1, it is still FALSE since not all the conditions are satisfied.Įxample #2 – Complex Criteria in Combination with IF, AND/OR Press Enter key, and you can see the output as TRUE in cell E2 since C1 and D1 have values greater than 16. Ideally, OR gives Boolean output as TRUE if at least one of the criteria is satisfied. Step 4: In cell E2, use the OR to check how it works use the same criteria values. Since the value in cell B1 is not greater than 16, not all the criteria are satisfied. You can see with the same criteria under AND function, if we change the value under cell B1 as less than the criteria value (i.e. Step 3: Change the value in cell B1 as 12 and see how the result in cell E1 is affected.

Compare two lists for matches excel 2016 mac