How to Use VLOOKUP for Multiple Columns in Excel?

Get FREE Advanced Excel Exercises with Solutions!

Today I will talk about MS Excel’s popular and highly used function named the VLOOKUP function. This function can be used to extract, compare, shift, or search data in any table or range. These functionalities can be used for multiple columns. In this article, I will show how we can use the VLOOKUP function for multiple columns for various purposes in Excel.


Here, I have taken the following dataset for this article. It contains Product Details for some products. I will use this dataset to explain how to use VLOOKUP for multiple columns in Excel.

Dataset for Using VLOOKUP for Multiple Columns in Excel


1. Getting Values from Multiple Columns Using Excel VLOOKUP Function

Let’s consider that you have a Product Details list with their ID, Name, and Unit Price. There is another table which will be called Sales Overview. In this table, there will be ID, Name, Unit Price, Quantity, and Total Sales. Your task is to generate an auto calculation of the Total sales in the table if you just enter the product ID. The formula will extract product names and prices from the Product Details table and generate Total Sales automatically.

Get Values from Multiple Columns Using Excel VLOOKUP Function in Excel

Let’s see how you can do it.

Steps:

  • Firstly, select the cell where you want the product Name. Here, I selected Cell C15.
  • Secondly, in Cell C15 write the following formula.
=VLOOKUP(B15,$B$6:$D$11,{2,3},0)

  • Thirdly, press Enter to get the result.
  • If you are using an older version of Microsoft Excel than Excel 2019 then press Ctrl + Shift + Enter.

🔎 How Does the Formula Work?

  • In the VLOOKUP function, the first argument is carrying the data which will be searched in the table. Here B15 contains the ID which is going to be matched with the product list table ID.
  • $B$6:$D$11 is the table array range where the data will be searched.
  • {2,3} means we are extracting the second and third column values of the matched rows.
  • 0 is defining that we want to get an exact match.
  • After that, drag the Fill Handle down to copy the formula to the other cells.

Dragging Fill Handle to Copy VLOOKUP formula for Multiple Columns in Excel

  • Finally, you can see that I have copied the formula to the other cells and got my desired output.

Note: To generate the Total Sales, I have used the following formula.

=D15*E15

And then copied it down up to F20.

Read More: 10 Best Practices with VLOOKUP in Excel


2. Using the VLOOKUP Function for Multiple Columns from Different Workbooks

In this example, I will use the Excel VLOOKUP function to get data from multiple columns from different workbooks. Now, the dataset is still the same, but the two tables will be in two different workbooks. The Product Details table is in a workbook named Product-List-Table. I will extract the names and prices from this table in this example.

Use VLOOKUP Function for Multiple Columns from Different Workbooks in Excel

Let’s see the steps.

Steps:

  • In the beginning, select the cell where you want the product Name.
  • Then, write the following formula in that selected cell. Here, I have used the name of the Excel workbook I used. You will have to change it accordingly.
=VLOOKUP(B6,'[Product-List-Table.xlsx]Product Details'!$B$5:$D$10,{2,3},0)

  • Next, press Enter to get the result.
  • If you are using an older version of Microsoft Excel than Excel 2019 then press Ctrl + Shift + Enter.

Here, the formula is the same as the previous method’s formula. The only key difference is that here as the source table is in another file that’s why we had to use the reference of the file using ‘[Product-List-Table.xlsx]Product Details’!$B$5:$D$10 part.
  • After that, drag the Fill Handle down to copy the formula.

  • In the following picture, you can see that I have copied the formula to the other cells and got my desired output.

Read More: 7 Practical Examples of VLOOKUP Function in Excel


3. Applying VLOOKUP to Find Values from Multiple Columns in Excel

For this example, I have taken the following dataset. Suppose, you have the Name of some students and their obtained marks in Physics and Chemistry. You have another table that has the names only and you want to show the total marks beside their name. Now, I will show you how you can apply the VLOOKUP function to find values from multiple columns and get the Total Marks from them in Excel.

Apply VLOOKUP to Find Values from Multiple Columns and Get Total in Excel

Let me show you how you can do that.

Steps:

  • Firstly, select the cell where you want the Total Marks. Here, I selected Cell G5.
  • Secondly, in Cell G5 write the following formula.
