Different Methods to Match Data in Excel

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

Now, let us discuss each of the methods in detail.

#1 – Match Data Using VLOOKUP Function

In Data 1, we have 12,104 for 04-Mar-2019. However, in Data 2, we have 15,104 for the same date, so there is a difference of 3,000.

  • For example, look at the below table. We have two data tables here. The first is “Data 1.” The second is “Data 2.” Now, we need to reconcile whether the data in the two tables match. The first way of comparing the data is the SUM function in excel to two tables to get the total sales.Data 1 – TableData 2 – Table We have applied the SUM function for the table’s “Sale Amount” column. In the beginning, we have got the difference in values. The Data 1 table shows the total sales of 2,16,214, and the Data 2 table shows 2,10,214. Now, we need to examine this in detail. So, let us apply the VLOOKUP function for each date. Select the table array as Data 1 range. We need the data from the second column, and the range of LOOKUP is “FALSE,” i.e., exact match. The output is given below: First, we must deduct the original value from the next cell’s arrival value. After deducting, we get the result as zero. We must copy and paste the formula to all the cells to get the variance values. In cells G6 and G12, we got the differences.

Now, we need to reconcile whether the data in the two tables match. The first way of comparing the data is the SUM function in excel to two tables to get the total sales.Data 1 – TableData 2 – Table

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

Similarly, for 18-Mar-2019 in Data 1, we have 19,351. In Data 2, we have 10,351, so the difference is 9,000.

#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 functionAlternative To The VLOOKUP FunctionTo reference data in columns from right to left, we can combine the index and match functions, which is one of the best Excel alternatives to Vlookup.read more.

The INDEX function is used to get the value from the selected column based on the row number provided. We need to use the MATCH functionUse The MATCH FunctionThe MATCH function looks for a specific value and returns its relative position in a given range of cells. The output is the first position found for the given value. Being a lookup and reference function, it works for both an exact and approximate match. For example, if the range A11:A15 consists of the numbers 2, 9, 8, 14, 32, the formula “MATCH(8,A11:A15,0)” returns 3. This is because the number 8 is at the third position. read more based on the LOOKUP value to give the row number.

We must Open INDEX functionOpen INDEX FunctionThe INDEX function in Excel helps extract the value of a cell, which is within a specified array (range) and, at the intersection of the stated row and column numbers.read more in the F3 cell.

Then, select the array as a result column range, B2 to B14.

Open the MATCH function as the next argument to get the row number.

Select the LOOKUP value as a D3 cell.

Next, select the lookup array as the “Sales Date” column in Data 1.

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

Now, close two brackets and press the “Enter” key to get the result.

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

#3 – Create Your Lookup Value

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

As shown above, we have zone-wise and date-wise sales data in the above data. Therefore, we need to perform the data matching process again. But, first, let us apply the VLOOKUP function as per the previous example.

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

In cell I5, we got a variance of 8,300. Let us look at the main table.

Even though the main table value is 12,104, we got the value of 20,404 from the VLOOKUP function. That is because VLOOKUP can return the first found LOOKUP value.

In this case, our LOOKUP value is a date of 20-Mar-2019. In the above cell for the “North” zone for the same date, we have a value of 20,404, so VLOOKUP has returned this value for the “East” zone.

To overcome this issue, we need to create unique LOOKUP values. For example, combine Zone, Date, and Sales Amount in Data 1 and Data 2.

Data 1 – Table

Data 2 – Table

We have created unique values for each zone with the combined value of “Zone,” “Sale Date,” and “Sale Amount.”

Using these unique values, let us 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. First, however, we need to look at the duplicates in the LOOKUP value for accurate reconciliationReconciliationReconciliation is the process of comparing account balances to identify any financial inconsistencies, discrepancies, omissions, or even fraud. At the end of any accounting period, reconciliation involves matching balances and ensuring that debits (credits) from one account for one transaction is same as the credit (debits) to another account for the same transaction.read more before applying the formula. The above example is the best illustration of duplicate values in the LOOKUP value. We need to create our unique LOOKUP values in such scenarios and arrive at the result.

This article is a guide to Match Data in Excel. We discuss matching data in Excel using the VLOOKUP function, INDEX + MATCH function, and our LOOKUP value with a downloadable Excel template. You may learn more about Excel from the following articles: –

  • What is VBA Match?Vlookup to the LeftVLOOKUP Table ArrayLookup Excel Table