How To Match Data In Excel? (2024)

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.

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.

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.

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

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

3. Press Enter to view the required return value corresponding to the specified lookup value.
4. Choose a cell to check if the data in the second dataset and the VLOOKUP() return value in the corresponding row match or not.
5. 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().

3. Press Enter to view the required return value corresponding to the specified lookup value.
4. 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.
5. 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.

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)

• Step 2: Using the Excel fill handle, update the VLOOKUP() in cells G4:G12.

• Step 3: Choose cell H3, enter the below formula, and press Enter.

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

• Step 4: Using the Excel fill handle, update the formula in cells H4:H12.

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.

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

• Step 2: Using the Excel fill handle, update the INDEX + MATCH function in cells I4:I14.

• Step 3: Choose cell J3, enter the below formula, and press Enter.

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

• Step 4: Using the Excel fill handle, update the formula in cells J4:J14.

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 12^th 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.

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)

• Step 2: Using the Excel fill handle, update the VLOOKUP() in cells I4:I15.

• Step 3: Choose cell J3, enter the below formula, and press Enter.

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

• Step 4: Using the Excel fill handle, update the formula in cells J4:J15.

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.

• Step 2: Choose cell A3, enter the following formula, and press Enter.

=B3&C3&D3

• Step 3: Using the fill handle, enter the formula in cells A4:A15.

• Step 4: Choose cell F3, enter the below formula, and press Enter.

=G3&H3&I3

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

• Step 6: Choose cell K3, enter the VLOOKUP(), and press Enter.

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

• Step 7: Using the fill handle, update the VLOOKUP() in cells K4:K15.

• Step 8: Choose cell L3, enter the IF(), and press Enter.

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

• Step 9: Using the fill handle, implement the formula in cells L4:L15.

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)

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.

Recommended Articles

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–

• Compare Two Columns in Excel for Matches
• Find And Replace In Excel
• Excel Column Auto Width