=SUM(VLOOKUP(F5,$B$5:$D$12,{2,3},FALSE))

  • Thirdly, press Enter to get the Total Marks.
  • If you are using an older version of Microsoft Excel than Excel 2019 then press Ctrl + Shift + Enter.

🔎 How Does the Formula Work?

  • VLOOKUP(F5,$B$5:$D$12,{2,3},FALSE): Here, in the VLOOKUP function, I selected F5 as lookup_value, $B$5:$D$12 as table_array, {2,3} as col_index_num, and FALSE as range_lookup. The formula returns the matches for the lookup_value from columns 2 and 3 of the table_array.
  • SUM(VLOOKUP(F5,$B$5:$D$12,{2,3},FALSE)): Now, the SUM function returns the summation of the two values it got from the VLOOKUP function.
  • Afterward, drag the Fill Handle down to copy the formula to the other cells.

  • Next, you can see that I have copied the formula to all the other cells and got my desired output.


4. Combining Excel VLOOKUP and IFERROR Functions to Compare Multiple Columns

For this section, let’s consider you have a dataset of Tasks and the names of employees who were assigned to that task. There is a column that contains the names of Old Employees who were assigned to that task, the names of New Employees who were assigned to that task, and the names of Current Expected Employees for those tasks. Now your job is to compare the names of the two columns of Old Employees and New Employees and identify the matches. Then, you will need to compare the Current Expected Employees Names column with the identified matches and return that name if it is in all the 3 columns.

Employ VLOOKUP and IFERROR Functions to Compare Multiple Columns in Excel

Let’s see how you can do that.

Steps:

  • First, select the cell where you want the name of the Matched Employee.
  • Then, write the following formula in that selected cell.
=IFERROR(VLOOKUP(IFERROR(VLOOKUP(C6:C11,D6:D11,1,FALSE),""),E6:E11,1,FALSE),"")

  • Finally, press Enter to get the result. If you are using an older version of Microsoft Excel than Excel 2019 then press Ctrl + Shift + Enter.

🔎 How Does the Formula Work?

  • VLOOKUP(C6:C11,D6:D11,1,FALSE): This part of the formula compares the Old Employees column and the New Employees column.
  • IFERROR(VLOOKUP(C6:C11,D6:D11,1,FALSE),””): Now, the IFERROR function replaces the #N/A with an empty string.
  • VLOOKUP(IFERROR(VLOOKUP(C6:C11,D6:D11,1,FALSE),””),E6:E11,1,FALSE): Here, the VLOOKUP function compares the Current Expected Employees column against the matching values returned from the first VLOOKUP function.
  • IFERROR(VLOOKUP(IFERROR(VLOOKUP(C6:C11,D6:D11,1,FALSE),””),E6:E11,1,FALSE),””): Finally, the IFERROR function replaces the #N/A with an empty string.

Read More: How to Make VLOOKUP Case Sensitive in Excel


5. Joining Excel CHOOSE and VLOOKUP Functions for Multiple Criteria

Here, I will show you how you can extract data from multiple columns using multiple criteria. Let’s consider we have a dataset of sales information with Sales Person, Month, and Sales. Now your task is to create a new table where you will show all the sales in the column where each column will represent each month.

Combine CHOOSE and VLOOKUP Functions for Multiple Criteria in Excel

Let’s see the steps.

Steps:

  • To begin with, select the cell where you want the Sales for a month. Here, I selected Cell G6.
  • Next, in Cell G6 write the following formula.
=VLOOKUP($F6&G$5,CHOOSE({1,2},$B$5:$B$12&$C$5:$C$12,$D$5:$D$12),2,0)

  • Afterward, press Enter to get the result.
  • If you are using an older version of Microsoft Excel than Excel 2019 then press Ctrl + Shift + Enter.

🔎 How Does the Formula Work?

  • CHOOSE({1,2},$B$5:$B$12&$C$5:$C$12,$D$5:$D$12): Here, in the CHOOSE function, I selected {1,2} as index_num, $B$5:$B$12&$C$5:$C$12 as value1, and $D$5:$D$12 as value2. This formula returns the value using the index_num.
  • VLOOKUP($F6&G$5,CHOOSE({1,2},$B$5:$B$12&$C$5:$C$12,$D$5:$D$12),2,0): Now, the VLOOKUP function finds the match and returns value accordingly.
  • Next, drag the Fill Handle down to copy the formula.

  • Then, drag the Fill Handle right.

  • Finally, you can see that I have copied the formula to all other cells and got my desired output.


