I want to importrange from Spreadsheet OLD with several tabs/sheets into Spreadsheet NEW. What i want to do is to import values from SheetA and Sheet B. First column: vstack(importrange(SheetA!A5:A),importrange(SheetB!B5:B) easy The problem is here. In second column, I would like to do something like if the row imported is from sheet A , then "Sheet A", if the row imported is from sheet B, then "Sheet B".

My failed approaches: 1.

=VSTACK(
  ARRAYFORMULA(IF(IMPORTRANGE("SheetA", "SheetA!B5:B") <> "", "Sheet A", "")),
  ARRAYFORMULA(IF(IMPORTRANGE("SheetB", "SheetB!B5:B") <> "", "Sheet B", ""))
)

this doesn't work, because i assume, arrayformula doesn't work with vstack. Sheet A is there but it did not anything after that. but you can prove me wrong. 2.

=ARRAYFORMULA(
  IF(A2:A<>"", 
    IF(ISNUMBER(MATCH(A2:A, IMPORTRANGE("SheetA", "SheetA!B5:B"), 0)), "Sheet A",
    IF(ISNUMBER(MATCH(A2:A, IMPORTRANGE("Sheet B", "SheetB!B5:B"), 0)), "Sheet B"
  ""))

close enough, but some of B5:B are from two sheets A or B. So, it would only take SheetA even though it is from SheetB.

So, please give your suggestion.

I want to importrange from Spreadsheet OLD with several tabs/sheets into Spreadsheet NEW. What i want to do is to import values from SheetA and Sheet B. First column: vstack(importrange(SheetA!A5:A),importrange(SheetB!B5:B) easy The problem is here. In second column, I would like to do something like if the row imported is from sheet A , then "Sheet A", if the row imported is from sheet B, then "Sheet B".

My failed approaches: 1.

=VSTACK(
  ARRAYFORMULA(IF(IMPORTRANGE("SheetA", "SheetA!B5:B") <> "", "Sheet A", "")),
  ARRAYFORMULA(IF(IMPORTRANGE("SheetB", "SheetB!B5:B") <> "", "Sheet B", ""))
)

this doesn't work, because i assume, arrayformula doesn't work with vstack. Sheet A is there but it did not anything after that. but you can prove me wrong. 2.

=ARRAYFORMULA(
  IF(A2:A<>"", 
    IF(ISNUMBER(MATCH(A2:A, IMPORTRANGE("SheetA", "SheetA!B5:B"), 0)), "Sheet A",
    IF(ISNUMBER(MATCH(A2:A, IMPORTRANGE("Sheet B", "SheetB!B5:B"), 0)), "Sheet B"
  ""))

close enough, but some of B5:B are from two sheets A or B. So, it would only take SheetA even though it is from SheetB.

So, please give your suggestion.

• google-sheets
• lambda
• google-sheets-formula
• reduce
• array-formulas

• Welcome to StackOverFlow! Would you be able to provide your sample sheet, with your initial output, and also your expected output so that we can further help you. You may use this to provide a markdown table (you may create one with the help of this link ) or an anonymous sample spreadsheet (using this link – Alma_Matters Commented Jan 14 at 3:06
• So, there may data in either SheetA or SheetB? Better to share few sample data showing your desired output? – Harun24hr Commented Jan 14 at 3:06
• oh sorry! old spreadsheet - docs.google/spreadsheets/d/… new spreadsheet - docs.google/spreadsheets/d/… – cywh Commented Jan 14 at 3:18

2 Answers 2

You can try this formula using Query:

=QUERY(
  {
    IMPORTRANGE("OLD_SHEET_URL", "SheetA!B2:B"),
    ARRAYFORMULA(IF(IMPORTRANGE("OLD_SHEET_URL", "SheetA!B2:B")<>"", "SheetA", ""))
  ;
    IMPORTRANGE("OLD_SHEET_URL", "SheetB!B2:B"),
    ARRAYFORMULA(IF(IMPORTRANGE("OLD_SHEET_URL", "SheetB!B2:B")<>"", "SheetB", ""))
  },
  "SELECT Col1, Col2 WHERE Col1 IS NOT NULL",
  0
)

Note: Kindly replace OldsheetUrl with the link to your old sheet.

Sample Output:

You can use REDUCE.

=ARRAYFORMULA(
   REDUCE(
     {"Food", "Sheet"},
     {"SheetA", "SheetB"},
     LAMBDA(result, sheet, LET(
       import, TOCOL(IMPORTRANGE(
         "Old_Spreadsheet_ID",
         sheet & "!B2:B"
       ), 1),
       stack, HSTACK(import, IF(N(import)^0, sheet)),
       VSTACK(result, IF(ISERROR(stack), TOCOL(,1), stack))
     ))
   )
 )