Two Methods To Extract Multiple Columns With VLOOKUP In Excel

In this tutorial we are going to look at two ways of using VLOOKUP to pull data from a range of multiple columns at one time. One option will utilize an array formula using an array constant.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here we have a data range with various types of fruit and the values of each in 5 different columns:

And below that we have two different areas with Data Validation drop down lists with each fruit type. I want to be able to select a fruit and have the values from the range above populate the cells to the right:

For the first method, we are going to use the following formula:

The only unique thing with formula is the col_num COLUMN(B1). For the formula in the first cell, it will return the number 2 for the second column of the data range, and as we copy it to the right, that will index to 3, 4, 5, and 6 for the other columns.

For the second method, we are going to use the following formula:

There are a few unique things with this formula:

1) First, it’s an array formula, notice the curly brackets around the entire formula. When we started the formula, we actually selected all the cells that the formula would be in, from G21:K21. Then when we entered it, we used Ctrl+Shift+Enter rather than just Enter.

2) Second, notice that we did not have to “lock” either the lookup value or table array, since we are not copying the formula, but we could have – it would work either way.

3) Third, instead of a single value or formula like the COLUMN function we used above, the column number is an array constant {2,3,4,5,6}. These curly brackets are entered manually. This tells the VLOOKUUP formula not to select a single column, but to select all the ones listed and insert the value in the appropriate column.

So with either scenario, as you select a fruit from the drop down list, the correct values will populate:

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , ,

No comments yet.

Leave a Reply