Since it specifies whether we want an exact match or approximate match, we can use TRUE (approximate match) or FALSE (exact match) for the same. Step 5: The last argument is optional which is range_lookup. Since we only have one column selected as table_array, we can use col_index_num value as 1. Step 4: Third and most important argument is to specify the column index from the table_array which can be used to lookup the values. You can use keyboard shortcut F4 to fix the table range. Thus, select range from B2:B9 as a table_array and fix it using dollar signs so that the range will be the same for formula when we copy it and paste across different cells. Which will be the range of data within which we wanted to check the values based on lookup_value. Step 3: Second argument for VLOOKUP function is table_array. separate it with comma (“,”) to specify the next argument. Since we are working with cell C2, it is better to use A2 as lookup value. Since we are trying to lookup whether the values from column Text A are present in column named Text B, we need to specify lookup value from column A. Step 2: The first argument we need to specify is lookup value. Step 1: Initiate the VLOOKUP function in cell C2 by typing “=VLOOKUP(“.
Follow the steps below to get the comparison within these two columns using the VLOOKUP function. In column C, we need to check whether the values in column “Text B” are matching with those in “Text A”.
Compare two columns in excel download#
Is this your first time on Eas圜lick? We’ll be more than happy to welcome you in our online community.You can download this Compare Two Columns using VLOOKUP Excel Template here – Compare Two Columns using VLOOKUP Excel Template Example #1Ĭonsider mixed data as shown below in columns A and B of the excel sheet.
Compare two columns in excel how to#
If you found this tutorial helpful, give us a like and watch other tutorials by Eas圜lick Academy. Learn how to use Excel in a quick and easy way! Try out Data Bars in Excel for clear graphical data representation.How to Highlight Blank Cells in Excel (Conditional Formatting).How to Use the VLOOKUP Function in Excel (Step by Step).Highlighting in the columns will disappear completely and the data table looks exactly as it did at the beginning.ĭon’t miss out a great opportunity to learn:
Go to the Home tab once again, click on ‘Conditional Formatting’ and choose the option ‘Clear Rules’ where you can click on ‘Clear Rules from Entire Sheet’. If you need to remove the highlighting we used for the unique values, it’s pretty simple. To remove the filter completely, go again to the Home tab, click on ‘Sort & Filter’ one more time and unselect the option ‘Filter’.Īnd that’s all it takes! The Filter option has been removed.Īnd before we wrap it up, there’s one more thing worth mentioning. To display all the values unsorted, click on ‘Clear Filter’ and the column will show all values again. You do the same if you need to filter out only the duplicates – simply use the option ‘Sort by Color’ again to have the duplicates displayed all together. The filter option now shows in the header of both columns and we can conveniently sort the data in a column based on the colour.įor example, we can display only the unique values in the first column. Select the whole area (header included), go to the Home tab, click on ‘Sort & Filter’ and choose ‘Filter’. How to Turn on the Filter to Help You Sort the ValuesĪs soon as the unique values have been highlighted, we can turn on the filter to help us sort the values, which might help in further data processing. Confirm, and let’s have a look at other useful tricks that can help you efficiently compare the column contents. Let’s use this option and have the values highlighted in yellow. Of course, we can choose the colour we want to use to highlight the values. So, we want to highlight the values that are present only in one of the columns. Here in this window, we can choose whether we want to highlight duplicates – the values showing in the selected area twice, or we can choose the option ‘Unique’, which means that Excel will highlight the values that occur in both columns only once.Īnd this is exactly what we want because we can quickly find the differences between the columns in terms of the values they contain. Then we go to the Home tab, click on Conditional Formatting and hover over ‘Highlight Cell Rules’.įrom the options that appeared, we select ‘Duplicate Values’. To find differences between the contents of two columns in Excel, we need to select both columns first. 4 How to Remove to Highlighted Data How to Compare Two Columns in Excel to Find Differences
0 kommentar(er)
