Importing values of column from other sheet based on a matching values in a different column across-CodePudding

Hey Stack Overflow Sheets, I have a question regarding a use case we want to create in our Google Sheet, either with built-in functionality, or with an extension like Sheetgo.

We have 2 sheets with multiple of the same columns (properties) and rows (users). The problem is that only one of the sheets is a trusted source for one column’s data (“source sheet”), and the other sheet has empty or outdated values for the same column (“outdated sheet”), and we need both sheets to have this column match values in rows that have a matching value for another column across both sheets (in our case, an “email” column). After they’re matching, we want to change the formula to sync any changes made for that column between both sheets.

Here’s an obfuscated data example:

Source sheet: https://docs.google.com/spreadsheets/d/1uxqC3lB15UHhKTzjZyzzVIj5tlPjhCCCZ48xHYEcm0o/edit?usp=sharing

Outdated sheet: https://docs.google.com/spreadsheets/d/1ckoCh8gMwt2QeBRH1dB2dyFPJUukrjQ-SCgucTL8rhc/edit?usp=sharing

In the example, we’re looking for a formula that would allow us to have a “Type” column value injected into the Outdated Sheet’s Type column, based on both sheet’s matching Email column value. And then, have it so if a row’s “Type” value changes in either doc, the other doc follows.

What formula or extension would I use to go about this? Any help appreciated, thanks!

I tried to create a VLOOKUP and MATCH formula, but I couldn't yet figure out how to have the function first LOOKUP into the Source Sheet, then inject it into the Outdated Sheet based on a matched email column value. Sheetgo made the LOOKUP easier, but I still couldn't figure out how to do an exact operation.

CodePudding user response：

Use importrange() and vlookup(). Put this formula in cell A1 of the target spreadsheet:

=arrayformula( 
  lambda( 
    import, 
    iferror( 
      vlookup( 
        C1:C, 
        { index(import, 0, 3), index(import, 0, 1) }, 
        2, false 
      ) 
    ) 
  )( 
    importrange("1uxqC3lB15UHhKTzjZyzzVIj5tlPjhCCCZ48xHYEcm0o", "Sheet1!A1:F") 
  ) 
)