Query and ImportRange with Dynamic Values

1
votes

I have 2 sheets which are connected.

Sheet-1: Has ID column and Status column (Status field is manually updated by the team)

Sheet-2: Has ID column and Status column (+ many other fields)

Requirement: Status column in Sheet 2 to be updated from Sheet-1 if ID column in sheet-2 = ID column in Sheet-1

I wrote a Query and ImportRange, but I could find Query working only when I compare it against a static value (for e.g. Yes). What I want is it to check against the value in ID column from both the sheets and import only for rows that match.

=QUERY(IMPORTRANGE("1ZkPaYb1IIIkcbVerdmZ-Ru1vxFu1YMWj74QNQj2jles", "Ops Action Sheet!B2:B10000"),"select Col2 where Col1 = 'Yes'")
2
google-sheetsgoogle-sheets-formulaarray-formulasgs-vlookupimportrange

2 Answers

0
votes

This doesn't directly answer your question about using QUERY, but I believe that that function might not be the best tool for what you're trying to do.

I write below a solution using the alternative function VLOOKUP.

I am assuming here that by "Sheet-1" and "Sheet-2", you actually mean entirely different spreadsheets, judging by your use of IMPORTRANGE.

If you don't mind having a copy of Sheet-1's contents living within Sheet-2, here is what you can do:

  1. On a new sheet (tab) in Sheet-2, copy this function (fill in sheet_1_url):
=IMPORTRANGE("sheet_1_url", "Ops Action Sheet!$A:$B")
  1. You should see a copy of the data from Sheet-1 in that tab. You can hide this tab or protect it if you wish.
  2. On your main data tab within Sheet-2, copy this formula in your Status column (assuming that your ID is in A2:
=IFERROR(VLOOKUP($A2,range_from_step_1,2,),)
  1. Copy that formula down as much as you need.

However, if you don't want to have a copy of Sheet-1's data in Sheet-2 for whatever reason, you can simply skip steps 1-2 above, going straight to step 3, with the following formula in Sheet-2:

=IFERROR(VLOOKUP($A2,IMPORTRANGE("sheet_1_url", "Ops Action Sheet!$A:$B"),2,),)

Not that I recommend it, since you'd technically be importing your data virtually within the formula N number of times, which will be a huge performance hit.

You can see the references for the formulas used above here:

0
votes

edit to reflect and paste in spreadsheet 2:

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, IMPORTRANGE("ID", "Sheet!A:C"), {2, 3}, 0)))

A2:A - column of IDs in spreadsheet 2
Sheet!A:C - column A of this range hosts IDs
{2, 3} - brings column B and C from spreadsheet 1 into spreadsheet 2