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:

Another sheet contains a list of personalities:

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 1^st sheet to indicate whether the member is in the list of the 2^nd 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".

---