Excel compare two lists and return differences in another column

You can compare data in a column and match the content to a different column. Different Excel formula can be used to compare two columns, find the matches in the column and return a value. In this article, I will show you different methods you can use to compare data from different columns. Create the table below. Copy the formula to the rest of the cells using Autofill feature or drag the fill handle down to cells you want to copy the formula. Enter the following formula on cell E2. Autofill the formula to the rest of the cells.

In the above formulas, cell D2 is the criteria cell that returns value based on column cells A2:A20 which is to be compared with and return the matching value from data range A2:B You can pull the matching content from one column to another using the Index and match formulas only. Using the same example, compare the projects in column A and D and if a match is found, return the corresponding name in column B. Use the fill handle to drag the formula to the rest of the cells you want to match.

You can download a free trial Kutools add-in and use it to compare two columns and return the value in a third column.

Once you install the add-in to you excel workbook, put your cursor on the cell where the value is to be returned. Click on Kutools, select formulas tab then click Look for a value in the list. A formula helper window opens which allows you to enter the table array, Lookup value and the corresponding column with the return values. Enter the table references and click OK. Spread the love. Like this: Like Loading Tags: Compare 2 columns and get valueexcel compare 2 columns.

Close Menu.While doing different tasks in Excel we often come across a situation where the matching and differences of two or multiple columns are required. In this article, we will look for different techniques of matching and differentiating columns in Excel.

First of all, we will be using a table of 2 columns containing different colors name. Using conditional formatting is a way of finding the differences between two columns in Excel. The procedure is given below. IF function plays a significant role in finding both differences and matches in excel. Here we will be using it to find the matches in two columns. In the cell C2, there is no match so nothing will appear if we drag down the cell where the two rows match there we will found our result.

Again, conditional formatting can be used for finding the matches between two columns in Excel. To show the differences between two column the easiest option is using the Exact option. It has to be remembered that it can be used for only two columns and this function is case sensitive. The procedure of using Exact function is given below. Note that this function is case sensitive. If it was not case sensitive we would have got True in C9.

Here one thing is to be noted that we are doing the analysis of cells containing text. If we use a number instead of text, we will see the result Do Not Match in the column D. Previously we discussed how the differences and matches are found using IF function.

The formula we will be using is. By dragging the cell downwards where the formula is applied we can find the matching and differences between the two columns.

By using conditional formatting, it can be easily done. Only the formula will be changed.

Diagram of a clock diagram base website a clock

A3 to find out the matches. Just like 2 columns finding differences in multiple columns is also easy. While doing different data analysis we face difficulties of finding differences and matches in columns in Excel. As you can see there are a lot of ways in Excel to find them. Based on the task you are doing you can use any of these. This Blog may help you to overcome the problems that you face while working on multiple columns. Welcome to my Profile.

Excel formula to compare two columns and return a value (5 examples)

Here I will be posting articles related to Microsoft Excel. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and attitude to grow continuously.Comparing columns in Excel is something that we all do once in a while. Microsoft Excel offers a number of options to compare and match data, but most of them focus on searching in one column.

In this tutorial, we will explore several techniques to compare two columns in Excel and find matches and differences between them. When you do data analysis in Excel, one of the most frequent tasks is comparing data in each individual row.

This task can be done by using the IF functionas demonstrated in the following examples. To compare two columns in Excel row-by-row, write a usual IF formula that compares the first two cells. Enter the formula in some other column in the same row, and then copy it down to other cells by dragging the fill handle a small square in the bottom-right corner of the selected cell.

As you do this, the cursor changes to the plus sign:. To find cells within the same row having the same content, A2 and B2 in this example, the formula is as follows:. And of course, nothing prevents you from finding both matches and differences with a single formula:. The result may look similar to this:. As you see, the formula handles numbersdatestimes and text strings equally well. As you have probably noticed, the formulas from the previous example ignore case when comparing text values, as in row 10 in the screenshot above.

Compare Two Lists to Highlight Matched Records

If you want to find case-sensitive matches between 2 columns in each row, then use the EXACT function:. To find case-sensitive differences in the same row, enter the corresponding text "Unique" in this example in the 3 rd argument of the IF function, e. If your table has three or more columns and you want to find rows that have the same values in all cells, an IF formula with an AND statement will work a treat:. If you are looking for a way to compare columns for any two or more cells with the same values within the same row, use an IF formula with an OR statement:.

In case there are many columns to compare, your OR statement may grow too big in size. If the count is 0, the formula returns "Unique", "Match" otherwise. For example:. Suppose you have 2 lists of data in Excel, and you want to find all values numbers, dates or text strings which are in column A but not in column B. If no match is found, the formula returns "No match in B", an empty string otherwise:.

If you want a single formula to identify both matches duplicates and differences unique valuesput some text for matches in the empty double quotes "" in any of the above formulas. Sometimes you may need not only match two columns in two different tables, but also pull matching entries from the second table. For example, the following formula compares the product names in columns D and A and if a match is found, a corresponding sales figure is pulled from column B.

If you don't feel very comfortable with this formula, then you may want to try the Merge Tables wizard - a fast and intuitive solution that can compare and match 2 tables by any column s.

excel compare two lists and return differences in another column

When you compare columns in Excel, you may want to "visualize" the items that are present in one column but missing in the other. You can shade such cells in any color of your choosing by using the Excel Conditional Formatting feature and the following examples demonstrate the detailed steps. To compare two columns and Excel and highlight cells in column A that have identical entries in column B in the same row, do the following:.

Solly moholo songs

If you are new to Excel conditional formatting, please see How to create a formula-based conditional formatting rule for step-by-step instructions. You create the conditional formatting rules with the following formulas:. And get the following result:. If you closely followed the previous exampleyou won't have difficulties adjusting the COUNTIF formulas so that they find the matches rather than differences.Compare two columns and list differences with Kutools for Excel.

Click to download and free trial Kutools for Excel now! Kutools for Excel : with more than handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now! To list only the unique values from the two lists separately, you can apply the following formulas:. Then drag the fill handle down to the cells that you want to apply this formula, and all the unique names which are only in Column A have been listed at once, see screenshot:.

Then select the cell E2, and drag the fill handle over to the cells that you want to contain this formula, and then all names only in Column B have been extracted as follows:. After installing Kutools for Excelplease do as follows:.

A : Click button under the Find values in: Range A section to select the names in Column A that you want to extract the different names, and click button under the According to: Range B section to select the names in Column B that you want to be compared. B : Select Each row under the Based on section. C : Then choose Different Values from the Find section.

D : If you want to shade the different names, you can check the Fill backcolor or Fill forecolor under the Processing of results. Then click OK button, all the names only in Column A have been selected, then you can copy and paste them into any other cells you need, see screenshots:. To list the names only in Column B, you just need to exchange the Find values in cells and According to cells in the dialog box, see screenshot:.

And all the different names only in Column B have been selected, then copy and paste them into any other cells you need, see screenshots:. Click to know more details about this Compare Ranges feature. Download and free trial Kutools for Excel Now! Log in. Remember Me Forgot your password? Forgot your username? Password Reset.

Please enter the email address for your account. A verification code will be sent to you. Once you have received the verification code, you will be able to choose a new password for your account.This is an interesting situation that often comes up. Namely, sometimes one needs to differentiate data in two different columns. There are so many processes in which Excel compare two lists and return differences. In this article, we will see the ways on how to compare two columns in Excel for differences.

Here we have two lists where some fruits name is placed. We will compare the two lists for finding the differences. The two lists containing the fruits name is given below.

We will see 4 different processes of finding the differences between two columns. In every process, we will use the same table. We can use the conditional formatting to highlight the unique values of two columns. The procedure is simple and given below. The procedure is given below. We will compare List-1 with List The formula will calculate the two lists and will return the fruits name which is only in List In this procedure, if List-1 contains any fruits name which is not placed in List-2, the formula that we will be using will say that the fruit name from List-1 is not found in List So, we can see the different processes of finding the differences between two columns.

