Look Who it Was Random video

Excel - Master the Double Lookup Method in Excel for Matching and Analyzing Data - Episode 926

2,093 views · Published 12 January 2009 · 4:12 · Indexed 8 November 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial:  Master the Double Lookup Method in Excel for Matching and Analyzing Data.

Welcome back to the MrExcel netcast! In today's video, we'll be discussing a useful technique called the double lookup method. This method is perfect for situations where you have two worksheets with different information, but one field that ties them together. This tip comes from my very first book, "Guerilla Data Analysis using Microsoft Excel".

Our viewer, Jim, sent in a question asking if there is an automated way to match up data from two different worksheets. In Jim's case, the data was tied together by a serial number, but in our example, we'll be using customer names. On the first worksheet, we have a list of customers and their forecasted sales. On the second worksheet, we have a list of customers and their actual orders. Some customers are on both lists, while others are only on one. Is there a way to automatically match up this data?

The answer is yes, and we'll be using the double lookup method to do so. First, we'll add a new column to the second list called "THERE" to indicate if the customer is present in the first list. We'll use the MATCH function to check if the customer name is in the first list. If it is, the MATCH function will return the row number. If not, it will return #N/A. We'll then sort the data by the "THERE" column to bring all the #N/A values to the bottom. We can then copy and paste these customers to the first list and fill in their forecasted sales with 0.

Next, we'll do a VLOOKUP to bring in the actual orders for each customer. We'll sort the data again to bring all the #N/A values to the bottom, and then override them with 0. This will give us a complete list of customers with their forecasted and actual sales. This method may seem a bit complicated, but it's a great way to match up data from two different lists.

In tomorrow's video, we'll be exploring a different method using pivot tables, which may be easier and faster for some users. So be sure to tune in for that! Thanks for watching this netcast from MrExcel. Don't forget to like and subscribe for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Jim has two lists that he wants to match, but some records are missing from one list or the other. There are many ways to solve this. Episode 926 will show the classic double-lookup method.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Table of Contents:
(00:00) Introduction to the topic
(00:25) Explanation of data and question
(00:36) Methods for matching data
(01:00) Introduction to the double lookup method
(01:16) Adding a new column for the double lookup
(01:26) Using the MATCH function
(01:49) Sorting the data to identify missing values
(02:31) Second lookup using VLOOKUP
(03:19) Results of the double lookup method
(03:31) Introduction to a different method using pivot tables
(03:51) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Combining worksheets in Excel
Double lookup method in Excel
Easier and faster data analysis in Excel
Excel data analysis
Filling cells in Excel
MATCH function in Excel
Matching data in Excel
Sorting data in Excel
Superset of two lists in Excel
Using pivot tables in Excel
VLOOKUP in Excel

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152043/

More from this channel