How to Compare Three Columns Using VLOOKUP in Excel: 3 Methods

Method 1 – Inserting VLOOKUP with a Helper Column to Compare Three Columns in Excel

Steps:

=B5&C5&D5

Helper Column

Copy

=VLOOKUP(B17,E5:F12,2,0)

Formula Explanation

The lookup_value is B17.
The table_array is E5:F12. Excel will look for B17 in this array.
The col_index is 2. That means, Excel will return the corresponding department name.

Steps:

=VLOOKUP(B17&C17&D17,$B$4:$E$12,4)

Formula Explanation

The lookup_value is B17&C17&D17.
The table_array is $B$4:$E$12. Excel will look for B17&C17&D17 in this array.
The col_index is 4. That means, Excel will return the corresponding department name.

Steps:

=VLOOKUP(B17,$B$5:$E$12,MATCH(C17,B4:E4,0),0)

Formula Breakdown

MATCH(C17,B4:E4,0) → This indicates the col_index for the VLOOKUP function.
- Output: 2.
VLOOKUP(B17,$B$5:$E$12,MATCH(C17,B4:E4,0),0) → This becomes,
VLOOKUP(B17,$B$5:$E$12,2,0)
- Output: 1000

Steps:

=INDEX(E5:E12,MATCH(1,(B5:B12=B17)*(C5:C12=C17)*(D5:D12=D17),0))

Since this is an array formula, press CTRL + SHIFT + ENTER to get the output.

Formula Breakdown

(B5:B12=B17)*(C5:C12=C17)*(D5:D12=D17) → This is the lookup_array for the MATCH function.
- Output: {0;0;0;0;0;0;0;1}
MATCH(1,(B5:B12=B17)*(C5:C12=C17)*(D5:D12=D17),0) → This is the row_num for the INDEX function.
- Output: 8
INDEX(E5:E12,MATCH(1,(B5:B12=B17)*(C5:C12=C17)*(D5:D12=D17),0)) → This becomes,
INDEX(E5:E12,8)
- Output: Assistance

Notice the curly bracket {} in the formula bar. It indicates an array formula.

Download the Practice Workbook

Three Column Comparison.xlsx

<< Go Back to Columns | Compare | Learn Excel