WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » How to Match Data in Excel?

How to Match Data in Excel?

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Different Methods to Match Data in Excel

There are various methods to match data in excel, if we want to match the data in the same column let’s say we want to check for duplicity we can use conditional formatting from the home tab or else if we want to match the data in two or more different columns we can use conditional functions like if function.

  • Method #1 – Using Vlookup Function
  • Method #2 – Using Index + Match Function
  • Method #3 – Create Your Own Lookup Value

Now let us discuss each of the methods in detail

You can download this Match Data Excel Template here – Match Data Excel Template

#1 – Match Data Using VLOOKUP Function

VLOOKUP is not only used to get the required information from the data table; rather, it can be used as a reconciliation tool as well. When it comes to reconciliation or matching the data VLOOKUP formula leads the table.

For example, look at the below table.

Match data Example 1

We have two data tables here, first one is Data 1 & the second one is Data 2.

Now we need to reconcile whether the data in the two tables are matching or not. The very first way of matching the data is the SUM function in excel to two tables to get the total sales.

Data 1 – Table

Match data Example 1-2

Data 2 – Table

Match data Example 1-3Match data Example 1-3

I have applied the SUM function for both the table’s Sale Amount column. At the beginning step itself, we got the difference in values. Data 1 table showing the total sales of 2,16,214, and the Data 2 table showing the total sales of 2,10,214.

Now we need to examine this in detail. So, let’s apply the VLOOKUP function for each date.

Match data Example 1-4

Select the table array as Data 1 range.

Match data Example 1-5

We need the data from the second column, and the range of lookup is FALSE, i.e., Exact Match.

Match data Example 1-6

The Output is given below:

Match data Example 1-7

In the next cell, deduct the original value with arrive value.

Match data Example 1-8

After deducting, we get the result as zero.

Match data Example 1-9

Now copy and paste the formula to all the cells to get the variance values.

Match data Example 1-10

In cell G6 and G12, we got the differences.

Match data Example 1-11

In Data 1, we have 12104 for the Date 04-Mar-2019, and in Data 2, we have 15104 for the same date, so there is a difference of 3000.

Similarly, for the date 18-Mar-2019 in Data 1, we have 19351, and in Data 2, we have 10351, so the difference is 9000.

#2 – Match Data Using INDEX + MATCH Function

For the same data, we can use the INDEX + MATCH function. We can use this as an alternative to the VLOOKUP function.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

The INDEX function is used to get the value from the selected column based on the row number provided. To provide the row number, we need to use the MATCH function based on the LOOKUP value.

Open INDEX function in the F3 cell.

Match data Example 2-1

Select the array as a result column range, i.e., B2 to B14.

Match data Example 2-2

In order to get the row number, open MATCH function now as the next argument.

Example 2-3

Select the lookup value as a D3 cell.

Example 2-4

Next select lookup array as Sales Date column in Data 1.

Example 2-5

In the match type, select “0 – Exact Match”.

Example 2-6

Close two brackets and hit the enter key to get the result.

Match data Example 2-7

This also gives the same result as VLOOKUP only. Since we have used the same data, we got the numbers as it is

#3 – Create Your Own Lookup Value

Now we have seen how to match data using excel functions. Now we will see the different scenarios of real-time. For this example, look at the below data.

Example 3

In the above data, we have Zone-Wise and Date-wise sales data, as shown above. We need to again do the data matching process. Let’s apply the VLOOKUP function as per the previous example.

Example 3-1

We got many variances. Let’s examine each case by case.

In cell I5, we got the variance of 8300. Let’s look at the main table.

Match data Example 3-2

Even though the main table value is 12104, we got the value of 20404 from the VLOOKUP function. The reason for this is VLOOKUP can return the value of the first found lookup value.

In this case, our lookup value is a date, i.e., 20-Mar-2019. In the above cell for the North zone for the same date, we have a value of 20404, so VLOOKUP has returned this value for the East zone as well.

To overcome this issue, we need to create unique lookup values. Combine Zone, Date & Sales Amount in both Data 1 & Data 2.

Data 1 – Table

Example 3-3

Data 2 – Table

Match data Example 3-4

Now we have created unique value for each zone with the combined value of Zone, Sale Date, and Sale Amount.

Using these unique values, let’s apply the VLOOKUP function.

Match data Example 3-5

Apply the formula to all the cells; we will get the variance of zero in all the cells.

Match data Example 3-6

Like this, by using excel functions, we can match the data and find the variances. Before applying the formula, we need to look at the duplicates in the lookup value for accurate reconciliation. The above example is the best illustration of duplicate values in lookup value. In such scenarios, we need to create our own unique lookup values and arrive at the result.

Recommended Articles

This has been a guide to Match Data in Excel. Here we will learn how to match data in excel using VLOOKUP Function, INDEX + MATCH Function, and own LOOKUP value with a downloadable excel template. You may learn more about excel from the following articles –

  • What is VBA Match?
  • Vlookup to the Left
  • VLOOKUP Table Array
  • Lookup Excel Table
12 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Match Data Excel Template

Special Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More