Monday, December 5, 2011

yogi_Use VLOOKUP For Multi-Row Multi-Column LookUp And Populating Cells

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user robjwill said:
ArrayFormula with nested IF statement
I have a date column and columns with people's initials. I keep a table of values for each person by date. I have another table with running dates in descending order to match the values by date and initials, as follows:
Input Sheet
      A       B  C  D  E  F  G  H  I
1            RW SH IJ LL KC JB KH SS
2 11/27/2011 -1  6  5  1  0 -2  0  0
3 11/30/2011 10 10 10 10 10 10 10 10
4 12/2/2011   5  5  5  5  5  5  5  5
Desired Result Sheet
      A       B  C  D  E  F  G  H  I
1            RW SH IJ LL KC JB KH SS
2 12/3/2011
3 12/2/2011   5  5  5  5  5  5  5  5
4 12/1/2011
5 11/30/2011 10 10 10 10 10 10 10 10
6 11/29/2011
7 11/28/2011
8 11/27/2011 -1  6  5  1  0 -2  0  0
9 11/26/2011
I have a very long row of people (cols B to BN) to track this for, so I don't want to use the many vlookup formulas to achieve the result. I would like to use an ArrayFormula with a nested IF statement (or whatever is required).
I tried the following: =arrayformula(if(Desired!$A$2:$A=Input!$A$2:$A,Desired!$B$2:$BN,"")), but it only returns the last data entry ( the row of 5's for the 12/2/2011 row). Everything else if blank. I am new to using ArrayFormulas. What am I doing wrong????
Yogia, help!!!!!

----------------------------------

following is problem solution using a single VLOOKUP formula