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 almost the same items to match.

**Table of Contents**hide

## Download Practice Workbook

**How to Sort Two Columns in Excel to Match**

### 1. Matching Two Columns with Exactly the Same Items

Look at the set of data below. We have a list of the items produced by a company named APEX Group in the years 2019 and 2020.

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.

Take aÂ new column (must be adjacent to the second column)Â and enter this formula in the first cell.

`=MATCH(C4,$B$4:$B$18,0)`

Then double click on the **Fill Handle**.

- Here,
**C4**is the first cell of my second column,**$B$4:$B$18**is the range of my first column. You use your one accordingly.

Next, select the new column and the second column and go to the **Home>Sort & Filter** option in Excel Toolbar.

Click on **Sort & Filter**. From the drop-down menus, select **Custom Sort**.

You will get the** Custom Sort **dialogue box. Select Sort by **Column D** (The new column), Sort On **Cell Values** and Order **Sort Ascending**.

Then click **OK**. You will get the two columns matched exactly like this.

Now if you wish, you can delete the new column.

**2. Matching Two Columns with Not Exactly the Same Items**

#### Case 2: First Column Having All the Items of the Second Column

Now, look at the new data set. We have again a list of items produced in two years, 2019 and 2020, which are not exactly the same.

But the first column has all the items of the second column.

Now we will try to sort the second column to match the first column.

And when we will not find a match in the second column, we will keep that cell empty.

**Option 1: Sorting in the Original Location**

First, we will try to sort the second column in its original location.

We will use the **MATCH**, **FILTER**, **ROW**, and **COUNTIF** functions of Excel.

Take a new column (must be adjacent to the second column) and enter this formula in the first cell.

`=MATCH(C4,$B$4:$B$18,0)`

Then drag the** Fill Handle.**

- Here,
**C4**is the first cell of my second column, and**$B$4:$B$18**is my first column. You use your ones accordingly.

Then in the first blank cell of the new column, insert this formula:

`=FILTER(ROW(A1:A15),COUNTIF(D4:D11,ROW(A1:A15))=0)`

- Here I have taken
because my first column consists of a total of 15 items (`ROW(A1:A15)`

**B4**to**B18**). You use your one. For example, if your first column consists of 50 items, use.`ROW(A1:A50)`

is the range in the new column which was filled before applying this formula. You use your one.`D4:D11`

Then you have to turn the formulas of the new column into values.

To do that, select the new column and press **Ctrl + C**.

Then right-click on your mouse and choose **Values **from **Paste Option**.

All the formulas in the new column will turn into values.

Now follow the procedure mentioned in section 1. (Select the second column and the new column, Go to **Sort & Filter**, Choose **Custom Sort**, in the **Custom Sort** dialogue box **Sort by** the new column).

After successfully following the above procedure, you will find the two columns matched like this.

Now if you wish, you can delete the new column.

**Option 2: Sorting in a Different Location**

Now we will try to sort the second column to match the first column in a different location.

We will use the **IF **and **COUNTIF** functions of Excel.

To do that select a new column and insert this formula:

`=IF(COUNTIF(C4:C11,B4:B18)>0,B4:B18,"")`

**Array Formula**. So do not forget to enter

**Ctrl + Press + Enter**unless you are in

**Office 365**.]

See, the second column is sorted to match the first column.

Now if you wish, you can copy the first column to the right of it and check whether the two columns have completely matched or not.

**Case 2: First Column Not Having All the Items of the Second Column**

Now finally, look at this data set. We have again a list of items of two different years, 2019 and 2020, and they are not exactly the same.

The first column does not have all the items of the second column.

Now we will try to sort the second column to match the same items in the first column.

We will use the **FILTER**, **COUNTIF**, **MATCH** and **ROW** functions of Excel here.

Take a new column and insert this formula:

`=FILTER(C4:C18,COUNTIF(B4:B18,C4:C18)>0)`

- Here
**C4:C18**is my second column,**B4:B18**is my first column. You use your one accordingly.

Then in the first blank cell of the same column, inserts this formula:

`=FILTER(C4:C18,COUNTIF(B4:B18,C4:C18)=0)`

- Here
**C4:C18**is my second column,**B4:B18**is my first column. You use your one accordingly.

Then in the first cell of the next column, insert this formula:

`=FILTER(MATCH(C4:C18,B4:B18,0),COUNTIF(B4:B18,C4:C18)>0)`

- Here
**C4:C18**is my second column,**B4:B18**is my first column. You use your one accordingly.

Then in the first blank cell of the same column, insert this formula:

`=FILTER(ROW(A1:A15),COUNTIF(F4:F12,ROW(A1:A15))=0)`

- Here, I have used
because each of my columns consists of 15 items,`ROW(A1:A15)`

**B4:B18**and**C4:C18**. You use it according to your needs. For example, if you have a total of 30 items, use**ROW(A1:A30)**. **F4:F12**is the range of the cells of the same column filled by the previous**FILTER**formula. You use it accordingly.

Next, you have to turn the two new columns from formulas to values. To do that follow the procedure mentioned in section 2.1.1

(Select the two new columns, press **Ctrl + C**, then right-click on your mouse and choose **Values** from the **Paste Options**)

Then you have to follow the procedure mentioned in section 1 (Select the two new columns, go to **Sort & Filter**, choose **Custom Sort** and in the **Custom Sort** dialogue box, Sort by Column** F**, the second new column)

After successfully following the above steps, you will find your second column arranged like this.

Then copy the first column and paste it right to the new column. You will see the same items have been matched beautifully.

Now, if you wish, you can delete the extra column for beautification.

**Note:**

You can also use this procedure when the lengths of the two columns are not the same.

For example, see, here I have used the procedure when the first column has items more than the second column.

And here I have used the procedure when the first column has items less than the second column.

**Conclusion**

Using these methods, you can sort two columns to match that have the same items or almost the same items. Do you know any other method? Or do you have any problems? Feel free to ask us.

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