How To Match Data In Excel?

What Is Match Data in Excel?

Match data in Excel is a method to check each cell in a column against the corresponding cell in another column to identify a match. On the other hand, if the values in the cells we compare do not match, we can display an appropriate message.

Users can utilize the match data method in Excel while performing risk assessments and cost estimations using data in one or more spreadsheets.

For example, the image below shows two datasets.

The first lists students and their test scores in Mathematics in descending order of the scores. On the other hand, the second lists the same students, but in alphabetical order, and their test scores.

How To Match Data In Excel - Definition Example - 1

The aim is to check if each student’s score is the same in the two datasets and show the difference in the test scores of each student, if any, in column H.

Then, we can find and match data in Excel in columns G and H, using the Excel VLOOKUP function and the Subtraction operator, ‘‘, respectively.

How To Match Data In Excel - Definition Example - 2

In the above find and match data in Excel example, we apply the VLOOKUP() in column G since the student names are in different orders in the two datasets. The function updates the test score of each student, specified in the second dataset, based on the first dataset.

Next, we find the difference between the test scores in each row of columns E and G. This step helps us determine the students for whom the test scores match in the two datasets.

A difference of 0 between two test scores indicates a match. On the other hand, if the difference is a non-zero value, it indicates a mismatch between the test scores in the two datasets.

Furthermore, the above method will also work when the two datasets are in two different sheets, and we must match data in Excel from 2 worksheets.

Key Takeaways
  • The match data in Excelis a technique to check if the value in a cell in one column matches the value in the corresponding cell in another column. The columns can be on the same or different sheets. Also, we can display customized messages for matches and discrepancies.
  • Users can match data in one or more sheets to find matching data and differences in the data in different datasets while working with financial and accounting data.
  • Users can use the VLOOKUP(), INDEX + MATCH function and use these functions based on uniquely-created lookup values to match data in one or more worksheets.

How To Match Data In Excel?

We can match data in a spreadsheet using the following methods:

  1. Match Data Using VLOOKUP Function
  2. Match Data Using INDEX + MATCH Function
  3. Create Your Own Lookup Value


Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Method #1 – Match Data Using VLOOKUP Function

The steps to compare and match data in Excel using the VLOOKUP function and the Subtraction operator, ‘‘, are as follows:

  1. Choose a cell to apply the VLOOKUP().
  2. Enter the following VLOOKUP() formula in the chosen cell.

=VLOOKUP(lookup_value_dataset2, table_array_dataset1, col_index_num, [range_lookup])

While the lookup value will be in the second dataset, the table array will be in the first dataset. On the other hand, the col_index_num and range_lookup arguments have the same definition as in a regular VLOOKUP().

  1. Press Enter to view the required return value corresponding to the specified lookup value.
  2. Choose a cell to check if the data in the second dataset and the VLOOKUP() return value in the corresponding row match or not.
  3. Enter the ‘‘- based formula to match data in Excel in the target cell, which determines if the required data in the two datasets match or are different.

=data_second dataset – data_VLOOKUP() return value

Thus, the VLOOKUP() helps obtain the data pertaining to the lookup value in the second dataset based on the first. Next, the second formula helps determine the data match and mismatch between the two datasets.

Method #2 – Match Data Using INDEX + MATCH Function

This method is helpful when the target column containing the return value we aim to retrieve is on the left of the range containing the lookup value.

The steps to compare and match data in Excel using the INDEX + MATCH function and the Subtraction operator, ‘‘, are as follows:

  1. Choose a cell to insert the INDEX + MATCH function.
  2. Enter the following INDEX + MATCH () formula in the chosen cell.

=INDEX(array_dataset1,MATCH(lookup_value_dataset2,lookup_array_dataset1,[match_type]))

The array range in the INDEX Excel function will be in the first dataset, and the Excel MATCH function is the row_num argument value in the INDEX().

