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 -
=ARRAYFORMULA(IF(LEN(A2:A),VLOOKUP(A2:A,DATA!A2:F,{2,5,6}*SIGN(ROW(A2:A)),FALSE),IFERROR(1/0)))
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
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,)
ReplyDeleteHi 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
ReplyDelete