Today I will talk about MS Excel’s popular and highly used function name VLOOKUP. 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 in multiple columns for various purposes in Excel.
Download the Practice Workbook
For method 1, 3, 4, and alternative.
For method 2 only
4 Ways to Use VLOOKUP Function for Multiple Columns
1. Get Values from Multiple Columns Using VLOOKUP Function
Let’s consider that we have a product list with their ID, Name, and Unit Price. There is another table which will be called Statement. In this table, there will be ID, Name, Price, Quantity, and Total Amount. Our task is to generate an auto calculation of the total price in the Statement table if you just enter the product ID. Our formula will extract product names and prices from the product list table and generate a total amount automatically.
[ Note: To generate Total Amount, I have used =D20*E20 this formula and copied it down up to F31]
Step 1: Enter the formula in C20 and press CTRL + SHIFT + ENTER
=VLOOKUP(B20, $B$4:$D$15, {2,3},0)
Formula Explanation:
 In the VLOOKUP function, the first argument is carrying the data which will be searched in the table. Here B20 is containing the ID which is going to be matched with the product list table ID.
 $B$4:$D$15 is the table array range where the data will be searched.
 {2,3} this means we are extracting second and third column values of the matched rows.
 0 is defining that we want to get an exact match.
 If you want to explore more about this function you can check this link
Step 2: Now copy the formula up to C31
2. VLOOKUP Array on Multiple Columns in Different Workbooks
In this part, we will use the VLOOKUP function’s array formula to get data from multiple columns in different workbooks. Now our dataset will be the same, but the two tables will be in two different workbooks. The name of the workbooks will be Product List Table and Statement Table. We will do the same operation as the previous one but here we will use a different workbook.
Read more: How to Pull Data from Multiple Worksheets in Excel
Product List Table Workbook
Statement Table Workbook
Step 1: Go to the Statement Table workbook and enter the following formula in cell C4 and then press CTRL + SHIFT + ENTER
=VLOOKUP(B4,'[Product List Table.xlsx]Sheet1'!$B$4:$D$15,{2,3},0)
Formula Explanation
 This 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]Sheet1’!$B$4:$D$15 part.
Step 2: Now copy the formula up to C15
3. Compare Multiple Columns Using VLOOKUP Function
For this section, let’s consider we have a dataset of employees with their ID and name. There is a table of Old Employees, New Employees, and Current Expected Employees. Now our task is to compare two columns of Old Employees and New Employees Names and identify the matches. Then, we need to compare the Current Expected Employees Names column with the identified matches.
Read more: How to Compare Two Columns in Excel Using VLOOKUP
Step 1: Select the cells from J5 to J15
Step 1: Enter the following formula in cell J5 and press CTRL + SHIFT + ENTER
=IFERROR(VLOOKUP(IFERROR(VLOOKUP(B5:B15,E5:E15,1,FALSE),""),H5:H15,1,FALSE),"")
Formula Explanation:
 VLOOKUP(B5:B15, E5:E15,1, FALSE) this part compares the first two names.
 H5:H15 the range to look up (third name column) against the matching values returned from the comparison of the first and second name columns.
 1: the column to return the matching values from.
 The IFERROR function is used to replace the #N/A with blank cells. If you want to explore more about this function, you can check this link
4. VLOOKUP with Multiple Criteria – Using the CHOOSE and VLOOKUP Functions
Here we will see how we can extract data from multiple columns using multiple criteria. Let’s consider we have a dataset of sales information with ID, Name, Month, and Sales. Now our task is to create a new table where we will show all the sales in the column where each column will represent each month.
Read more: VLOOKUP with Multiple Criteria in Excel
Step 1: Enter the formula in cell I4 and press CTRL + SHIFT + ENTER. Then copy the formula in the whole table
Alternative Option
In MSExcel there is an alternative of the VLOOKUP function named INDEX or MATCH functions. Now we will do the same operation but with a different function (without VLOOKUP)
Return Values from Multiple Columns Using INDEX/MATCH Function
Now let’s consider the same dataset that we have used in the first example. We will find the price of the product using the Name and ID.
Step 1: Enter the formula in cell D19 and press Enter
=INDEX(D:D,MATCH(1,(C:C=C19)*(B:B=B19),0))
Formula Explanation
 Here D:D is the range from where we will extract data.
 MATCH(1,(C: C=C19)*(B: B=B19) using this we are matching 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.
 If you want to learn more about this INDEX function, you can visit this link
Things to Remember
Common Errors  When they show 

#NA!  This error will occur if the formula is an array formula, and you just press Enter. To solve press CTRL+SHIFT+ENTER. 
#N/A in VLOOKUP  In practice, there are many reasons why you might see this error, including:

#VALUE in CHOOSE  If index_num is out of range, CHOOSE will return #VALUE 
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. 
Conclusion
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. If you have any other method of achieving this, then please feel free to share it with us.
Further Readings:
 VLOOKUP to Return Multiple Columns in Excel (4 Examples)
 How to VLOOKUP from Multiple Columns with Only One Return in Excel (2 Ways)
 VLOOKUP to Return Multiple Values Horizontally in Excel
 VLOOKUP SUM Multiple Rows (4 Ways with Alternative)
 VLOOKUP to Search Text in Excel (4 Easy Ways)
 10 Best Practices with VLOOKUP in Excel