You can also use the ISNA function or have additional logic with the IF function. The IFNA function detects the #N/A error and replaces the output with an empty space (“”). To do so, combine the VLOOKUP function with the IFNA function like this: It’s good practice to show a different indication that no match was found. However, some Excel users may think that there is some data issue or function error. That is correct and is useful information about missing values. Excel is telling us that the match is not applicable i.e. The formula will find three matches out of the four items in List 1. If you’re using our sample data, put your comparison results in the third column. In case of a matching value, the value will be displayed in the same row of the result column (e.g., Column C).ĭrag the formula down from C2 to copy it over as many cells as you need. Type the following formula: =VLOOKUP(A2, B:B, 1, FALSE)) Select a cell in a new column where you want to display the comparison results (e.g., cell C2). Once data is prepared, you can write the VLOOKUP formula to compare two columns in Excel and identify matches.
#Compare two columns in excel using vlookup how to
How To Use VLOOKUP To Compare Two Columns It’s also a good idea to remove any duplicate values or blank cells to minimize the risk of errors.Ĭlick on Remove Duplicates in the Data Tools section. Matching errors may occur in the final result if the formatting is inconsistent. VLOOKUP is case-sensitive,which means that capitals and lower-case characters matter. It’s important to ensure that the data in both columns are formatted similarly. This is the sample table with header to be added to the section titled “Preparing Your Data for VLOOKUP”: Here is the sample data used in this article: This article uses Column A and Column B for our examples. The default is TRUE.īefore using VLOOKUP to compare two columns in Excel, you need to prepare your data.Ĭreate two separate columns in your worksheet where you want to compare the values. It’s either TRUE (approximate match) or FALSE (exact match). Table_array: the range of cells containing the data you want to search in.Ĭol_index_num: the column number in the table_array you want to return the value from. Lookup_value: the value you want to search for in the first column of the table_array. =VLOOKUP(lookup_value, table_array, col_index_num, ) Here’s a basic syntax of the VLOOKUP function: The function will compare two columns, finds matches between them, and returns the associated values. The term VLOOKUP stands for Vertical Lookup. Thankfully, Excel brings VLOOKUP to the rescue! You can imagine that working manually through lists of thousands of items would be hugely time-consuming. You want to find the items in List 1 that also appear in List 2. Suppose you have a spreadsheet with two lists of items in column A and column B. Five Tips to Improve VLOOKUP Performance.Alternatives to VLOOKUP for Column Comparison.How To Use VLOOKUP To Compare Two Columns.
0 kommentar(er)
