How to compare two columns in excel using vlookup

Answer

To compare two columns in Excel using VLOOKUP, follow these steps. This method identifies if values in one column exist in another column and highlights matches or missing entries.

Step 1: Organize Your Data

Place your data in two separate columns:

• Column A contains the first list.

• Column B contains the second list.

Example:

A        B

Apple    Apple

Banana   Orange

Cherry   Banana

Date     Grape

Step 2: Write the VLOOKUP Formula

1. Click on the first cell in a new column (e.g., Column C) next to Column A.

2. Enter the following formula:

   =IF(ISNA(VLOOKUP(A2,B:B,1,FALSE)),"Not Found","Found")

Explanation:

1. VLOOKUP(A2,B:B,1,FALSE) searches for the value in cell A2 within Column B.

2. ISNA() checks if the value was not found.

3. IF() returns "Not Found" when the value is missing or "Found" when it exists in Column B.

Step 3: Copy the Formula

Drag the formula down to compare all values in Column A against Column B.

Step 4: Analyze the Results

• "Found" indicates the value exists in both columns.

• "Not Found" indicates the value in Column A does not exist in Column B.

Step 5: Optional – Compare Column B Against Column A

To ensure a two-way comparison, repeat the same steps but reverse the columns:

=IF(ISNA(VLOOKUP(B2,A:A,1,FALSE)),"Not Found","Found")

Tips for Accuracy

1. Ensure there are no extra spaces in cells. Use TRIM() to remove spaces:

   =TRIM(A2)

2. Make the column ranges dynamic if your data size changes often by using named ranges or full column references.

Example Output

This method provides a clear, visual comparison of two columns using VLOOKUP.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report