Next, while the lookup value will be in the second dataset, the lookup array will be in the first dataset in the MATCH(). On the other hand, the match_type argument has the same definition as in a regular MATCH().

  1. Press Enter to view the required return value corresponding to the specified lookup value.
  2. Choose a cell to check if the data in the second dataset and the INDEX + MATCH function return value in the corresponding row match or not.
  3. Enter the ‘‘- based formula to match data in Excel in the target cell, which determines if the required data in the two datasets match or are different.

=data_dataset2 – data_ INDEX + MATCH_function_return value

Thus, MATCH() returns the position of a cell in the first dataset as the required row number based on the lookup value in the second dataset. Next, the INDEX() finds the value of the cell in the first dataset at the intersection of the specified column and row number the MATCH() returns. Next, the second formula helps determine the data match and mismatch between the two datasets.

Method #3 – Create Your Own Lookup Value

Sometimes, the lookup value in the second dataset may appear multiple times in the lookup range in the first dataset.

Then, the VLOOKUP and the INDEX + MATCH functions output will be the return value corresponding to the first appearance of the lookup value in the first dataset. But in some cases, this output may be incorrect.

Thus, here is how to create unique lookup values for the two datasets in question to obtain the correct return value for every lookup value.

  1. Insert a new column in the first dataset, and the new column must be before the column containing the target values we aim to find based on the lookup values.
  2. Concatenate the first dataset values in each row of the newly inserted column to form unique lookup values using the ‘&’ symbol, CONCAT(), or Excel CONCATENATE function.
  3. Insert a new column in the second dataset and repeat step 2 using the second dataset’s values to create unique lookup values.
  4. Apply method #1 or #2 to match data in a worksheet based on the newly created unique lookup values.

Please note that all three methods discussed above will work when we must match data in Excel from 2 worksheets.

Examples

Check out the following match values in Excel examples to use the functionality effectively.

Example #1 – Match Data Using VLOOKUP Function

We have a dataset containing a list of products and their order quantities, updated in two files manually, and they are as depicted in the image below.

Match Data Using VLOOKUP Function - Example 1

The task is to compare and match the order quantity data for each product in the two datasets. Assume the target columns are G and H.

Then, here is how to use the VLOOKUP() in column G to find the order quantity values for the products listed in File_2 based on the File_1 dataset. Then, use ‘‘ in column H to find the difference between the order quantity value in File_2 and the VLOOKUP() output for each product to find the required matches.

  • Step 1: Choose cell G3, enter the VLOOKUP(), and press Enter.

=VLOOKUP(D3,$A$3:$B$12,2,0)

Example 1 - Step 1
How To Match Data In Excel - Example 1 - Step 2
  • Step 3: Choose cell H3, enter the below formula, and press Enter.

=IF((E3-G3)=0,”-“,(E3-G3))

Example 1 - Step 3
  • Step 4: Using the Excel fill handle, update the formula in cells H4:H12.
Match Data Using VLOOKUP Function - Example 1 - Step 4

Let us check the row 12 formulas in cells G12 and H12 to understand the method and the output.

The VLOOKUP() in cell G12 searches for the cell D12 lookup value in the range A3:B12 and returns the corresponding return value from the range B3:B12, 2,942.

Next, the Excel IF function condition contains the subtraction formula, which finds the difference between the order quantity value in cell E12 and the VLOOKUP() output in cell G2. The IF() condition checks if the difference equals zero. Since the condition holds in this case, the IF() returns the TRUE value, ‘‘, indicating the order quantity values in the two files for the product cited in File_2 match.

Thus, in this way, we can find the matches between two datasets and also the discrepancies to correct them.

Example #2 – Match Data Using INDEX + MATCH Function

The image below shows two datasets containing the monthly sales figures at a firm.

However, while the first dataset follows month-wise order, the second is the corresponding months in each quarter-based.

Match Data Using INDEX + MATCH Function - Example 2

Thus, since the data order differs in the two datasets, the requirement is to compare the sales figures in the two datasets for the matches and differences. Assume the target columns as I and J.

