• About
  • Contact
  • Privacy Policy
  • Submit For Review
  • Template Gallery
  • Excel 2016 Udemy Course
  • Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

TheAppTimes

App Reviews, Tutorials, & Gadget News Hub

  • Gadgets
  • How To
  • Mobile Apps
  • Software and Web Apps
  • Buying Guides
  • Shop
  • Show Search
Hide Search
Home » How To » Excel 2010: How to Match Two Lists of Items in Microsoft Excel

Excel 2010: How to Match Two Lists of Items in Microsoft Excel

March 31, 2011

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.

How to Match Two Lists

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.

ALSO READ:  How to Use Microsoft Access

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","")

leave-it-blank

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

Tweet
Share1
WhatsApp
Pin
Buffer
1 Shares

Filed Under: How To Tagged With: excel, microsoft

You May Also Like

Manage Android Phone Notifications with Microsoft Your Phone App - TATFI
Read and Reply to Messages on Android from Your PC with Microsoft Your Phone App
Which Microsoft Office Version Are You Using
Which Microsoft Office Version (32-bit or 64-bit) Are You Using?
How to Delete Blank Rows in Excel 2016
How to Delete Blank Rows in Excel 2016

About Adeline Gear

Adeline is a tech enthusiast who loves exploring the latest tools and applications in the marketplace. She also loves her Android and spends time reviewing apps and playing games when she has the time.

Reader Interactions

Comments

  1. Joan @ Easy ways to make money online says

    August 16, 2011 at 8:22 pm

    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

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Hottest New Gadgets!

Fast External SSD Gadgets for your Mac - TATFI

4 Fast External SSD Gadgets for your Mac (2021)

Anker Powerhouse 100 - TATFI

Charge Your iPhone, Galaxy, iPad, or MacBook with the Portable Power Bank Anker Powerhouse 100

Logitech Circle View - TATFI

Logitech Circle View – A Great Wired Outdoor Camera for HomeKit Users

Spigen Galaxy S21 Cases - TATFI

Spigen Galaxy S21 Cases is Perfect for New Samsung Smartphone

Samsung Galaxy SmartTag Bluetooth Tracker - TATFI

Find Lost Items with the Samsung Galaxy SmartTag Bluetooth Tracker

Popular Tags

android android apps android games android how to apple chrome extensions excel excel how to facebook gadgets gaming gmail google instagram ios apps ios games ios how to ios tips ipad iphone kids apps learn excel management microsoft music apps new releases outlook how to photo apps powerpoint powerpoint how to productivity Samsung security smartphone smartphones software tablet tablets technology twitter web browsers windows 10 word how to xbox youtube

Disclosure

TheAppTimes is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for us to earn fees by linking to Amazon.com and affiliated sites.

TheAppTimes © 2021 // Runs on StudioPress