How to Compare Three Columns in Excel Using VLOOKUP

VLOOKUP is a lookup and reference function to find and fetch data from a range in Excel. VLOOKUP finds and compares values by rows for a single criterion. To use VLOOKUP to compare three columns we add some modifications to the formula. The modifications can be performed through CONCATENATE, INDEX & MATCH functions.

Let’s say, we have a dataset of Employee Details and Departments. That contains several columns regarding employees’ names, addresses, and departments.

dataset

Dataset For Download

4 Easy Methods to Compare Columns in Excel Using VLOOKUP

Method 1: Using VLOOKUP with Helper Column 

VLOOKUP hardly handles multiple columns. In order to use VLOOKUP, we have to insert a helper column combining names and addresses into one to find and fetch the department of an employee.

Step 1: In the dataset, insert a Helper Column (using =B4&C4&D4) combining cell data of First Name, Last Name & Address.

Step 2: Click on cell C16 & Enter =VLOOKUP(B16,E4:F11,2,0).

vlookup formula

Step 3: Press ENTER. You will see the resultant data like in the picture below

vlookup result

Method 2:Using the Concatenate Operator(“&”)

Concatenate Operator (“&”) modifies VLOOKUP to compare multiple columns in order to apply multiple criteria. Normal VLOOKUP can satisfy one condition in a specific column as we mentioned earlier. For our dataset, VLOOKUP can not find the First and Last Name, Address altogether.

However, inserting a CONCATENATE Operator in VLOOKUP enables it to do so.

CONCATENATE VLOOKUP text =VLOOKUP(B16&C16&D16,$B$3:$E$11,4).

Step 1: Click on cell E16 & Enter =VLOOKUP(B16&C16&D16,$B$3:$E$11,4).

concatenate function

Step 2: Press ENTER. The execution results into the image similar to the image below

concatenate function result

Method 3: Using Index and Match

A combination of two functions INDEX and MATCH can overcome the limitations of VLOOKUP.

INDEX function fetches the entries of a cell in a dataset depending on the column and row number. MATCH Function fetches a cell’s position in a row of a reference from a selected range of cells. Thus the combination of these two functions helps VLOOKUP to look up any value that exists horizontally or vertically.

Step 1: Click on the cell E16 & Enter =INDEX(E4:E11,MATCH(1,(B4:B11=B16)*(C4:C11=C16)*(D4:D11=D16),0)).

index & match combination

Step 2: Press CTRL+SHIFT+ENTER altogether. The steps result like the image below

index & match result

Related Content: How to Compare 3 Columns for Matches in Excel (4 Methods)

Method 4: Using VLOOKUP & MATCH Function (Two-Way Lookup)

VLOOKUP and MATCH functions can create a dynamic column index to locate any reference we choose to look for. We change our dataset as Employee’s Salary data and wish to fetch monthly salary as we enter Employee Name & Month.

vlookup & match

Step 1: Click on cell D16 & Enter =VLOOKUP(B16,$B$4:$E$11,MATCH(C16,B3:E3,0),0).

vlookup & match

Step 4: Press ENTER. You will see the resultant data like in the picture below

vlookup & match

Conclusion 

VLOOKUP combined with CONCATENATE Operator, INDEX, and MATCH functions can compare three or multiple columns to find and fetch values from a specific range. For VLOOKUP, we use a helper column, finding an exact match inside a range. CONCATENATE Operator results in the value from the range as default which is False. In the case of INDEX and MATCH functions, the matches are Exact. Hope you find discussed methods easy to follow.Comment, if you need further clarifications or want to add something.


Read more:

Maruf Islam

I, Maruf Islam, an engineer, content writer. I completed my BSc from Bangladesh University Of Engineering & Technology, want to pursue a career in content writing & development.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo