I am building a Google Sheet that pulls data from another Google Sheet with multiple tabs. The source sheet doesn't include one particular data point (i.e. business name), and I want to populate the cells in the corresponding rows of the new sheet based on the source tab. Is this possible? If so, what is the formula/function for doing so?

In this sheet, I would like column F to be populated based on the source tab rather than manually typed. Thanks!

  • Hi, welcome to WebApps. You have not provided any details of the source sheets. Is there a field for the "Business name"/Prescribing Partner in the source sheets, or are you anticipating taking the name from the sheet name of the import range. e.g. "JenCare - AP Prescription Form" is for the Business name - "Jencare". Would you please also summarise your research and your efforts to solve this problem yourself.
    – Tedinoz
    Commented Aug 4, 2022 at 5:24
  • Try vlookup(). Your sample spreadsheet gives "Access denied." Commented Aug 4, 2022 at 20:52
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Commented Aug 8, 2022 at 23:34

1 Answer 1


Add an additional column to each of the IMPORTRANGE` arrays; the extra column will contain the "Business name".

For example: based on the formula in Column E,

=QUERY({IMPORTRANGE("<<insert url1>>", "CareSource Stronger Together Referral Form!e2:e1000"),arrayformula(if(len(importrange("<<insert url1>>","CareSource Stronger Together Referral Form!e2:e1000")),"CareSource",)); importrange("<<insert url2>>","JenCare - AP Prescription Form!E2:E1000"),arrayformula(if(len(importrange("<<insert url2>>","JenCare - AP Prescription Form!E2:E1000")),"Jencare",)); IMPORTRANGE("<<insert url3>>", "Focus Forward - AP Prescription Form!e2:e1000"),arrayformula(if(len(importrange("<<insert url3>>", "Focus Forward - AP Prescription Form!e2:e1000")),"Focus Forward",))}, "SELECT * WHERE Col1 IS NOT NULL" )


This is the extra column:

,arrayformula(if(len(importrange("<<insert url3>>", "Focus Forward - AP Prescription Form!e2:e1000")),"Focus Forward",))

  • if the IMPORTRANGE has a value, then create a new Column "Focus Forward".




The existing spreadsheet has one formula for each column; this could be simplified by creating one formula (entered in Cell A2) that populates columns A-F. Using the example data from above, such a formula might look like this:

=QUERY({IMPORTRANGE("<< INSERT URL1>>", "CareSource Stronger Together Referral Form!a2:e1000"),arrayformula(if(len(importrange("<< INSERT URL1>>","CareSource Stronger Together Referral Form!a2:a1000")),"CareSource",)); importrange("<< INSERT URL2>>","JenCare - AP Prescription Form!a2:E1000"),arrayformula(if(len(importrange("<< INSERT URL2>>","JenCare - AP Prescription Form!a2:a1000")),"Jencare",)); IMPORTRANGE("<< INSERT URL3>>", "Focus Forward - AP Prescription Form!a2:e1000"),arrayformula(if(len(importrange("<< INSERT URL3>>", "Focus Forward - AP Prescription Form!a2:a1000")),"Focus Foward",))}, "SELECT * WHERE Col1 IS NOT NULL" )

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.