13. Exercise - Comparison of two lists


Prerequisites

You should read the Excel Functions course up to the chapter on COUNTIF, COUNTIFS, SUMIF and SUMIFS functions before starting this exercise.

Question

Comparing two lists is a common problem. Here is a simple example:

Consider the following spreadsheet with a list of members of an association:

Excel 2016 Exercise Comparaison

Another sheet contains a list of personalities:

Excel 2016 Exercise Comparaison

I assume that both sheets belong to the same workbook, and that column A "Name" does not contain any duplicates.

Write the formula in column E of the 1st sheet to indicate whether the member is in the list of the 2nd sheet or not by displaying "Exists" or "Does not exist".


Indications Exercise - Comparison of two lists


Solution :

Formula in E2 :

=IF(COUNTIF(Sheet2!$A$2:$A$544,A2)>0, "Exists", "Does not exist")

 

Explanation :

The function COUNTIF is used to count the number of occurrences of the value in A2 in the range A2:A544 of the sheet "Sheet2". If an occurrence exists, then the function IF returns the text "Exists". Otherwise, the text "Does not exist" is returned.

For the copy down of the formula in E2, the absolute cell reference is used for the range A2:A544 of the sheet "Sheet2".


<< 12. Exercise 2 - Logical functions

14. Exercise – COUNTIF, COUNTIFS, SUMIF and SUMIFS functions >>