Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Compare Three Columns Using VLOOKUP in Excel

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.


Download Practice Workbook

Download this workbook and practice while going through the article.


3 Simple Methods to Compare Three Columns Using VLOOKUP Function in Excel

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

Dataset


1. Use VLOOKUP Function with Helper Column

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.

Helper Column

  • Then, use Fill Handle to AutoFill up to E12.

VLOOKUP Function

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

Copy

  • After that, write down the following formula in C17.
=VLOOKUP(B17,E5:F12,2,0)
  • Then, press ENTER to get the output.

VLOOKUP Function

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.

Read more: How to Compare 4 Columns in Excel VLOOKUP


2. Apply Ampersand Sign with VLOOKUP Function to Compare Three Columns in Excel

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.

Ampersand

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.

Read More: Compare Three Columns in Excel and Return a Value(4 Ways)


Similar Readings


3. Merge VLOOKUP and MATCH Functions

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 Employee’s Salary data and wish to fetch monthly salary as we enter Employee Name & Month.

Here is the new dataset.

how to compare three columns in excel using vlookup

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.

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

Read more: How to Compare 3 Columns for Matches in Excel (4 Methods)


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.

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.

Read More: Match Two Columns and Output a Third in Excel (3 Quick Methods)


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. Please visit Exceldemy for more useful articles like this.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo