Data clean-up techniques in Excel: Matching text in a list

You may have some data and you want to check this data against another available list.

Download this file to work with us in the following example.

See the following screenshot of our example. We are going to find out the persons who have resigned from the left side of our example. There is a list on the right side of the resigned numbers.

Matching text in a list in Excel. The goal is to identify member numbers that are in the resigned members list in column F.

If you want to grasp this example very well, you have to be an expert on the following topics.

How to copy formula in Excel.

IF function in Excel.

COUNTIF formula in Excel.

The above screenshot shows a simple example. The data is in the range A5: C133. The goal is to identify the rows in the data zone which are appearing in the Resigned Members list, in column F. You can delete these unnecessary rows later.

Here’s a formula entered in cell D5, and copied down to cells where Names are available.

=IF(COUNTIF($F$2:$F$22,B2)>0,”Resigned”,”” ), the bold part of the formula will return 1 if it (bold part) matches a list value with a data value. If BOLD part returns 1 or more, then IF function will return “Resigned”, otherwise nothing.

This whole formula will display the word “Resigned” if the “Member Num” in column B is found in the “Resigned Members” list. If the Member number is not found, it returns an empty string. You can sort the list by column D, the rows for all resigned members will appear together and can be quickly deleted. To sort by the column D, just select from cell D5 to D133 and then choose Home⇒Editing⇒Sort & Filter ⇒ Sort Z to A.

Our Excel sample file will be like this one: