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

**Table of Contents**hide

## 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 theargument which refers to*lookup_value**Apple*. Following,**$B$5:$B$13**represents theargument from where the value is matched. Lately,*lookup_array***0**is the optionalargument which indicates the*match_type***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 theargument, and since it is a number so the function returns*value***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 theargument which checks if the value in the*logical_test***B5**cell is equal to the**E5**cell. If they are equal then the function returns text**TRUE**(argument) otherwise it returns*value_if_true***FALSE**(argument).*value_if_false***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]) →**returns the relative position of an item in an array by default requiring an exact match. Here,**the XMATCH function****Table2[List 2]**is theargument which refers to the*lookup_value***B5:B13**array. Following,**Table1[List 1]**represents theargument from where the value is matched.*lookup_array***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])**(argument) which returns an array.*value***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**(argument) is mapped from the*lookup_value***$B$5:$B$13**(argument) array. Next,*table_array***1**(argument) represents the column number of the lookup value. Lastly,*col_index_num***FALSE**(argument) refers to the*range_lookup***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 theargument, and since it is a text string so the function returns*value***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**(argument) is mapped from the*lookup_value***$B$5:$B$13**(argument) array. Lastly,*lookup_array***$B$5:$B$13**(argument) represents the returned array or range.*return_array***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 theargument, and since it is a text string so the function returns*value***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**.