The IFNA function replaces errors with empty strings and serves the results to the FILTER function, which filters out blanks ("") and outputs an array of matches as the final result.Īlternatively, you can use the ISNA function to check the result of VLOOKUP and filter the items evaluating to FALSE, i.e. The function compares each of the lookup values against List 2 (C2:C9) and returns an array of matches and #N/A errors representing missing values. Please pay attention that in this case we supply the entire List 1 (A2:A14) to the lookup_value argument of VLOOKUP. For this, utilize the IFNA VLOOKUP formula as the criteria for FILTER: In Excel for Microsoft 365 and Excel 2021 that support dynamic arrays, you can make use of the FILTER function to sift out blanks dynamically. To get a list of common values without gaps, just add auto-filter to the resulting column and filter out blanks. The result of that formula is a list of values that exist in both columns and blank cells in place of the values not available in the second column. In the previous examples, we discussed a VLOOKUP formula in its simplest form:
=IFNA(VLOOKUP(A2, Sheet2!$A$2:$A$9, 1, FALSE), "")Ĭompare two columns and return common values (matches) The best practice is to start typing the formula in your main sheet, then switch to the other worksheet and select the list using the mouse - an appropriate range reference will be added to the formula automatically.Īssuming List 1 is in column A on Sheet1 and list 2 is in column A on Sheet2, you can compare two columns and find matches using this formula: To search in another worksheet or workbook, you have to use external reference. In real life, the columns you need to compare are not always on the same sheet. Compare two columns in different Excel sheets using VLOOKUP Depending on your particular task, it can be modified as shown in further examples. That is the basic VLOOKUP formula to compare two columns in Excel. You can also return your custom text such as "Not in List 2", "Not present", or "Not available". Our improved formula returns an empty string ("") instead of #N/A. To replace errors with blank cells, use VLOOKUP in combination with the IFNA or IFERROR function in this way: However, it delivers a bunch of #N/A errors, which may confuse inexperienced users making them think that something is wrong with the formula. The VLOOKUP formula discussed above perfectly fulfills its main objective - returns common values and identifies missing data points. For the remaining participants, a #N/A error appears indicating that their names are not available in List 2. Please notice that table_array is locked with absolute references ($C$2:$C$9) so that it remains constant when you copy the formula to the below cells.Īs you can see, the names of the qualified athletes show up in column E. The formula goes to cell E2, and then you drag it down through as many cells as there are items in List 1. You want to compare these 2 lists to determine which participants from Group A made their way to the main event.
Suppose you have the names of participants in column A (List 1) and the names of those who have passed through the qualification rounds in column B (List 2).
HOW TO USE VLOOKUP IN EXCEL 2013 TO COMPARE TWO COLUMNS HOW TO
How to compare two columns in Excel using VLOOKUP Identify matches and differences between two columns.Compare two columns to find differences (missing data).Compare two columns to find matches (common values).Compare two columns in different sheets.Comparison can be done in many different ways - which method to use depends on exactly what you want from it. When you have data in two different lists, you may often need to compare them to see what information is missing in one of the lists or what data is present in both. The tutorial shows how to use VLOOKUP formula in Excel to compare two columns to return common values (matches) or find missing data (differences).
0 kommentar(er)
