Introduction

This is one of those things that I freely admit I did not know until last night and now that I know it, I am shocked at myself!

VLOOKUP is very useful and I know how to use it in all sorts of ways. There are other ways to use VLOOKUP that I do not use and it will pay you to go and find them! I am also aware that LOOKUPs, in general, are being superseded by Power Query and the latest Dynamic Array Functions that will be published soon.

Array Constants

Firstly, array constants: they look like this … ={1,2,3;4,5,6} .. to make this work, select a three by two range of cells in a worksheet, type or paste that expression to the top left cell of the range and then press Ctrl+Shift+Enter. You should now see a table like this

123

456

In the VLOOKUP case, we are working through the array constants are embedded in the formula like this:

=VLOOKUP(M9,$A$10:$K$22,{3,7,5,11,9},0) … I am sure you can see the constants, 3,7,5,11,9 …

Read up on Array Constants because they are very useful by themselves, let alone here!

The Problem we are Trying to Solve

With VLOOKUP, we search for a value or some text in a table array and when we find it, the function enters that result in the cell containing the VLOOKUP function.

For example, I want to find the Age of Employee Number 5 from an array and I do that like this:

=VLOOKUP(5,A9:J15,6,0) … that will work and it will give me 26

Imagine now, though that I want to see the age and the salary of employee number 5 … two VLOOKUP functions? Yes, that should work. However, using array constants, we can do everything in one formula, like this, in which age is in column 6 and salary is in column 10 of the table array:

I select the two cells, AH9:AH10 and keeping them selected, I type into the formula bar =VLOOKUP(AG9,A9:J15,{6,10},0) and then ARRAY ENTER the formula as I press Ctrl+Shift+Enter and I get this:

There! one formula but I can grab two values and set them side by side. Again, play with that since it will reap rewards if you have large tables but want to select just a few columns and rows to print or to add to a report. Yes, Power Query can do this for you too but for a trivial example like the ones here, that might be overkill.

Additional Examples

Imagine that I want a report showing First Name, Gender, Age and Salary
Alternatively, I want a report showing Age then Salary and Gender
Now, I want a horizontal report showing Employee Numbers 1,5,9,22,30 by Age then Salary, Gender, First Name and Last Name
Finally, randomise the horizontal report, showing any five employees

Download my file and go to the array_constants tab to see these questions and my solutions. Here they are in outline, however:

Select the cells highlighted in the graphic below, array enter them and there you are. Fill down to the end of your output range and now you have your unique output range using just one VLOOKUP formula.

So far so good. Notice, I just took data from columns 2, 4, 6 and 10 from the table array: that is Age then Salary then Gender. Now, though, I want to take columns out of order, like this following, with 6 then column 10 then column 4 … no problem at all!

Transposing VLOOKUP Output: horizontal not vertical

Let’s look at something a bit more interesting now: same problem, same solution EXCEPT that now I want to show my output data vertically and not horizontally:

Enlarge that graphic if you have to but all we do here is flip the output data by wrapping the TRANSPOSE function around the VLOOKUP function and selecting five rows rather than five columns. By the way, I programme the row headings in column W in a similar way: try that for yourself or look at my solution to see how I did it.

Vertical and Horizontal II

Go to the vlook_arrays_1 tab in my file now and see what I did with some financial data for Netflix:

You can work out quite easily what I did there, I am sure.

Take a look at vlook_arrays_2 where I mix up the columns, otherwise, the same output as vlook_arrays_1:

=VLOOKUP(M9,$A$10:$K$22,{3,7,5,11,9},0)

VLOOKUP with array Constants and SUM as well as SUMPRODUCT

On the vlook_arrays_1 tab, you will find an example I have taken from Mike Girvin’s excellent book on Array Formulas. What Mike has done is to take our VLOOKUP with array constants and wrapped SUM and then SUMPRODUCT around them to help us o find the COGS or one or more products.

Conclusions

I know that Excel is starting to make VLOOKUP functions obsolete but they will be with us for a long time yet. So, enjoy this insight into the use of array constants with your lookups and be prepared, again, to amaze your friends and colleagues!

 

Download the file from here: vlookup_array_constants

 

Duncan Williamson

9th January 2019

 

 

 

%d bloggers like this: