Saturday, January 14, 2012

yogi_Extract Values From A Set of Specified Columns That Match Values In Another Column

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
user alx77 said:
First column of array is truncated, anyone knows why? 
I'm trying to use a group of columns as an array to pass to filter, as in:
=FILTER({A2:A, C2:D, F2:F};B2:B=...)
This gives error: Mismatched range lengths. I think the problem is in the way I construct the array.
I tried to make an array using  
={{1;2;3},{4;5;6},{7;8;9}}
The result is
1 4 7
5 8
6 9
The first column is always trimmed. Is there another way to do this?
--------------------------------
I am going to assume that the user want to extract from the set of values in columns A,C, and F those values that match the ones in column B.
Following is a solution to the problem: