Google Sheets – Get Data from Another Sheet in Different Order

formulasgoogle sheetsgoogle-sheets-arrayformulaimportrangevlookup

I have a very basic knowledge of deeper functions of Google sheets and solving this has been a little bit of pain in the butt so far.

I have a spreadsheet A

  • column A contains ID
  • column I that has the info that I need to receive

I have spreadsheet B

  • in my column X I need to get the exact data that is in spreadsheet A, column I

the thing is these two spreadsheets don't have the same order of IDs so basically I need it first look for the correct ID, THEN to duplicate data into my spreadsheet B, column X.
Column A in both sheets contain IDs, just assorted compared to each other.

Hopefully, this explanation is understandable enough.

Best Answer

=ARRAYFORMULA(IFERROR(VLOOKUP(W2:W; 
 IMPORTRANGE("17FPRpPL0ieLGAw1971urLUu_egG7bMBqDtpzRk6-_ic"; 
 "Sheet1!A2:I"); 9; 0); ))

  • W2:W in your case A1:A
  • 17FPRpPL0ieLGAw1971urLUu_egG7bMBqDtpzRk6-_ic needs to be changed for yours (spreadsheet A)
  • Sheet1!A2:I will need to have actual sheet name (the part before !)
  • 9 is column number of column I
  • if you get #REF! error first use =IMPORTRANGE("ID-or-URL", "Sheet1!A2:A") and allow access and then use that array formula with vlookup from above
Related Topic