How to compare two columns in Excel using VLOOKUP

This tutorial demonstrates how to use Excel's VLOOKUP function to compare two columns, identifying common values (matches) and missing data (differences). Comparing data across lists is crucial for identifying discrepancies or shared information. The optimal comparison method depends on your specific needs.

Using VLOOKUP to Compare Two Columns

VLOOKUP efficiently identifies data points from one list present in another. The basic VLOOKUP formula requires:

• lookup_value (1st argument): The topmost cell of List 1.
• table_array (2nd argument): The entire List 2.
• col_index_num (3rd argument): 1 if List 2 has only one column.
• range_lookup (4th argument): FALSE for an exact match.

Example: Comparing participant names (List 1 in Column A) with those who qualified (List 2 in Column C). The formula =VLOOKUP(A2, $C$2:$C$9, 1, FALSE) in cell E2 (and dragged down) reveals qualified participants. #N/A errors indicate missing names in List 2. Using absolute references ($C$2:$C$9) keeps the reference constant when copying the formula.

Handling #N/A Errors

To replace #N/A errors with blank cells, combine VLOOKUP with IFNA or IFERROR: =IFNA(VLOOKUP(A2, $C$2:$C$9, 1, FALSE), ""). You can also customize the replacement text, for example: =IFNA(VLOOKUP(A2, $C$2:$C$9, 1, FALSE), "Not in List 2").

Comparing Columns in Different Sheets

To compare columns on different sheets, use external referencing. For example, if List 1 is in Sheet1 Column A and List 2 is in Sheet2 Column A, the formula becomes: =IFNA(VLOOKUP(A2, Sheet2!$A$2:$A$9, 1, FALSE), "").

Finding Common Values (Matches)

The basic IFNA(VLOOKUP(...), "") formula provides a list of common values with blanks for missing data. To obtain a list of only common values, use auto-filter to remove blanks. Alternatively, in Excel 365/2021, use FILTER: =FILTER(A2:A14, IFNA(VLOOKUP(A2:A14, C2:C9, 1, FALSE), "")=""). XLOOKUP offers a simpler solution: =FILTER(A2:A14, XLOOKUP(A2:A14, C2:C9, C2:C9,"")="").

Finding Missing Values (Differences)

To find differences, use IF(ISNA(VLOOKUP(A2, $C$2:$C$9, 1, FALSE)), A2, ""). This returns values from List 1 that are not in List 2. Use auto-filter to remove blanks, or in Excel 365/2021, use FILTER: =FILTER(A2:A14, ISNA(VLOOKUP(A2:A14, C2:C9, 1, FALSE))) or =FILTER(A2:A14, XLOOKUP(A2:A14, C2:C9, C2:C9,"")="").

Identifying Matches and Differences

To label matches and differences, use IF(ISNA(VLOOKUP(A2, $D$2:$D$9, 1, FALSE)), "Not qualified", "Qualified"). This adds labels indicating presence in the second list. The MATCH function offers an alternative: =IF(ISNA(MATCH(A2, $D$2:$D$9, 0)), "Not in List 2", "In List 2").

Returning a Value from a Third Column

To compare two columns and return a value from a third, use =VLOOKUP(A3, $D$3:$E$10, 2, FALSE). IFNA can handle errors: =IFNA(VLOOKUP(A3, $D$3:$E$10, 2, FALSE), ""). INDEX MATCH and XLOOKUP provide more flexible alternatives.

This comprehensive guide provides various methods for comparing columns in Excel using VLOOKUP and other functions, catering to diverse comparison needs. Remember to download the practice workbook for hands-on experience.

The above is the detailed content of How to compare two columns in Excel using VLOOKUP. For more information, please follow other related articles on the PHP Chinese website!