How can I uso VLOOKUP QUERY IMPORTRANGE using multiple conditions in non-sequential columns in G-CodePudding

The formula/solution below, kindly provided by a sheets genius, solves it when the criteria/ranges obtained are next to each other, but just bumped into a situation where there are other columns in between. I thought that by selecting the wanted column in the first query would get me in the right direction, but it doesn't, so here I am:

Here is the formula sitting in cell H3:

=ARRAYFORMULA(IF(A3:A="",,IFNA(VLOOKUP(F3:F&" "&G3:G,
 QUERY({FLATTEN(QUERY(TRANSPOSE(
 IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU", "Arrayformula VLOOKUP multiple columns!A1:C")),,9^9)),
 IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU", "Arrayformula VLOOKUP multiple columns!D1:D")}, 
 "select Col1,Col2 label Col2 'x'", 0), 2, 0))))

Here is the example

Thanks a lot!

CodePudding user response：

use:

=ARRAYFORMULA(IF(A3:A="",,IFNA(VLOOKUP(F3:F&" "&G3:G,
 QUERY({
 IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU", "Arrayformula VLOOKUP multiple columns!A1:A")&" "&
 IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU", "Arrayformula VLOOKUP multiple columns!C1:C"),
 IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU", "Arrayformula VLOOKUP multiple columns!D1:D")}, 
 "select Col1,Col2"), 2, ))))