I have created two spreadsheets:
- the first, consisting of two tabs, lists the recorded values (“Values” tab) and the objectives to be reached (“Value 1” and “Value 2” in the “BDD” tab).
- the second will enable me to set up indicators
For the indicators, I’ve imported the desired values (columns A to F). And I’d like to be able to display in columns G and H the targets present in the first file.
So I wrote these formulas (in G2 and H2) :
={"Value 1";arrayformula(SI($A2:$A="";"";QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1tePHlgV-FBTRB9_rqFm6oen3jVwlrnI1woaDhjeV8BE";"BDD!A2:F");"SELECT Col5 WHERE Col1 ='"&$B$2:$B&"' AND Col2='"&$C$2:$C&"' AND Col3 ='"&$D$2:$D&"' AND Col4='"&$E$2:$E&"'")))}
={"Value 1";arrayformula(SI($A2:$A="";"";QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1tePHlgV-FBTRB9_rqFm6oen3jVwlrnI1woaDhjeV8BE";"BDD!A2:F");"SELECT Col6 WHERE Col1 ='"&$B$2:$B&"' AND Col2='"&$C$2:$C&"' AND Col3 ='"&$D$2:$D&"' AND Col4='"&$E$2:$E&"'")))}
However, the results are not what I expected. For the “Male” gender, it works, but not for the “Female” gender. The expected results are 2.6 for Value 1 and 2.9 for Value 2.
What would be the best formula for achieving my objectives? Since the arrayformula, query importrange combination I made doesn’t work, I’m stuck. Thanks for your help
Here’s one approach you may test out:
=vstack({"Value 1""Value 2"};byrow(B2:E;lambda(Σ;if(indirect("A"&row(Σ))="";;index(vlookup(join(;Σ);
byrow(importrange("1tePHlgV-FBTRB9_rqFm6oen3jVwlrnI1woaDhjeV8BE";"BDD!A2:F");lambda(x;{join(;choosecols(x;1;2;3;4))choosecols(x;5;6)}));{23};))))))
- I’ve used
vlookuphere but the same above lambda approach can be adapted on your originalqueryformula as well
Query approach update
=vstack({"Value 1""Value 2"};
map(B2:B;C2:C;D2:D;E2:E;lambda(b;c;d;e;if(indirect("A"&row(b))="";;
query(importrange("1tePHlgV-FBTRB9_rqFm6oen3jVwlrnI1woaDhjeV8BE";"BDD!A2:F");"select Col5,Col6 where Col1 ='"&b&"' AND Col2='"&c&"' AND Col3 ='"&d&"' AND Col4='"&e&"'")))))
2