1

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 sheet

enter image description here

Thanks a lot!

player0
  • 124,011
  • 12
  • 67
  • 124
onit
  • 2,275
  • 11
  • 25
  • 1
    hi, please link the previous [Answer](https://stackoverflow.com/a/73626158/19529694) in your question to benifit future visitors. – Osm Sep 06 '22 at 18:44

1 Answers1

2

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, ))))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • the header problem when aggregated with 9^9 in the first IMPORTRANGE ? – Osm Sep 06 '22 at 18:48
  • You can discard the `Query` header since it's a range of a `Vlookup` see [this image](https://i.imgur.com/RW0ll19.jpg) – Osm Sep 06 '22 at 18:56
  • 1
    @Osm yup, I made it shorter – player0 Sep 06 '22 at 19:03
  • Hi! I see that if it worked, it's solved and marked as such, but it gives me the error here: ```Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: Col2```. Here's the formula: ```=Arrayformula(if(E10:E="";;IFNA(vlookup(A10:A&" "&E10:E; QUERY({ IMPORTRANGE("FILE_ID";"Pedidos!L1:L")&" "& IMPORTRANGE("FILE_ID";"Pedidos!B1:B"); IMPORTRANGE("FILE_ID";"Pedidos!D1:D")}; "select Col1,Col2");2;0))))```. Error simple to pin point? Thanks a million for all your help! – onit Sep 06 '22 at 19:31
  • @onit yes. use: `=Arrayformula(if(E10:E="";;IFNA(vlookup(A10:A&" "&E10:E; QUERY({ IMPORTRANGE("FILE_ID";"Pedidos!L1:L")&" "& IMPORTRANGE("FILE_ID";"Pedidos!B1:B")\ IMPORTRANGE("FILE_ID";"Pedidos!D1:D")}; "select Col1,Col2");2;0))))` – player0 Sep 06 '22 at 19:40
  • Thanks - Last question: WHYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY? – onit Sep 06 '22 at 19:57
  • 1
    @onit locale :) if you intend to use ; as separator than ; will put stuff under each other while \ will put stuff next to each other (notice the \ before the 3rd importrange) – player0 Sep 06 '22 at 20:01
  • 1
    @onit https://stackoverflow.com/q/73767719/5632629 – player0 Sep 19 '22 at 14:11