This is a guest post written by Yoav Ezer. If you would like to write for theapptimes, check our Guest blogging guidelines. When you work with Microsoft Excel for long enough inevitably you will be presented with two lists of items that you need to work with or match. Everything from party invitations matched to RSVPs to supplier product codes and present stock levels, you will need to see if items match, are present, or maybe categorize your data. Obviously you don’t want to do this manually if the task is repetitive or if your lists are long… how can we automate this? In this Microsoft Excel 2010 tutorial, you will learn how to match two lists of items in Excel formulas.
How to Match Two Lists of Items in Microsoft Excel
Working Example
Say our company was a manufacturer of cooking equipment and supplies and we want to sell more in a certain area. We can match two lists to find potential new customers, one a list of contact data of new businesses in the area and the other a list of all the restaurants in the area so we can narrow down our efforts. By using both lists we can find the new restaurants.
Creating the Formula
In the first sheet of our list of newly opened businesses, we need to use COUNTIF. This allows us to count if the business also appears in the restaurant list, sheet2. If the business does appear we can set the end column to say “Restaurant”, leaving it blank otherwise.
Sound easy? It is!
First step is to see if our business is in the list. It essentially says “count how many of whatever is in cell A2 appears in the Restaurant List. If the response is fewer than 1 then we know it is not in the list.
COUNTIF('Restaurant List'!A$1:A$20,A2)
Next we wrap the whole thing with an IF which translates to “if the answer is greater than zero, set the cell as Restaurant, otherwise leave it blank.
IF(COUNTIF('Restaurant List'!A$1:A$20,A2)>0,"Restaurant","")
By using labels in this way, you can filter the list to show only new restaurants, which becomes our target list for sales activity.
Summary
As you can see, the solution was very simple but hides a great deal of power and potential. We used Excel’s built-in features to do something pretty clever, without resorting to more complex “merge” strategies that we might otherwise consider. How might you use this idea?
About the author: Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.
For more Excel tips from Yoav, join him on Facebook or Twitter
Using Microsoft Office is really a big part of my job. I’m doing data entry tasks daily and these tools are always keeping me busy. Thanks for sharing these tips.
Joan