Eshrak Kader
While working in Microsoft Excel you may need to compare two sets of data and reconcile any missing or matching items. As with Excel, there are multiple ways of doing data reconciliation. In this article, we’ll demonstrate 9 ways how to reconcile two data sets in Excel.
Download Practice Workbooks
You can download the practice workbook from the link below.
9 Ways to Reconcile Two Sets of Data in Excel
Throughout this article, we’ll consider the dataset in the B4:F13 cells, which shows two lists containing Company names, and their Stock Price in USD respectively. Now, we want to check if the two lists are an exact match or if there are any mismatches. In order to do this, we’ll utilize various Excel tools and functions hence, without further delay let’s see each method individually.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.
Method-1: Comparing Rows to Reconcile Two Sets of Data
Let’s start with the simplest and the most obvious way, that is, to compare two datasets row by row. Now, allow me to demonstrate this simple process in the steps below.
📌 Steps:
- At the very beginning, insert a column with the header Result.
- Next, go to the H5 cell and type in the expression below.
=B5=E5
Here, B5 and E5 refer to the value Apple in List 1 and List 2. Now, when the value of B5 and E5 are the same, it returns TRUE otherwise it returns FALSE.
- Then, use the Fill Handle Tool to copy the formula into the cells below.
Finally, you should see the results as shown in the image below.
Read More: How to Reconcile Data in 2 Excel Sheets (4 Ways)
Method-2: Applying Conditional Formatting to Reconcile Two Sets of Data
The Conditional Formatting tool allows you to highlight cells based on given criteria. It is a quick and easy way to compare two columns visually. So, let’s see it in action.
📌 Steps:
- To begin with, select the List 1 and List 2 columns >> in the Home tab, click the Conditional Formatting drop-down >> select the Highlight Cells Rules option >> from the list, and press the Duplicate Values option.
This opens the Duplicate Values wizard.
- Next, choose the Unique values option >> then select Light Red Fill with Dark Red Text as the cell and text colors >> click the OK button.
- Similarly, choose the Duplicate values option >> then select Green Fill with Dark Green Text for the cell and text colors.
Lastly, the results should look like the picture given below.
Method-3: Utilizing MATCH Function
If you’re one of those people who enjoy using Excel functions then our third method has you covered. Here we’ll use the MATCH function to reconcile two sets of data in Excel. So, let’s begin.
📌 Steps:
- Initially, go to the H5 cell and insert the expression shown below.
=ISNUMBER(MATCH(E5,$B$5:$B$13,0))
In this formula, the E5 cell points to the value Apple while the B5:B13 range represents the List 1 array.
⚡ Formula Breakdown:
- MATCH(E5,$B$5:$B$13,0) → returns the relative position of an item in an array matching the given value. Here, E5 is the lookup_value argument which refers to Apple. Following, $B$5:$B$13 represents the lookup_array argument from where the value is matched. Lately, 0 is the optional match_type argument which indicates the Exact match criteria.
- Output → 1
- ISNUMBER(MATCH(E5,$B$5:$B$13,0)) → becomes
- ISNUMBER(1) → the ISNUMBER function checks whether a value is a number and returns TRUE or FALSE. Here, 1 is the value argument, and since it is a number so the function returns TRUE.
- Output → TRUE
Eventually, the results should look like the screenshot given below.
Method- 4: Using IF Function to Reconcile Two Sets of Data
Another Excel function that can be used to compare and reconcile data is the IF Function. So, let’s see the process step-by-step.
📌 Steps:
- Firstly, move to the H5 cell and type in the expression below.
=IF(B5=E5,"TRUE","FALSE")
In the above formula, the B5 and E5 represent the value Apple in List 1 and List 2.
⚡ Formula Breakdown:
- IF(B5=E5,”TRUE”,”FALSE”) → checks whether a condition is met and returns one value if TRUE and another value if FALSE. Here, B5=E5 is the logical_test argument which checks if the value in the B5 cell is equal to the E5 cell. If they are equal then the function returns text TRUE (value_if_true argument) otherwise it returns FALSE (value_if_false argument).
- Output → TRUE
Finally, the output should look like the screenshot shown below.
Similar Readings
- How to Create a Party Ledger Reconciliation Format in Excel
- How to Make a Vendor Ledger Reconciliation Format in Excel
Method-5: Employing Table Option with XMATCH Function to Reconcile Two Sets of Data
If using formulas doesn’t suit you then our next method might help you. Here, we’ll utilize Excel Table to compare and reconcile the dataset below. It’s simple and easy, just follow along.
📌 Steps:
- Firstly, select the B4:B13 range of cells >> move to the Insert tab >> click the Table option.
- Now, the Create Table wizard appears where you have to check the My table has headers option.
Next, the Table should look like the screenshot below.
- Following this, navigate to the H5 cell and enter the formula below.
=ISNUMBER(XMATCH(Table2[List 2],Table1[List 1]))
⚡ Formula Breakdown:
- XMATCH(Table2[List 2], Table1[List 1]) → the XMATCH function returns the relative position of an item in an array by default requiring an exact match. Here, Table2[List 2] is the lookup_value argument which refers to the B5:B13 array. Following, Table1[List 1] represents the lookup_array argument from where the value is matched.
- ISNUMBER(XMATCH(Table2[List 2],Table1[List 1])) → checks whether a value is a number and returns TRUE or FALSE. Here, XMATCH(Table2[List 2],Table1[List 1]) (value argument) which returns an array.
- Output → TRUE
📄 Note: The XMATCH function is available in Excel 365. If you’re using an older version then you can use the expression below to get the same results.
=ISNUMBER(MATCH(Table2[List 2],Table1[List 1],0))
After completing the above steps, the output should appear as the image given below.
Method-6: Reconcile Two Sets of Data Using VLOOKUP Function
Another way to compare and reconcile two datasets involves using the VLOOKUP function. So, just follow along.
📌 Steps:
- Initially, navigate to the H5 cell and type in the expression below.
=ISTEXT(VLOOKUP(E5,$B$5:$B$13,1,FALSE))
In this expression, the E5 cell points to the value Apple while the B5:B13 range represents the List 1 array.
⚡ Formula Breakdown:
- VLOOKUP(E5,$B$5:$B$13,1, FALSE) → looks for a value in the left-most column of a table and then returns a value in the same row from a column you specify. Here, E5 ( lookup_value argument) is mapped from the $B$5:$B$13 (table_array argument) array. Next, 1 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the Exact match of the lookup value.
- Output → Apple
- ISTEXT(VLOOKUP(E5,$B$5:$B$13,1,FALSE)) → becomes
- ISTEXT(Apple) → the ISTEXT function checks whether a value is text and returns TRUE or FALSE. Here, Apple is the value argument, and since it is a text string so the function returns TRUE.
- Output → TRUE
- Next, copy the formula to the cells below and the results should look like the picture shown below.
Method-7: Applying XLOOKUP Function
Similar to the previous method we can also employ the XLOOKUP function to reconcile the differences in our dataset. Now, let’s go through the steps.
📌 Steps:
- Firstly, go to the H5 cell and enter the formula below.
=ISTEXT(XLOOKUP(E5,$B$5:$B$13,$B$5:$B$13))
Here, the E5 cell points to the value Apple while the B5:B13 range represents the List 1 array.
⚡ Formula Breakdown:
- XLOOKUP(E5,$B$5:$B$13,$B$5:$B$13) → searches a range or an array for a match and returns the corresponding item from a second range or array. By default, an exact match is used. Here, E5 ( lookup_value argument) is mapped from the $B$5:$B$13 (lookup_array argument) array. Lastly, $B$5:$B$13 (return_array argument) represents the returned array or range.
- Output → Apple
- ISTEXT(XLOOKUP(E5,$B$5:$B$13,$B$5:$B$13)) → becomes
- ISTEXT(Apple) → checks whether a value is text and returns TRUE or FALSE. Here, Apple is the value argument, and since it is a text string so the function returns TRUE.
- Output → TRUE
- Secondly, copy the formula to populate the cells below and the results should look like the screenshot below.
Method-8: Using View Side by Side Option to Reconcile Two Sets of Data in Separate Workbooks
So far we’ve shown you how to reconcile your dataset if they are in the same worksheet. What if they are in two separate workbooks? Our next method answers this very question.
Now, allow me to demonstrate this process in the steps below.
📌 Steps:
- To begin with, navigate to the View tab >> click the View Side by Side option.
This opens the Compare Side by Side dialog box.
- Now, from the list, choose the other workbook with which you want to compare the current workbook.
This opens the two workbooks side by side so we can compare and reconcile the differences between them.
For example, we’ve chosen to highlight the first difference between the two datasets as shown in the image below.
Method-9: Reconcile Two Sets of Data in Same Workbook with New Window Option
Our last method considers the scenario where you may need to reconcile two different worksheets. So, let’s have a look at the procedure below.
📌 Steps:
- Firstly, move to the List 1 worksheet >> now go to the View tab >> click the New Window option.
After completing the above step, Excel opens a new instance of the same worksheet as shown in the picture below. In this case, we chose to open the List 2 worksheet in the second instance.
- Secondly, navigate to the View tab >> click the Arrange All option.
Immediately, the Arrange Windows dialog box pops up.
- Thirdly, choose the Vertical option.
Lastly, we can highlight any differences between the two datasets as shown in the screenshot below.
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Conclusion
I hope this article helped you understand how to reconcile two data sets in Excel. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.
