How to Use VLOOKUP to Return Multiple Columns in Excel?

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.

1. Using VLOOKUP with Array Formula to Return Values from Multiple Columns in Excel

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.

2. Applying 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.

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.

3. Inserting 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 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.

4. Combining 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.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

VLOOKUP to Return Multiple Columns.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!