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:

  • Create a helper column.
  • Go to E5 and insert the following formula
=B5&C5&D5
  • Press ENTER to get the output.

Helper Column

VLOOKUP Function

  • Copy any cell from the Helper Column and paste it to B17.

Copy

  • Insert the following formula in C17.
=VLOOKUP(B17,E5:F12,2,0)
  • Press ENTER to get the output.

VLOOKUP Function

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.

Method 2 – Applying the Ampersand Sign with Excel VLOOKUP to Compare Three Columns

Steps:

  • Go to E17 and insert the following formula
=VLOOKUP(B17&C17&D17,$B$4:$E$12,4)
  • Press ENTER to get the output.

Ampersand

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.

Method 3 – Using Excel VLOOKUP-MATCH Formula to Compare Three Columns

Steps:

  • Go to D17 and insert the following formula
=VLOOKUP(B17,$B$5:$E$12,MATCH(C17,B4:E4,0),0)
  • Press ENTER to get the output.

MATCH INDEX

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

How to Combine INDEX and MATCH Functions to Compare Three Columns in Excel

Steps:

  • Go to E17 and insert the following formula
=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.

how to compare three columns in excel using vlookup

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


<< Go Back to Columns | Compare | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

2 Comments
  1. Thanks Maruf, very useful information

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo