Friday, 4 April 2014

Using a Vlookup in an array

Vlookup must be the most common formula I use in Excel - In the past I have had to use some VB script to ensure that these formulas fill down to the correct row, while if I want to bring in multiple columns I have had to enter multiple formulas in the appropriate rows.

With Google Sheets, I have found that its possible to use a single formula, which can bring in multiple columns of data in adjacent rows, which also has the advantage of being dynamic - if new data is added, the formula returns the matching data for the new row automatically.

Here is a sample working formula -


The way that this formula works, is that it looks for data in column A, and for each entry, returns matching data from columns B,E and F in the source sheet named "DATA" (B,E,F are 2,5,6 in the above code)

In the example below - the formula has been put in cell B2 and the lookups all complete....

Hope this is of use


  1. OMG, I think I *GET* IT NOW. This is so that you can skip columns living in the array that you don't want sullying your new datadisplay. I still don't get why the length formula or the sign formula live in there. I get some of the other bits (the IF statements and the VLOOKUP,)

  2. Hi Leona - the LEN is there so that in this example for all cells in A2:A downwards, the rest of the function will work - omitting blank cells. The SIGN function is there to encode True/False to 1/0