While working in Excel, we often have to work with columns that are almost the same, and sometimes exactly the same. Today I will be showing how you can sort two columns with exactly the same items or nearly the same items to match. In this article, we will show **2** methods. Using these methods, you can easily sort two columns to match in Excel.

**Table of Contents**hide

## How to Sort Two Columns to Match in Excel (2 Simple Methods)

In this section, I will show **2 **simple methods to sort two columns to match in Excel. In the first method, I will show sorting columns having exactly the same items, and in the second method, columns have different items. For demonstration, I have used a dataset that includes **Items Produced in 2021 **and **Items Produced in 2022 **of a company.

### 1. Sort Two Columns to Match with Exactly Same Items

If you look carefully, you will find that the two columns contain the same items, but in a different order. Now we will try to sort the second column to match the first column. We will use** the MATCH function** of Excel here.

- First, take a new column
**Serial,**and type the following formula in**Cell D5**.

`=MATCH(C5,$B$5:$B$14,0)`

- Then, press
**Enter**. - Further, use the
**Fill handle**to copy the formula in the cells below.

**MATCH**function has arguments

**C5**as the matching value,

**$B$5:$B$14**is the range for matching and

**0**denotes exact matching.

- Now, select the range of cells
**C5:D14**.

- Further, select
**Sort & Filter > Custom Sort**in the**HomeÂ**tab.

- Then, in the
**Sort**window choose**1**(value in**Cell D5**) in the**Sort by**section,**Cell Values**in**Sort on**section, and**Small to Largest**in the**Order**section**.** - Next, press
**OK**.

- Finally, we will see the second column sorted according to the first column.

**Read More: **How to Sort Multiple Columns in Excel Independently of Each Other

### 2. Sort Two Columns to Match with Partially Matched Items

Now itâ€™s time to show you the method to sort two columns when the columns donâ€™t contain exactly the same items.

#### 2.1 If First Column Has All Items of Second Column

Suppose the second column has fewer items and the first column has all items of the second column. I will show how to sort them in the second column or in a new column. I will use the **MATCH**, **FILTER**, **COUNTIF**, and **ROWÂ **functions in the procedures.

**2.1.1 Sorting on Same Location**

Letâ€™s discuss the stepwise procedures to sort the second column in the same location.

- First, take a new column
**Serial,**and write the following formula in**Cell D5**.

`=MATCH(C5,$B$5:$B$14,0)`

- Then, press
**Enter**and use the**Fill Handle**to copy the formula up to**Cell D10**.

**MATCH**function has arguments

**C5**as the matching value,

**$B$5:$B$14**is the range for matching and

**0**denotes exact matching.

- Now, write the following formula in
**Cell D11**.

`=FILTER(ROW(A1:A10),COUNTIF(D5:D10,ROW(A1:A10))=0)`

In the formula,

- I have taken
**ROW(A1:A10)**because my first column consists of a total of**10**items (**B5**to**B16**). You use your one. For example, if your first column consists of**50**items, use**ROW(A1:A50)**. **D5:D10**is the range in the new column which was filled before applying this formula.

**Note**

**:**The formula is in array form so press

**Ctrl + Shift + Enter**for other versions of

**Excel**except

**Excel 365**.

- Now, copy the cell values from
**D5:D14**that contain the serial number. - Consecutively, paste it as values.

- Finally, sort
**Column D**and**Column C**following the procedures in**Method 1**. - In this way, we will see the second column sorted according to the first column.

**Read More: **How to Sort Alphabetically in Excel with Multiple Columns

**2.1.2 Sorting on Different Location**

Itâ€™s time to show the procedures for sorting the data in a different location. I will use the **IF **and **COUNTIF **functions in this method. Follow the steps given below.

- First, take a
**New Column**and write the following formula in**Cell D5**.

`=IF(COUNTIF(C5:C110,B5:B14)>0,B5:B14,"")`

- Next, press
**Enter**. - Finally, we can see the second columnâ€™s data sorted according to the first columnâ€™s data in the
**New Column**.

**Note**

**:**we have used an array formula so press

**Ctrl + Shift + Enter**for other versions of

**Excel**.

**Read More: **Sorting Columns in Excel While Keeping Rows Together

#### 2.2 If First Column Doesnâ€™t Have All Items of Second Column

The first column may not have all the items from the second column. I have used **COUNTIF**, **ROW**, **FILTER **and **MATCH **functions for this method. Follow the stepwise procedures given below to sort the columns.

- First, take two additional columns
**New Column**and**Serial**beside the two columns. - Secondly, write the following formula in
**Cell D5**.

`=FILTER(C5:C16,COUNTIF(B5:B16,C5:C16)>0)`

- Then, press
**Enter**for**Excel 365**or press**Ctrl + Shift + Enter**for previous versions as it is an array formula.

- Now, the first empty cell below the array formula result is
**Cell D11**.rite the following formula in**Cell D11**.

`=FILTER(C5:C16,COUNTIF(B5:B16,C5:C16)=0)`

