Vlookup returns only 1 value, right? Well…normally yes but there are some tricks to return multiple values (numbers) and add them up.

I was reminded of this as I was reading Guerrilla Data Analysis Using Microsoft Excel Edition 2 by Oz du Soleil and Bill Jelen. It’s called an array constant and I explain below. Before jumping into the details, if you’re interested in improving your Excel skills then pick up this book and work through all of the examples! When you finish you’ll have a lot more Excel knowledge!

Here is an excerpt of their book provided by ExcelTV

So, what is an array constant?

Let’s look at an example:  VLOOKUP(J2,Table1,2,0)

In this vlookup the ‘Col Index Num’ (Column Index Number) argument is set to 2. This indicates that after the vlookup finds the look value (value in cell J2) it will return a value 1 column to the right. This is nothing new to most of you.

Here’s the fun part! We could change the 2 to be {3,6,9}

Here is the modified Vlookup: =SUM(VLOOKUP(J2,Table1,{3,6,9},0))

Now, we are returning the 3rd, 6th, and 9th values at the same time. We need to wrap the SUM function around the vlookup in order to sum the 3 numbers. We also need to hold down the ‘Ctrl’ and ‘Shift’ keys and then press the ‘Enter’ key as this is an array formula.

Convert the static {3,6,9} to dynamic column references

What if you want to select the columns to return instead of always returning the 3rd, 6th, and 9th columns?

We can do this by changing {3,6,9} to this MATCH(Table2[Data],Table1[[#Headers],[Dept]:[Data 8]],0)

I use the MATCH function to lookup multiple values (listed in a table called “Table2[Data]”) in the column header of Table1.

At this point it might be easier to play around with my Excel file and watch my YouTube video.

Download my Excel file

Download here or via my OneDrive (file 00137)

Watch my YouTube video

Me With Fenton & Cali

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.

I’ve enrolled in Power Query Academy! Why not learn Power Query (Get & Transform) with me?

I’ve been a Data Analyst since 2001. Check out my YouTube videos and my blog posts.