0

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.

Improve this question
0

1 Answer 1

Reset to default
0 

=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
Improve this answer
9
  • Hello, firstly thank you a lot for your time and effort! I have tried what you said and I got #ERROR!, so I have tried only the importrange function and it had access to the spreadsheet A so I guess the problem is somewhere in my formula: =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, IMPORTRANGE("link_to_my_spreadsheet_B"; "sheet_I_need!A4:I"), 9, 0), )). A4 is the first cell with ID so I guess I should use that row number, right?
    – Marek Marhoul
    Feb 19, 2019 at 13:25
  • I have imported all data into my spreadsheet B under a separate sheet with IMPORTRANGE formula so I think now I can work with the data easier, right? I have tried to only use VLOOKUP for the rest of the job but it says that it expects a number and my data is in text (even tried changing the format to text and vice versa but that didn't help)
    – Marek Marhoul
    Feb 19, 2019 at 16:26
  • @MarekMarhoul can you share a copy of your sheet?
    – user0
    Feb 19, 2019 at 16:43
  • what the #ERROR! message said? yes for A4. try changing every , to ; - that's the reason for the error I guess.
    – user0
    Feb 19, 2019 at 16:47
  • 1
    Great, it's working! Thank you =ARRAYFORMULA(IFERROR(VLOOKUP(B2:B;{Sheet1!A2:AG; Sheet2!A2:AG}; 24; 0)))
    – Marek Marhoul
    Feb 26, 2019 at 17:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.