0

I am using this formula to look for data in column F related to data in column A between two spreadsheets.

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A; {IMPORTRANGE("spreadsheetID";"sheet1!A4:A")\ IMPORTRANGE("spreadsheetID";"sheet1!F4:F")}; 2; 0);))

for some reason after some while (minutes to days) the formula stops working and I need to change the sheet name from SHEET1 to 'SHEET1' and it starts working again. BUT after some time I need to change that back to only SHEET1 because it stopped working again and needs a change to be done.

Reason for this instead of

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A;IMPORTRANGE("spreadsheetID";"sheet1!A4:F"); 6; 0);))

is because this seems to take a lot less time to import since it's only minding two columns instead of A and F PLUS all between those two. And also because sometimes I am using the same formula for columns like DF and that's a huge amount of data.

1

While the above formulas may work for you, in case of huge imported arrays is better to import them separately in a sheet as:

A1: =IMPORTRANGE("spreadsheetID";"sheet1!A4:A")
B1: =IMPORTRANGE("spreadsheetID";"sheet1!F4:F")

and then use it in VLOOKUP:

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A; 'hiddenimportsheet'!A:B; 2; 0)))

This will slightly improve the performance and luckily solve your issue.

  • hmmm, interesting. When I use A4:A it gives me "internal error importing range" but A:A works just fine (but it also imports the unwanted first four cells/rows obviously). Does that mean there is some problem with the source spreadsheet? Unfortunately I cannot share these two specific spreadsheets and I suspect this is somewhat not possible to redo for test sheets – Marek Marhoul Jun 14 at 12:32
  • the only thing what comes to my mind is sheetname. make sure that formula uses exactly the same one (caps-wise like too) and also make sure you have enough rows. if sheet name does not have spaces you dont need to wrap it into single quotes ' ' – user0 Jun 14 at 12:39
  • The source spreadsheet has 1912 rows out of which 1419 are filled with data, target spreadsheet had also 1912, tried to have enough so now has 3823. I have tried A4:A1912 and it works perfectly but A4:A simply doesn't. I am clueless. Sheet name is exactly the same as in formula. – Marek Marhoul Jun 14 at 12:57
  • Anyway, I have imported it with A:A and F:F (and so on) and it works great so far, hopefully it stays like this. Thank you for your help! – Marek Marhoul Jun 14 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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