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 using Excel

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 the BOLD part returns 1 or more, then the 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 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:

Matching text in a list

Matching text in a list using Excel.

This technique can be adapted to other types of list-matching tasks.

Read More…

Data clean-up techniques in Excel: Replacing or removing text in cells

Data clean-up techniques in Excel: Changing vertical data to horizontal data

Data clean-up techniques in Excel: Extracting a filename from a URL

Happy Excelling 🙂



Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy:

We will be happy to hear your thoughts

Leave a reply