Nehad Ulfat
In Microsoft Excel, there are several formulas available to VLOOKUP and return values from multiple columns. As the VLOOKUP function is not able to extract multiple data itself, we have to make an array formula with the VLOOKUP function. There are also simple alternatives to the array formula to return values from multiple columns and all of these methods will be covered in this article with suitable examples and illustrations.
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
4 Ways to Use VLOOKUP to Return Multiple Columns in Excel
1. VLOOKUP with Array Formula to Return Values from Multiple Columns
In the following picture, there is a dataset lying with different smartphone models and their specifications. The output table at the bottom will represent all specifications for a specified smartphone model. We’ll use the VLOOKUP function to extract data from multiple columns for the specified handset or smartphone device.
Assuming that we want to pull out the specifications for the smartphone device Poco F3. The required array formula with the VLOOKUP function in the output Cell C15 will be:
=VLOOKUP(B15,B5:F12,{2,3,4,5},FALSE)
After pressing Enter, the function will return all specifications for the selected device at once.
Read More: Excel VLOOKUP to Return Multiple Values Vertically
2. VLOOKUP to Return Multiple Columns from Different Workbook in Excel
In this section, we’ll use the VLOOKUP function to extract data from a different worksheet present in another workbook. While applying this method, you have to remember that another workbook must be kept open otherwise the function will return a #N/A error.
Let’s assume, the following primary data table is present in an Excel workbook named Book 1.
And another workbook named Book 2 will represent the output data that has to be extracted from Book 1 for a specified smartphone device.
So, in Book 2, the output Cell C5 will occupy the following formula:
=VLOOKUP(B5,'[Book 1.xlsx]Sheet1'!$B$5:$F$12,{2,3,4,5},FALSE)
After pressing Enter, you’ll get the specifications for the selected device as shown in the screenshot below.
Read More: Use of VBA VLOOKUP to Find Values from Another Worksheet in Excel
Similar Readings
- Excel VLOOKUP to Find Last Value in Column (with Alternatives)
- How to VLOOKUP with Multiple Conditions in Excel (2 Methods)
- VLOOKUP Example Between Two Sheets in Excel
- How to Perform VLOOKUP with Wildcard in Excel (2 Methods)
- VLOOKUP and Return All Matches in Excel (7 Ways)
3. VLOOKUP to Sum Return Values from Multiple Columns in Excel
After returning values from multiple columns with the VLOOKUP function, we can perform any sort of calculation on the output data. The following data table displays the sales data for several random salesmen over successive 5 days. What we’ll do here is look up a salesman in the data table, draw out the corresponding sales over 5 days and then evaluate the total sales in the output Cell C18.
Assuming that we want to know the total sales for Patrick. The required formula comprising of the SUM and VLOOKUP functions in Cell C18 should be:
=SUM(VLOOKUP(C17,B6:G15,{2,3,4,5,6},FALSE))
Now press Enter and you’ll get the total sales amount for the selected salesperson right away.
Read More: How to Vlookup and Sum Across Multiple Sheets in Excel (2 Formulas)
4. VLOOKUP with COLUMN Function to Return Values from Multiple Columns
We have another method available to use the VLOOKUP function to return multiple columns and this time we won’t use the array formula. Rather we’ll use the COLUMN function here to specify the column number for the VLOOKUP function. After that, we have to use the Fill Handle along the row to autofill the rest of the data for the specified smartphone device.
Since we’re going to extract the specifications for Poco F3, the required formula in the output Cell C15 will be:
=VLOOKUP($B$15,$B$5:$F$12,COLUMN(C$4)-1,FALSE)
After pressing Enter and dragging the Fill Handle rightward till the last output cell, we’ll find the following return values.
An Alternative to the VLOOKUP to Return Multiple Columns in Excel
The most suitable alternative to the VLOOKUP function is the combination of the INDEX and the MATCH functions. The INDEX function returns a value or a reference at the intersection of the particular row and column. The MATCH function returns the relative position of an item in an array that matches a specified value in a specified order. In our formula, we’ll insert the MATCH function to specify the row number for the lookup device.
So, the required array formula in the output Cell C15 will be:
=INDEX(B5:F12,MATCH(B15,B5:B12,0),{2,3,4,5})
After pressing Enter, you’ll be displayed the specifications extracted from multiple columns for the selected smartphone device.
Read More: INDEX MATCH vs VLOOKUP Function (9 Examples)
Concluding Words
I hope, all of these methods mentioned above will now help you to apply them in your Excel spreadsheets when you have to look up a value and draw out multiple columns from a data table. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.
You May Also Like to Explore
- How to Use IF ISNA Function with VLOOKUP in Excel (3 Examples)
- Why VLOOKUP Returns #N/A When Match Exists? (5 Causes & Solutions)
- VLOOKUP with Multiple Matches in Excel
- How to VLOOKUP from Multiple Columns with Only One Return in Excel (2 Ways)
- VLOOKUP with Two Lookup Values in Excel (2 Approaches)
- VLOOKUP Formula in Excel with Multiple Sheets (4 Simple Tips)
- VLOOKUP from Another Sheet in Excel
