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