How to Match Data in Excel?

Match Data in Excel (Table of Contents)

• Match Data Using VLOOKUP Function
• Match Data by INDEX + MATCH Function
• Create Your Own Lookup Value

Match Data Using Excel Functions

When we do analysis in excel, it is foolishness to expect the data from the single source itself. We usually get the data from multiple sources and that is how it works 99% of the time. Often times we may require to match both the data and that is called “Reconciliation”. Reconciliation is an art to master in excel, to reconcile you need excel formulas extensive knowledge. We can use many types of formulas to match the data. In this article, we will show you how to match data in excel.

#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 an example look at the below table.

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

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

Data 1 – Table

Data 2 – Table

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 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.

Select the table array as Data 1 range.

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

The Output is given below:

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

After deducting we get the result as zero.

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

In cell G6 and G12 we got the differences.

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 VLOOKUP function.

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

Open INDEX function in F3 cell.

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

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

Select the lookup value as D3 cell.

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

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

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

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 scenario of real time. For this example look at the below data.

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.

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

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

Even though in the main table value is 12104 we got the value of 20404 from 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 date i.e. 20-Mar-2019. In the above cell for North zone for the same date we have a value of 20404, so VLOOKUP has returned this value for 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

Data 2 – Table

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

Using these unique values lets apply the VLOOKUP function.

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

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. 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 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 downloadable excel template. You may learn more about excel from the following articles –

• Create Family Tree Excel Template
• How to Create a Word Cloud in Excel?
• What is VBA Match?
• How to Vlookup to the Left?
• VLOOKUP Table Array Excel
• Lookup Excel Table