- Further, press
**Enter**for**Excel 365**or press**Ctrl + Shift + Enter**for previous versions as it is an array formula.

- Afterward, type the following formula in
**Cell E5**.

`=FILTER(MATCH(C5:C16,B5:B16,0),COUNTIF(B5:B16,C5:C16)>0)`

- Later on, press
**Enter**for**Excel 365**or press**Ctrl + Shift + Enter**for previous versions as it is an array formula.

- Then, type the formula in
**Cell E11**.

`=FILTER(ROW(A1:A12),COUNTIF(E5:E10,ROW(A1:A12))=0)`

- After that, press
**Enter**for**Excel 365**or press**Ctrl + Shift + Enter**for previous versions as it is an array formula.

- Now, copy the data from
**Column D**and**Column E**and paste only values in the same place. - Finally, sort
**Column D**and**Column E**just like in**Method 1**and we will see the sorted data in**New Column**.

**Read More: **How to Sort Multiple Columns in Excel

## How to Find If Data of Two Columns Match in Excel

If we have two columns of data and want to check whether the data matches or not, we can easily do that in **Excel**. For this purpose, we will use theÂ **IFERROR **and **VLOOKUP **functions. Follow the stepwise procedures given below to determine whether the data matches or not. For demonstration, I have included a dataset having **Item 1 **and **Item 2 **as well as **Status**.

- First, write the following formula in
**Cell D5**.

`=IFERROR(VLOOKUP(C5,$B$5:$B$15,1,0),"Data Doesn't Match")`

- Then, press
**Enter**and use the**Fill Handle**to copy the formula in the cells following. - As a consequence, we will see matched data in the cells, if the data donâ€™t match then
**Data Doesnâ€™t Match**text will appear in the cells.

**IFERROR**and

**VLOOKUP**functions. The

**VLOOKUP**function checks the data from

**Cell C5**and from range

**$B$5:$B$15**and gives that data as output which is taken as an argument for the

**IFERROR**function. If data is not found by the

**VLOOKUP**function then the

**IFERROR**function gives the output

**Data Doesnâ€™t Match**as it is in the argument.

**Download Practice Workbook**

You can download the practice workbook from here.

## Conclusion

Sorting the data of one column with reference to another column is quite a useful tool in **Excel**. Here, I have shown **2 **simple methods to sort two columns to match. If you have any queries, please leave a comment.

It help me so much, I was strugle at 2. Matching Two Columns with Not Exactly the Same Items. Now, I know how to done it, thanks to you, cheers.

Hey,

Thank you for the helpful article!

I have one question to ask to “2. Matching Two columns with Not Exactly the Same Items”:

If i want to sort the data in the second coloumn (items produced in 2020) to match not only data added to the coloumn: items produced in 2019 (as you do here), but also data added to a coloumn more, called: items produced in 2018. and 1) the 2018 coloumn have the same data as the 2019 coloumn have, 2) their data are placed at the same rows and 3) the 2018 coloumn have some empty cells (for instance while 2019 coloumn have a cell with the text “monkey”, the cell beside (which belongs to 2018 coloumn) is empty. So How do i sort the data from the coloumn: items produced in 2020) not only to match one coloumn (items produced in 2019) but to match two coloums?(items produced in 2018 and items produced in 2019)

Thank you in advance.

Anton

Hello Anton,

First of all, you have to create three columns including items produced in 2018. Here, we need to paste the same value as items produced in 2019. Just one or two blanks in there.

Then, take a new cell and write down the following formula.

=IF(COUNTIF(D5:D12,B5:C19)>0,B5:C19,””)

Here, the range becomes (D5:D12 as the column of items produced in 2020 changes. Then, the criterion becomes B5:C19. Here, it follows if the condition match for both cells then it will return the value. Otherwise, it returns blank. After that, press Ctrl+Shift+Enter as it is an array formula.

Here, you can see, that the product Trousers is available in 2019 and 2020. but, we give criteria for products in 2018 and 2019. As they donâ€™t find trousers in 2018, thatâ€™s why you wonâ€™t get them in the result. So, you will get the products that are available in both 2018 and 2019.

Hi,

Using your case 2 example, how would you align the items that do not match with a blank cell on both sides (instead of having them on the same row). E.g. blank – skirt & jacket – blank

Many thanks

Hello,

First of all, download the

Practice Workbookfor better understanding.I think you’ve talked about the following phenomena. Let’s see the following image.

Now, you want to match the items in

Column 2020with the items inColumn 2019.For this, copy the cells in the

B5:B12range.Then, paste them in the

E5:E12range.After that, go to cell

F5and enter the following formula.`=IF(COUNTIF(C5:C12,B5:B12)>0,B5:B12,"")`

As usual, press

ENTER.Note:It’s an array formula. If you are using Microsoft Excel 365 then you can easily run the formula by pressing theENTERkey. Otherwise, you have to tap theCTRL+SHIFT+ENTERkeys simultaneously to make the formula work.Hope that will work for you. Follow our blog

Exceldemyto learn more about Excel.