Bringing up data from another SpreadSheet with arrayformula, query and importrange

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

Solution

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)}));{2\3};))))))

I've used vlookup here but the same above lambda approach can be adapted on your original query formula 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&"'")))))