Then, we can use the INDEX + MATCH function in column I cells and the ‘‘ sign in column J cells to achieve the required match and difference data.

  • Step 1: Choose cell I3, enter the INDEX + MATCH function, and press Enter.

=INDEX($C$3:$C$14,MATCH(E3,$A$3:$A$14,0))

Example 2 - Step 1
  • Step 2: Using the Excel fill handle, update the INDEX + MATCH function in cells I4:I14.
How To Match Data In Excel - Example 2 - Step 2
  • Step 3: Choose cell J3, enter the below formula, and press Enter.

=IF((G3-I3)=0,”-“,(G3-I3))

Example 2 - Step 3
  • Step 4: Using the Excel fill handle, update the formula in cells J4:J14.
Match Data Using INDEX + MATCH Function - Example 2 - Step 4

Let us check the row 14 formulas in cells I14 and J14 to understand the method and the output.

The MATCH() in cell I14 finds the position of the lookup value, “Dec”, in the range A3:A14, which is 12. Next, the INDEX() determines the value in the cell at the intersection of the 12th row of column range C3:C14 and the column range C3:C14, which is cell C14 value, 11,213.

Next, the subtraction formula in the IF() condition finds the difference between the sales figure in the second dataset in cell G14 and the INDEX + MATCH function output in cell I14. Since the difference is 0, the IF() condition holds, leading to the IF() returning the TRUE value, ‘‘, indicating the December sales figures in the two datasets match.

Thus, the method helps find the matches between two datasets and also the discrepancies to resolve them.

Example #3 – Create Your Own Lookup Value

The image below shows two datasets containing a list of employees, their teams and annual salary details.

Create Your Own Lookup Value - Example 3

The task is to compare the annual salary data for each employee in the two datasets for matches.

Then, the steps are as follows:

  • Step 1: Choose cell I3, enter the VLOOKUP(), and press Enter.

=VLOOKUP(E3,$A$3:$C$15,3,0)

Example 3 - Step 1
  • Step 2: Using the Excel fill handle, update the VLOOKUP() in cells I4:I15.
How To Match Data In Excel - Example 3 - Step 2
  • Step 3: Choose cell J3, enter the below formula, and press Enter.

=IF((G3-I3)=0,”-“,(G3-I3))

Example 3 - Step 3
  • Step 4: Using the Excel fill handle, update the formula in cells J4:J15.
Create Your Own Lookup Value - Example 3 - Step 4

The output shows the annual salary figures for three employees, Alan Gracia, Jose Clark, and Patrick Murphy, do not match within the two datasets.

The reason is that the abovementioned names appear more than once in the lookup range. Hence, the VLOOKUP() output is the return value corresponding to the specific employee name’s first appearance in the lookup range, which may be incorrect.

So, here is how to create a unique lookup value for each employee to ensure the VLOOKUP() returns the correct output and required data matches for all the employees.

  • Step 1: Introduce a new column in each dataset to update the unique lookup value for each employee.

Please ensure the new column in the first dataset is on the left of the target column containing the return value. Otherwise, the VLOOKUP() will not work.

Create Your Own Lookup Value - Example 3-1 - Step 1
  • Step 2: Choose cell A3, enter the following formula, and press Enter.

=B3&C3&D3

Example 3-1 - Step 2
  • Step 3: Using the fill handle, enter the formula in cells A4:A15.
How To Match Data In Excel - Example 3-1 - Step 3
  • Step 4: Choose cell F3, enter the below formula, and press Enter.

=G3&H3&I3

Example 3-1 - Step 4

Step 5: Using the fill handle, enter the formula in cells F4:F15.

How To Match Data In Excel - Example 3-1 - Step 5
  • Step 6: Choose cell K3, enter the VLOOKUP(), and press Enter.

=VLOOKUP(F3,$A$3:$D$15,4,0)

Example 3-1 - Step 6
  • Step 7: Using the fill handle, update the VLOOKUP() in cells K4:K15.
Create Your Own Lookup Value - Example 3-1 - Step 7
  • Step 8: Choose cell L3, enter the IF(), and press Enter.

