How to use VLOOKUP() + QUERY() + IMPORTRANGE() with mutiple conditions?

onit 0
Viewed 19

I currently have this one working fine with 01 condition, but I can't manage to include another criteria:

=Arrayformula(if(A3:A="","",IFNA(vlookup(E3:E,QUERY(IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU","Arrayformula VLOOKUP multiple columns!A1:C"), "select Col1, Col2, Col3 label Col3 'Qtd'",0),3,0))))

I've started by concatenating A3:A&E3:E, but I don't know how to get the data ranges for comparison in this fashion using QUERY().

Here is the result VS expectation: [![enter image description here][1]][1]

Also, here is an example for tests: https://docs.google.com/spreadsheets/d/1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU/edit?usp=sharing

Appreciate any help - as usual! [1]: https://i.stack.imgur.com/o9FQT.png

formula import google-query-language google-sheets-formula google-sheets
1 Answers

use:

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

enter image description here

player00
Related