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

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

how to sort two columns in excel to match


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.

Sort Two Columns to Match with Exactly Same Items

In the formula, the 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.

Sort Two Columns to Match with Partially Matched Items

In the formula, the 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.

how to sort two columns in excel to match result

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.

Sorting on Different Locations

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.

If First Column Doesn’t Have All Items of Second Column

  • 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 sort two columns in excel to match result


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.

How to Find If Data of Two Columns Match in Excel

In the formula, we have used  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.


Further Readings


<< Go Back to Sort Columns in Excel | Sort in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

5 Comments
  1. 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.

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

  3. 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.
    null

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

  4. 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 Workbook for 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 2020 with the items in Column 2019.
      For this, copy the cells in the B5:B12 range.
      Then, paste them in the E5:E12 range.

      After that, go to cell F5 and 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 the ENTER key. Otherwise, you have to tap the CTRL+SHIFT+ENTER keys simultaneously to make the formula work.
      Hope that will work for you. Follow our blog Exceldemy to learn more about Excel.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo