CODEWITHSUNDEEP
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
Tarik Hodzic
Tarik Hodzic

Reputation: 357

Change value of a cell depending on contents of column of another google sheet

I have a Google sheet with information on it and I am trying to automate it a bit. I need a formula which changes a cell value to Yes if there are specific strings in the column of another sheet. I have tried a couple different things using IF and importrange but it's just not working.

I have created a sample sheet to show what I am trying to do: Test Sheet 1 Test Sheet 2

I would like column C of Sheet 1 to change to Yes if Columns 1 and 2 of both sheets match and Column C of Sheet 2 contains "Reloaded" or "Yes".

Upvotes: 1

Views: 2133

Answers (2)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IF(REGEXMATCH(VLOOKUP(A2:A&B2:B, {
 IMPORTRANGE("1YMBUYC6JgQke-2YWs_VZx9zqlmOdhV8WYvhTpTVxBYM", "Sheet1!A2:A")&
 IMPORTRANGE("1YMBUYC6JgQke-2YWs_VZx9zqlmOdhV8WYvhTpTVxBYM", "Sheet1!B2:B"), 
 IMPORTRANGE("1YMBUYC6JgQke-2YWs_VZx9zqlmOdhV8WYvhTpTVxBYM", "Sheet1!C2:C")}, 2, 0), 
 "Yes|Reloaded")=TRUE, "Yes", ))

0

UPDATE:

=ARRAYFORMULA(IFERROR(IF((D2:D="User Task")*(REGEXMATCH(VLOOKUP(B2:B, {
 IMPORTRANGE("1YMBUYC6JgQke-2YWs_VZx9zqlmOdhV8WYvhTpTVxBYM", "Sheet1!B2:B"), 
 IMPORTRANGE("1YMBUYC6JgQke-2YWs_VZx9zqlmOdhV8WYvhTpTVxBYM", "Sheet1!C2:C")}, 2, 0), 
 "Yes|Reloaded")=TRUE), "Yes", )))

Upvotes: 2

kishkin
kishkin

Reputation: 5325

Here you go:

={
  "Complete";
  ARRAYFORMULA(
    IF(
        (IMPORTRANGE("1YMBUYC6JgQke-2YWs_VZx9zqlmOdhV8WYvhTpTVxBYM", "Sheet1!A2:A") = A2:A)
      * (IMPORTRANGE("1YMBUYC6JgQke-2YWs_VZx9zqlmOdhV8WYvhTpTVxBYM", "Sheet1!B2:B") = B2:B)
      * (REGEXMATCH(IMPORTRANGE("1YMBUYC6JgQke-2YWs_VZx9zqlmOdhV8WYvhTpTVxBYM", "Sheet1!C2:C"), "Reloaded|Yes")),
        "Yes",
        ""
    )
  )
}

enter image description here

Upvotes: 2

Related Questions