Md. Abdullah Al Murad
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.
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.
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} this 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.
- 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: How to VLOOKUP and Return Multiple Values Vertically in Excel
2. Using 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.
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.
- 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: VLOOKUP to Return Multiple Columns in Excel (4 Examples)
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.
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.
Read More: VLOOKUP from Multiple Columns with Only One Return in Excel
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 name 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.
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: VLOOKUP Formula to Compare Two Columns in Different Excel Sheets
Similar Readings
- VLOOKUP Not Working (8 Reasons & Solutions)
- Excel LOOKUP vs VLOOKUP: With 3 Examples
- How to Use Nested VLOOKUP in Excel (3 Criteria)
- What Is Table Array in Excel VLOOKUP?
- How to Vlookup with Multiple Matches in Excel (with Easy Steps)
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.
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.
Read More: Use VLOOKUP with Multiple Criteria in Excel (6 Methods + Alternatives)
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 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.
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.
Read More: Why VLOOKUP Returns #N/A When Match Exists (with Solutions)
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.
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.
🔎 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.
Read More: INDEX MATCH vs VLOOKUP Function (9 Practical Examples)
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
|
| #VALUE in CHOOSE | If index_num is out of range, CHOOSE will return #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.
Further Readings
- How to VLOOKUP and SUM Across Multiple Sheets in Excel
- VLOOKUP with Numbers in Excel (4 Examples)
- How to Use VLOOKUP Function with INDIRECT Function in Excel
- Use Excel VBA VLOOKUP to Find Values in Another Worksheet
- How to Use VLOOKUP to Search Text in Excel (4 Ideal Examples)
- XLOOKUP vs VLOOKUP in Excel (Comparative Analysis)
- How to Use VLOOKUP with COUNTIF (3 Easy Ways)
