I am extremely basic in my understanding of coding formulas for Google Sheets, so please help where you can. The formula I'd like to put together is to search a vertical range (column) of information on a sheet and if there is any data present, I'd like the cells to automatically fill with the data of from the corresponding row of another vertical range within that same sheet.

For instance, I'd like my cell to search a range on a sheet (for this we can say the sheet is called 'Data Import') of Cells L1:L, and if there is any data present in that range, to import the data from the corresponding row of data in cells C1:C. I am sure this needs to be some kind of IF formulate possibly using vlookup or importrange, but I am not sure how to create the string for this. Ideally, this will results in a list of data from C1:C only if any data exists in the corresponding row for L1:L within the "Data Import" sheet.

I hope this makes sense! I can clarify further if needed!


1 Answer 1


Try this in row 1 of a free column:

=arrayformula( if( len('Data Import'!L1:L), 'Data Import'!C1:C, iferror(1/0) ) )

To omit values on rows where L1:L is blank, use this:

=filter('Data Import'!C1:C, len('Data Import'!L1:L))
  • This seems to have worked! It is importing, but it is importing EVERYTHING, even if the range is blank. Is there any way to omit the line if the cell range for hypothetical range L1:L is blank? This way it is ONLY populating with information when data exists in that range? Also, if it isn't too much trouble, could you try to explain the logistics of how the formula works? I am really working to understand this and be more self sufficient. Again - thank you for your help! Apr 19, 2021 at 17:38
  • Added an alternative formula that leaves out values in rows where L1:L is blank. If you need more help, please share a publicly editable sample spreadsheet with realistic-looking data. Apr 19, 2021 at 18:30
  • That worked! I really appreciate it! Thank you! Are you able to clarify how those formulas process so that I can work to use them in future work? Apr 19, 2021 at 19:04
  • Thank you so much for your help with everything! I really appreciate it. As a follow up, if I wanted to make a formula for formula that can reference cells A1:A on my main sheet, and reference another sheet refer back to the "Data Import" sheet for the same value to exist. Ideally, this will generate a list of all the items that match A1:A with the corresponding data from the "Data Import" (let's stick with L1:L in "Data Import") that is in row with the information on C1:C of "Data Import" that matches A1:A from the main sheet. Thank you again! Apr 19, 2021 at 20:48

Your Answer

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

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