Friday, June 8, 2012

Yogi_Query A Range With Query Criteria Also Being A Range

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #584    Jun 08, 2012     www.energyefficientbuild.com.


user VP4711 said:
Query a range with query criteria is also a range
My spreadsheet looks like this
         A               B                 C           D
1     Car1        BMW             Car1      =query(?????)
2     Car2        Mercedes       Car3
3     Car3        Porsche         Car7
4     Car4        Hyundai
5     Car5        Honda
6     Car6        Ford
7     Car7        Ferrari
 I do not want to use vlookup in each column D1, D2, D3. I want to use Query function to show the list in D1:D3 basis criteria in C1:C3
Formula might be
Query (A1:C7; "Select B where A = C1:C3")
 and desired result is
        A         B               C          D
1     Car1        BMW             Car1       BMW
2     Car2        Mercedes        Car3       Porsche
3     Car3        Porsche         Car7       Ferrari
4     Car4        Hyundai
5     Car5        Honda
6     Car6        Ford
7     Car7        Ferrari
 Please tell me what am I doing wrong?
-----------------------------------------------------------------------------------------
you can use contain in QUERY Select as illustrated in
following solution to the problem