Locate Differences From Within Two Excel Lists Using the Match Function

In this post, lets say you have a list of confirmed RSVP guests. You also have another list of actual guests who showed up. The problem is that you have some people who showed up but did not RSVP. In the graphic below can you spot out the two people that are in Column C (Actual List of Guests) but are not in Column A (Confirmed RSVP Guest Master List)? Probably, I guess it wouldn’t take that long for you find the two, but what if you had 500 or 1000 people on the list? Forget that approach. See my previous post on how to do this in Access here.

Master List vs Show Up List

The Match function returns the relative position of an item in an array that matches a specified value in a specified order is how Excel describes the Match function. That is a mouthful but let me try it in my own words. Tell Excel an item to look up. For example “Amy Woodson” from cell C2. Next tell Excel where to look to check where “Amy Woodson” is would be located from another list ($A$2:$A$10). Choose to find an exact match so type zero for the third argument (Match_type). If Excel finds “Amy Woodson” in the cross referenced list, then return what position it is in. So we will get a whole number if “Amy Woodson” is in both list. She is actually in the eight spot. That is why Excel places the value 8 next to her. Now how this is going to work is that if Excel cannot find the match, it will return an error #N/A. That is what will then tell us that we have someone that is not in both lists.

Match function

BINGO! We could then filter out all the #N/As to find out who was not on both lists. Henry Boxson and David Chapmen are not in the RSVP list in column A.

Match NA

If you liked this post you also might be interested in another post I wrote about ditching the VLookup and using the Match and Index instead to find the lookup value.

And here is my favorite PDF of  Excel Keyboard Shortcuts reference list.

 

 

 

Advertisements

About Steve Chase

I want to help you work in your business, so you can work on your business. I want to help you achieve your goals and help you have a successful small business journey. I understand that keeping up with the latest technology and cloud based computer trends can be difficult, when you’re running your day to day business. But, it is crucial that you keep up with the technology and best understand your options, in order to thrive in this new era of online business. I can help you discover the right software and tools for your business, train your team on how to use them, and mentor you to best serve and delight your customers. My certifications include: QuickBooks ProAdvisor, Microsoft Office Specialist Master, and OntraPort Certified Consultant. Please contact me on my new website at www.sequentiasolutions.com
This entry was posted in Excel. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s