Let’s say you have two columns with some textual or numeric values and you need to identify which values are present in both columns and which aren’t. The VLOOKUP function will help you complete this task. We imported a dataset from Google Sheets to Excel using Coupler.io, a
solution for automatic data exports from multiple apps and sources. Read more about Microsoft Excel integrations for data export on a schedule. On the dataset, we have two columns: Old users and New users. What we need is to compare the values from these columns to identify duplicates – values that are present in both columns. We can do this using the VLOOKUP function applied as an array formula. Select an array, which will be not less than
the arrays in your VLOOKUP formula, insert the following formula to the formula bar and press Ctrl+Shift+Enter for Windows (Command+Return for Mac) – this will apply an array formula in Excel: To get rid of It would also be great to exclude empty cells in the array. You can do this using the UNIQUE function, which is available in Excel 365 or Excel Online. Here is how the formula will look: Back to Blog Focus on your business goals while we take care of your data!Try Coupler.io Watch Video – Compare two Columns in Excel for matches and differences The one query that I get a lot is – ‘how to compare two columns in Excel?’. This can be done in many different ways, and the method to use will depend on the data structure and what the user wants from it. For example, you may want to compare two columns and find or highlight all the matching data points (that are in both the columns), or only the differences (where a data point is in one column and not in the other), etc. Since I get asked about this so much, I decided to write this massive tutorial with an intent to cover most (if not all) possible scenarios. If you find this useful, do pass it on to other Excel users.
Note that the techniques to compare columns shown in this tutorial are not the only ones. Based on your dataset, you may need to change or adjust the method. However, the basic principles would remain the same. If you think there is something that can be added to this tutorial, let me know in the comments section Compare Two Columns For Exact Row MatchThis one is the simplest form of comparison. In this case, you need to do a row by row comparison and identify which rows have the same data and which ones does not. Example: Compare Cells in the Same RowBelow is a data set where I need to check whether the name in column A is the same in column B or not.  If there is a match, I need the result as “TRUE”, and if doesn’t match, then I need the result as “FALSE”. The below formula would do this: =A2=B2 Example: Compare Cells in the Same Row (using IF formula)If you want to get a more descriptive result, you can use a simple IF formula to return “Match” when the names are the same and “Mismatch” when the names are different. =IF(A2=B2,"Match","Mismatch") Note: In case you want to make the comparison case sensitive, use the following IF formula: =IF(EXACT(A2,B2),"Match","Mismatch") With the above formula, ‘IBM’ and ‘ibm’ would be considered two different names and the above formula would return ‘Mismatch’. Example: Highlight Rows with Matching DataIf you want to highlight the rows that have matching data (instead of getting the result in a separate column), you can do that by using Conditional Formatting. Here are the steps to do this:
This will highlight all the cells where the names are the same in each row. Compare Two Columns and Highlight MatchesIf you want to compare two columns and highlight matching data, you can use the duplicate functionality in conditional formatting. Note that this is different than what we have seen when comparing each row. In this case, we will not be doing a row by row comparison. Example: Compare Two Columns and Highlight Matching DataOften, you’ll get datasets where there are matches, but these may not be in the same row. Something as shown below: Note that the list in column A is bigger than the one in B. Also some names are there in both the lists, but not in the same row (such as IBM, Adobe, Walmart). If you want to highlight all the matching company names, you can do that using conditional formatting. Here are the steps to do this:
The above steps would give you the result as shown below. Note: Conditional Formatting duplicate rule is not case sensitive. So ‘Apple’ and ‘apple’ are considered the same and would be highlighted as duplicates. Example: Compare Two Columns and Highlight Mismatched DataIn case you want to highlight the names which are present in one list and not the other, you can use the conditional formatting for this too.
This will give you the result as shown below. It highlights all the cells that have a name that is not present on the other list. Compare Two Columns and Find Missing Data PointsIf you want to identify whether a data point from one list is present in the other list, you need to use the lookup formulas. Suppose you have a dataset as shown below and you want to identify companies that are present in column A but not in Column B, To do this, I can use the following VLOOKUP formula. =ISERROR(VLOOKUP(A2,$B$2:$B$10,1,0)) This formula uses the VLOOKUP function to check whether a company name in A is present in column B or not. If it is present, it will return that name from column B, else it will return a #N/A error. These names which return the #N/A error are the ones that are missing in Column B. ISERROR function would return TRUE if there is the VLOOKUP result is an error and FALSE if it isn’t an error. If you want to get a list of all the names where there is no match, you can filter the result column to get all cells with TRUE. You can also use the MATCH function to do the same; =NOT(ISNUMBER(MATCH(A2,$B$2:$B$10,0))) Note: Personally, I prefer using the Match function (or the combination of INDEX/MATCH) instead of VLOOKUP. I find it more flexible and powerful. You can read the difference between Vlookup and Index/Match here. Compare Two Columns and Pull the Matching DataIf you have two datasets and you want to compare items in one list to the other and fetch the matching data point, you need to use the lookup formulas. Example: Pull the Matching Data (Exact)For example, in the below list, I want to fetch the market valuation value for column 2. To do this, I need to look up that value in column 1 and then fetch the corresponding market valuation value. Below is the formula that will do this: =VLOOKUP(D2,$A$2:$B$14,2,0) or =INDEX($A$2:$B$14,MATCH(D2,$A$2:$A$14,0),2) Example: Pull the Matching Data (Partial)In case you get a dataset where there is a minor difference in the names in the two columns, using the above-shown lookup formulas is not going to work. These lookup formulas need an exact match to give the right result. There is an approximate match option in VLOOKUP or MATCH function, but that can’t be used here. Suppose you have the data set as shown below. Note that there are names that are not complete in Column 2 (such as JPMorgan instead of JPMorgan Chase and Exxon instead of ExxonMobil). In such a case, you can use a partial lookup by using wildcard characters. The following formula will give is the right result in this case: =VLOOKUP("*"&D2&"*",$A$2:$B$14,2,0)or =INDEX($A$2:$B$14,MATCH("*"&D2&"*",$A$2:$A$14,0),2)In the above example, the asterisk (*) is a wildcard character that can represent any number of characters. When the lookup value is flanked with it on both sides, any value in Column 1 which contains the lookup value in Column 2 would be considered as a match. For example, *Exxon* would be a match for ExxonMobil (as * can represent any number of characters). You May Also Like the Following Excel Tips & Tutorials:
How do I compare two columns in Excel for matches and differences using VLOOKUP?How to compare two columns in Excel using VLOOKUP. For lookup_value (1st argument), use the topmost cell from List 1.. For table_array (2nd argument), supply the entire List 2.. For col_index_num (3rd argument), use 1 as there is just one column in the array.. For range_lookup (4th argument), set FALSE - exact match.. How do I find the matching data between two columns in Excel?Navigate to the "Home" option and select duplicate values in the toolbar. Next, navigate to Conditional Formatting in Excel Option. A new window will appear on the screen with options to select "Duplicate" and "Unique" values. You can compare the two columns with matching values or unique values.
What is the VLOOKUP formula to compare two columns?= VLOOKUP(A2,C:C,1,0)
This formula will check if the contents of Cell A2 (Thom Yorke) exist in the List of Artists 2. Once we press 'Enter', we can see the result is #N/A, which is Excel's error for letting us know it can't find what we are looking for.
Can you do a VLOOKUP against 2 columns?The VLOOKUP function can be combined with other functions such as the Sum, Max, or Average to calculate values in multiple columns. As this is an array formula, to make it work we simply need to press CTRL+SHIFT+ENTER at the end of the formula.
|
Compare two columns in excel and find matches using vlookup
Copyright © 2024 moicapnhap Inc.
