CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Eduardo Manstretta
Eduardo Manstretta

Reputation: 1

Pull data to destination from source based on criteria

Working in Google Sheets, I'm working on a spreadsheet (let's call it destination) and need to match data from it to data from another spreadsheet (let's call it source) to pull data from source into destination.

Destination spreadsheet has Service in column E, Weight in column I and Zone in column J. I need to match data from columns E, I and J, with columns of B, C, and D respectively of source spreadsheet.

If Column E Service Type, column H Billed Weight and Column J Zone from destination match with values of column B, C and D respectively of source, it will pull the data from column E of source and use it to populate column K of destination spreadsheet.

Both spreadsheets are in different Google Drive folder locations.

I tried using this formula in the cell I needed the data from column E of source to populate:

=IFERROR(IF(AND(E3=IMPORTRANGE("URL_of_Source", "DataSheet!B:B"), I3=IMPORTRANGE("URL_of_Source", "DataSheet!C:C"), J3=IMPORTRANGE("URL_of_Source", "DataSheet!D:D")), INDEX(IMPORTRANGE("URL_of_Source", "DataSheet!E:E"), MATCH(TRUE, ARRAYFORMULA((IMPORTRANGE("URL_of_Source", "DataSheet!B:B")=E3)*(IMPORTRANGE("URL_of_Source", "DataSheet!C:C")=I3)*(IMPORTRANGE("URL_of_Source", "DataSheet!D:D")=J3)), 0)), ""), "")

I expected to see the right value populate form source, but the cell remains blank.

Upvotes: 0

Views: 38

Answers (1)

Martín
Martín

Reputation: 10217

If you have several conditions, and unique values; you can try with FILTER:

=IFNA(FILTER(Source!E:E,Source!B:B=E2,Source!C:C=I2,Source!D:D=J2),"No register matches all values")

Or, as an array:

=MAP(E2:E,I2:I,J2:J,LAMBDA(e,i,j,IF(e="","",IFNA(FILTER(Source!E:E,Source!B:B=e,Source!C:C=i,Source!D:D=j),"No register matches all values"))))

Upvotes: 1

Related Questions