The **VLOOKUP **function is a lookup and reference function to find and fetch data from a range in Excel. It finds and compares values by rows for a single criterion. We add modifications to the formula to use this function to compare three columns. In this article, I will explain how to compare three columns using the **VLOOKUP **function in Excel.

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

## 1. Inserting VLOOKUP with Helper Column to Compare Three Columns in Excel

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

__Steps:__

- First of all, create a helper column.
- Then, go to
**E5**and write down the following formula

`=B5&C5&D5`

- After that, press
**ENTER**to get the output.

- Then, use
**Fill Handle**to**AutoFill**up to**E12**.

- Then, to apply the
**VLOOKUP**function, copy any cell from the Helper Column and paste it to**B17**.

- After that, write down the following formula in
**C17**.

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

- Then, press
**ENTER**to get the output.

**Formula Explanation**

- Here, 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.

## 2. Applying Ampersand Sign with Excel VLOOKUP to Compare Three Columns

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

However, inserting an Ampersand sign in the **VLOOKUP** function enables it to do so.

__Steps:__

- Go to
**E17**and write down the following formula

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

- Then, press
**ENTER**to get the output.

**Formula Explanation**

- Here, 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.

## 3. Using Excel VLOOKUP-MATCH Formula to Compare Three Columns

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

Here is the new dataset.

The steps are given below.

__Steps:__

- Firstly, go to
**D17**and write down the following formula

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

- After that, press
**ENTER**to get the output.

**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

**The INDEX function** fetches the entries of a cell in a dataset depending on the column and row number. The **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 acts as a replacement for the **VLOOKUP **function.

__Steps:__

- Go to
**E17**and write down 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.

**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 Practice Workbook**

Download this workbook and practice while going through the article.

## Conclusion

In this article, I have demonstrated 3 effective methods in Excel to split data into columns by comma. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below. Goodbye!

**<< Go Back to Columns | Compare | Learn Excel**

Thanks Maruf, very useful information

Hello

Mohammad,You are welcome.

Regards

ExcelDemy