compare two columns in excel and return a third value

Use Vlookup to Compare Two Columns with Matching Data Pulled Please note that instead of the VLOOKUP, we prefer using the combination of MATCH/INDEX or the Match function as it is more powerful and flexible. You can also do the same by using the MATCH function Whenever you want to get the list of every name that doesn’t have a match, you can have the result column filtered to get all of the cells with TRUE.

The function ISERROR will return FALSE if there is no error and TRUE if the is an error in the VLOOKUP result. The names that will give the #N/A error are those that are unavailable in Column B. It will provide the name from column B or return the #N/A error whenever it is available. With this formula, the VLOOKUP function is used so you can check if the company name in A is also in the B column or not.

Use this VLOOKUP formula if you want to do this: Whenever you want to know if a data point present in a list is in another list, you will have to use the lookup formulas.Īssume that you have this dataset and you want to know which companies are present in the A column but are unavailable in column B. Look for Missing Data Points and Compare the Two Columns Take note that the duplicate rule of Conditional Formatting is not case sensitive.īecause of that, ‘banana’ and ‘Banana’ are considered the same that they’ll get highlighted as duplicates. With these steps, you will get the result that is shown here: Check out the dialog box of Duplicate Values and make sure to select ‘Duplicate.’.Check out the Styles group and then click on the option’ Conditional Formatting.’.Whenever you want to have every matching company name highlighted, you can do this through conditional formatting. Some of the names are present in both lists, but they are not found in the same row. Take note that column A has a list that is bigger compared to column B. However, they may not be present in the same row.

Whenever there are matches, you may often get datasets. Example: Have Matching Data Highlighted and Two Columns Compared Highlighted here is every cell that includes the name that the other list doesn’t have. It will provide you with the result that is shown here. Specify the formatting to have the differences highlighted.Make sure to select ‘Unique’ in the Duplicate Values dialog box.Make the cursor hover on the option Highlight Cell Rules.Check out the Styles group and click on the option’ Conditional Formatting.’.Whenever you want to have the names in one list and not in the other become highlighted, you can have conditional formatting used. Example: Highlight Data that is Mismatched and Have Two Columns Compared Because of that, we will not compare according to rows.

Take note that this may be different whenever you collate rows. Whenever you want to highlight matching information and compare two columns, use conditional formatting’s duplicate functionality. Whenever you think that you can add something to our tutorial, let us know about it in the comments! Highlight Matches with Vlookup to Compare Two Columns Yet, the basic principles should be the same. Take note that this tutorial has techniques in comparing columns that are not the only ones available.Īccording to your dataset, you may have to make adjustments or changes to the method. Make sure that you pass this on to other people who use Excel if you find this a helpful lesson. Since many people have asked us about this, we have decided to provide you with a tutorial that covers a lot of the possible scenarios. You may also want to know about the differences wherein a data point is in a column and not included in the other, etc. The method that you must use would depend on the information structure and what the user would want to get from it.įor instance, you may want to have a couple of columns compared and look for or highlight every matching data point in both columns. Often, people ask how they can use Vlookup to Compare Two Columns in Excel. Ways You Can Use Vlookup to Compare Two Columns for differences and matches

Compare two columns in excel and return a third value