=IF((I3-K3)=0,”-“,(I3-K3))

Example 3-1 - Step 8
  • Step 9: Using the fill handle, implement the formula in cells L4:L15.
Create Your Own Lookup Value - Example 3-1 - Step 9

The logic of this method is the same as Method #1, except the function will search the column F lookup value in column A and return the correct output accordingly.

Thus, now we see the discrepancies in the annual salaries of the three previously-mentioned employees are resolved. The reason is that the concatenated values used as the unique lookup values contain the employee name, their team and annual salary data. It leads to the VLOOKUP() locating the lookup value in the lookup range correctly and returning the corresponding return value.

Important Things To Note

  • The argument values supplied to the VLOOKUP() to match data in Excel must adhere to the function syntax and definition. Otherwise, the function may return errors such as the #N/A, #REF!, Excel #VALUE! error, #NAME?, and #SPILL!.
  • Switching the lookup and return column values and supplying invalid ranges to the INDEX + MATCH function to match Excel data can lead to incorrect output or errors.

Frequently Asked Questions (FAQs)

1. How to match data in Excel from 2 worksheets?

We can match data in Excel from 2 worksheets, as explained below with an example.

The first two images show two worksheets containing US states and their population statistics.

How To Match Data In Excel - FAQ 1-1

FAQ 1-2

While the first worksheet shows the US states and population data by rank, the second contains the data in the alphabetical order of the US states.

The task is to compare the population data between the two sheets for matches. Assume the target cells are in the first sheet.

• Step 1: Choose cell D3 in the first worksheet and enter the VLOOKUP().
=VLOOKUP(A3,

FAQ 1 - Step 1a

Next, click the second worksheet tab to open it and select the lookup range A3:B17.
Excel will show the worksheet name of the chosen range in the formula.

FAQ 1 - Step 1b

After that, complete the VLOOKUP().
=VLOOKUP(A3,’US_State_Pop_Alphabetical Order’!A3:B17,2,0)

FAQ 1 - Step 1c

• Step 2: Press Enter to execute the function, and the first worksheet will open and show the function output in cell D3.

FAQ 1 - Step 2a

Next, make the range reference in the formula absolute.

How To Match Data In Excel - FAQ 1 - Step 2b

• Step 3: Using the fill handle, update the formula in cells D4:D17.

FAQ 1 - Step 3

• Step 4: Choose cell E3, enter the IF(), and press Enter.
=IF((B3-D3)=0,”-“,(B3-D3))

FAQ 1 - Step 4

• Step 5: Using the fill handle, implement the formula in cells E4:E17.

How To Match Data In Excel - FAQ 1 - Step 5

The method’s logic is the same as that of Method #1, explained in the above article. However, the only difference is that the VLOOKUP() shows the worksheet name containing the lookup range, indicating that the match is between two worksheets.

2. How to match dates in Excel?

We can match dates in Excel by first using any of the following formulas.

=cell_reference_date_value1=cell_reference_date_value2
Or
=VALUE(“cell_reference_date_value1”)=VALUE(“cell_reference_date_value2”)
Or
=VALUE(“valid_date_value1”)=VALUE(“valid_date_value2”)
Or
=DATEVALUE(“valid_date_value1”)=DATEVALUE(“valid_date_value2”)

3. How do I match data in Excel with multiple criteria?

You can match data in Excel with multiple criteria using the following array formula:

{=INDEX(return_range,MATCH(1,(criteria1=range1)*(criteria2=range2)*(…),0))}

While return_range is the range from which we aim the function to return the output or return value, criteria1, criteria2, … are the conditions for the match. And range1, range2, … are the ranges where the corresponding criteria should be checked.
Next, we can use the ‘‘ operator to find the difference between the source dataset data and the corresponding INDEX-MATCH() output to determine if the values match or differ.

Download Template

This article must be helpful to understand How To Match Data In Excel, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to How To Match Data In Excel. We learn to match data using VLOOKUP, INDEX + MATCH, & by creating our own lookup value, with examples. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *