How to apply multiple criteria when using VLOOKUP+QUERY+IMPORTRANGE?

user_11832197 0
Viewed 92

So, right now, the formula below successfully queries data from another file, butthis based on a column's data as the criteria. The need is to have more than one criteria and I'm not sure if thecolumn to be compared against needs to come in as an additional query, together with the first query...so I'm lost here.

This is the formula I'm using:

=Arrayformula(
   if(F9:F="";"";
      vlookup(A9:A;
         QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxXXXXXXXxxxxxXXXX/edit";"Costura!A1:R"); 
         "select Col6, Col8, Col14";0)
      ;2;0)
    )
 )

Here's a sample file with dummy data!

google-query-language google-sheets-formula google-sheets import arrays
2 Answers

For a system like this, the best practice is just to import the entirety of the imported data in a tab of it's own, then run a vlookup to that tab using "both" criteria. This will simplify your formulas and also does not increase processing time at all. Just hide the "naked" import tab.

I will demonstrate on your sample sheet in two tabs beginning with "MK".

but the basic idea is an ampersanded (&'ed) [search key] in your VLOOKUP()

=ARRAYFORMULA(IFERROR(VLOOKUP(A6:A&B6:B,{MK_Data!A:A&MK_Data!H:H,MK_Data!J:J},2,0)))
user_110751290

can be done like this:

=ARRAYFORMULA(IF(B6:B="",,IFNA(VLOOKUP(A6:A&B6:B, 
 {QUERY(Data!A1:J, "select A", 0)&
  QUERY(Data!A1:J, "select H", 0), 
  QUERY(Data!A1:J, "select J", 0)}, 2, 0))))

which will translate into this:

=ARRAYFORMULA(IF(B6:B="",,IFNA(VLOOKUP(A6:A&B6:B, 
 {IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU", "Data!A:A")&
  IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU", "Data!H:H"), 
  IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU", "Data!J:J")}, 2, 0))))

enter image description here

user_56326290
Related