VLOOKUP to Return Multiple Columns in Excel (4 Examples)

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.

VLOOKUP with Array Formula to Return Values from Multiple Columns

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)

VLOOKUP with Array Formula to Return Values from Multiple Columns

After pressing Enter, the function will return all specifications for the selected device at once.

VLOOKUP with Array Formula to Return Values from Multiple Columns


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.

VLOOKUP to Return Multiple Columns from Different Workbook in Excel

And another workbook named Book 2 will represent the output data that has to be extracted from Book 1 for a specified smartphone device.

VLOOKUP to Return Multiple Columns from Different Workbook in Excel

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)

VLOOKUP to Return Multiple Columns from Different Workbook in Excel

After pressing Enter, you’ll get the specifications for the selected device as shown in the screenshot below.

VLOOKUP to Return Multiple Columns from Different Workbook in Excel


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.

VLOOKUP to Sum Return Values from Multiple Columns in Excel

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))

VLOOKUP to Sum Return Values from Multiple Columns in Excel

Now press Enter and you’ll get the total sales amount for the selected salesperson right away.

VLOOKUP to Sum Return Values from Multiple Columns in Excel


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)

VLOOKUP with COLUMN Function to Return Values from Multiple Columns

After pressing Enter and dragging the Fill Handle rightward till the last output cell, we’ll find the following return values.

VLOOKUP with COLUMN Function to Return Values from Multiple Columns


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})

An Alternative to the VLOOKUP to Return Multiple Columns in Excel

After pressing Enter, you’ll be displayed the specifications extracted from multiple columns for the selected smartphone device.

An Alternative to the VLOOKUP to Return Multiple Columns in Excel


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

VLOOKUP Example Between Two Sheets in Excel

How to Use VLOOKUP for Rows in Excel (With Alternatives)

VLOOKUP and Return All Matches in Excel (7 Ways)

VLOOKUP with Numbers in Excel (4 Examples)

XLOOKUP vs VLOOKUP in Excel (Comparative Analysis)

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo