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

## Download Practice Workbook

You can download the practice workbook from here.

## 2 Simple Methods to Sort Two Columns to Match in Excel

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**Sort by**section,**Cell Values**in**Sort on**section and**Small to Largest**in**Order**section**.** - Next, press
**OK**.

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

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

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

**Excel 365**.

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

## 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 match 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 for determining the data match 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.

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

## Further Readings

**How to Create Custom Sort in Excel (Both Creating and Using)****Sort Column by Value in Excel (5 Methods)****Sorting Columns in Excel While Keeping Rows Together****How to Sort Alphabetically in Excel with Multiple Columns (4 Methods)****How to Sort Multiple Columns in Excel****Excel Sort By Date And Time [4 Smart Ways]**

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.