6. Merging VLOOKUP and MATCH Functions to Lookup Value Dynamically

In this example, I will show you how you can find value dynamically from multiple columns using the VLOOKUP function in Excel. I have taken the following dataset for this example. It contains the Student ID, Name, and Marks. In another table, I have the Student ID. Now, I will use the VLOOKUP function to find value against this Student ID dynamically.

Apply VLOOKUP and MATCH Functions to Lookup Value Dynamically from Multiple Columns in Excel

Let’s see how to do it.

Steps:

  • Firstly, select the cell where you want the Marks.
  • Secondly, write the following formula in that selected cell.
=VLOOKUP(F5,$B$4:$D$12,MATCH($G$4,$B$4:$D$4,0),FALSE)

  • Thirdly, press Enter to get the result.

🔎 How Does the Formula Work?

  • MATCH($G$4,$B$4:$D$4,0): Here, in the MATCH function, I selected $G$4 as lookup_value, $B$4:$D$4 as lookup_array, and 0 as match_type. The formula will return the relative position of the lookup_value in the lookup_array.
  • VLOOKUP(F5,$B$4:$D$12,MATCH($G$4,$B$4:$D$4,0),FALSE): Now, the VLOOKUP function returns the match.
  • After that, drag the Fill Handle down to copy the formula.

  • Finally, you can see that I have copied the formula to the other cells and got my desired output.


Alternative to VLOOKUP Function for Multiple Columns in Excel

In this section, I will do the same operation but with different functions (without VLOOKUP). Here, I will use the INDEX function and the MATCH function. Now, let’s consider the same dataset that you used in the first example. I will find the Unit Price of the product using the Name and ID.

Alternative to VLOOKUP Function for Multiple Columns in Excel

Let’s see the steps.

Steps:

  • In the beginning, select the cell where you want the Unit Price.
  • Next, write the following formula in that selected cell.
=INDEX(D:D,MATCH(1,(C:C=C15)*(B:B=B15),0))

  • Thirdly, press Enter to get the result.
  • If you are using an older version of Microsoft Excel than Excel 2019 then press Ctrl + Shift + Enter.

Using INDEX and MATCH Functions for Multiple Columns instead of VLOOKUP in Excel

🔎 How Does the Formula Work?

  • MATCH(1,(C:C=C15)*(B:B=B15),0): This part of the formula matches the entered ID and Name with the dataset, and the “1” here refers to TRUE as in return the row number where all the criteria are TRUE.
  • INDEX(D:D,MATCH(1,(C:C=C15)*(B:B=B15),0)): Now, the INDEX function returns the value from within the range D:D.
  • Then, drag the Fill Handle down to copy the formula to the other cells.

  • In the end, you can see that I have copied the formula to all the other cells and got my desired output.

Related Content: How to Use Dynamic VLOOKUP in Excel


Common Errors While Using These Functions

Common Errors

When They show

#N/A Error This error will occur if the formula is an array formula, and you just press Enter. To solve it press CTRL + SHIFT + ENTER.
#N/A in VLOOKUP In practice, there are many reasons why you might see this error, including
  • The lookup value does not exist in the table.
  • The lookup value is misspelled or contains extra space.
  • The table range is not entered correctly.
  • You are copying VLOOKUP, and the table reference is not locked.
#VALUE in CHOOSE If index_num is out of range, CHOOSE will return a #VALUE error.
Range or an array constant CHOOSE function will not retrieve values from a range or array constant.
#VALUE in INDEX All ranges must be on one sheet otherwise INDEX returns a #VALUE error.

Things to Remember

  • If you are using an older version of Microsoft Excel than Excel 2019 then you will have to press Ctrl + Shift + Enter for array formulas.

Download Practice Workbook

You can download the practice workbook from here.


Conclusion

So, these are some ways to use the VLOOKUP function for multiple columns in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. Furthermore, the practice workbook is also added at the beginning of the article. Download it to exercise the examples. If you have any other method of achieving this, then please feel free to share it with us.


Related Articles


<< Go Back to Advanced VLOOKUPExcel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo