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 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".