The comparison between two columns can be obtained for matches also.

excel compare two lists and return differences in another column

Out of the 4 procedures we discussed using the conditional formatting is the best way to compare between two columns. Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and attitude to grow continuously.

Xtv apk password

Continuous improvement and life-long learning is my motto. Thanks for notifying me. It was a technical mistake.

Htft cima strategic case study

We provide tips, how to guide and also provide Excel solutions to your business problems. I earn a small commission if you buy any products using my affiliate links to Amazon.In this article, I will show you several techniques of using Excel formula to compare two columns and return a value.

Your necessity might be different, so I will show several scenarios in my article.

excel compare two lists and return differences in another column

In the following spreadsheet, you are seeing a list of some Projects and their Managers. In cell D2the Project coordinator might input a Project name and want to see who the Manager of the Project is.

Cut avi files

Take a look at the following image. If you observe the image carefully, you will find that the formula is actually self-explanatory. I hope you got how this formula works. Always try to break down a complex formula into pieces to make it easy.

excel compare two lists and return differences in another column

You want to conduct a lookup on the table to match two different values from two columns. If matching is found, it will return data from the 3 rd column. You cannot perform two columns lookup with regular Excel formulas. You have to use an Array formula.

You will get the formula is input as an array formula. The rest is simple. In the array of C2:C117 th position is value So, the overall function returns in cell F4. If you fail to understand how this array formula works: check out my link. This is another scenario. You are seeing a worksheet below. There are two lists.

What I want are:. And then copy it for other cells in the column. We get the following results as shown in the figure below. When you analyze a complex formula, start with the function that does not have any other functions in it. This is a straightforward Excel IF function. This comparison is case-insensitive. They are not same. So, these are my ways of comparing two columns in Excel and return a value based on the comparison. Do you know any other better techniques?

Let me know in the comment box. I am eager to update this article.

Match Two Columns in Excel and Return a Third (3 Ways)

Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Stay tuned! First of all, sorry for my bad english, than i want to congrats you for this lection and you have made in me one of the fan of your blog.

Thanks for your feedback.Sometimes while working with multiple columns you often need to match your two columns to return the third value. In this article, we will see how to match two columns in Excel and return a third. Here in this article, we are going to use some of these.

How to Compare Two Columns or Lists in Excel

Here we will be comparing two columns where there exist some same values. If the two values get matched then it will return third column values where the values will be corresponding results of the 1 st column. We create another column with a heading of Product ID Here we will use a formula by which we will get the corresponding price value of different product IDs. Now, drag down the formulated cell E2 below from E2 to E6 to find out the result for the entire column.

The previous problem can be solved with a different formula. IT will give us the same output like the previous method. Now like before drag down this formulated cell in downwards to insert the formula for the whole column. The return value will be from the third column and it will also be in the same row of the 2 values that will be given. The below image shows the information of the example that we will be working on.

You will see a pair of 2nd brackets appear in the formula which contains the formula inside it. After doing this you will get to see the below result. From this, the resultant value is which is in the same row of these two values. By changing the cell values of cell F2 and F3 we will get different results in cell F4.

Arduino accelerometer measure vibration

Comparison between columns to find matches and resulting a value from a different column is a common practice in Excel. By knowing the solutions to this kind of problems makes your work easier in many cases. Hope you will like this article. Stay tuned for more useful articles.

Welcome to my Profile. Here I will be posting articles related to Microsoft Excel. I am a diligent, goal-oriented engineer with an immense thirst for knowledge and attitude to grow continuously. Continuous improvement and life-long learning is my motto. Related Articles Excel formula to calculate hours worked and overtime [with template] Excel Formula to Add Percentage Markup [with Calculator] How to find the 5 most frequent numbers in Excel 3 Formulas How to find text in an Excel range and return cell reference 3 ways How to reference cell in another Excel sheet based on cell value!

We provide tips, how to guide and also provide Excel solutions to your business problems.


thoughts on “Excel compare two lists and return differences in another column

Leave a Reply

Your email address will not be published. Required fields are marked *