VLOOKUP + QUERY + IMPORTRANGE > import all lines with condition true

Question

Following this question IMPORTRANGE + extra column does not behave as table, I read about the VLOOKUP+QUERY+IMPORTRANGE other questions and decided it was a good way to use an index to store lines without breaking the logic of the other sheets.

I am using a combined VLOOKUP + IMPORTRANGE to copy original data to a viewer sheet. I have the lines imported correctly, matching their index, but I needed the condition that only checked lines in the original data sheet should be imported to the viewer SS. The Viewer SS then presents a copy of the datas and will also use checkboxes to select and show some data in another sheet. Thus, I needed the index to keep the order in the table and not mess this final step.

This shows the lines with matching index ( I do not have the checked condition): =IFERROR(arrayformula(VLOOKUP($A$3:$A$1001;IMPORTRANGE($A$1;"DataSorts!B2:S1000");2;false));"")

This gets only the lines where a checkbox (Col1) is checked in the original data sheet : QUERY(IMPORTRANGE(A1;I1);"select Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10 where Col1='TRUE'";1)

You can find an exemple of the logic here : https://docs.google.com/spreadsheets/d/1qBGkWETY2fcUks2gsPgeRSaG_RuFkOmik0_WKNs1nUQ/edit?usp=sharing

Be aware that I put the original data example in the same SS, for simplicity. I'll use an IMPORTRANGE in the QUERY or the VLOOKUP to get the real datas, whichever is the best solution.

Thanks a lot for the help !

Answer 1

try in C2:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A10; 
 QUERY({IMPORTRANGE("1qBGkWETY2fcUks2gsPgeRSaG_RuFkOmik0_WKNs1nUQ"; "Data (Edit)!A2:D10")}; 
 "select Col2,Col1,Col3,Col4 where Col1=TRUE"; ); {3\ 4}; )))