CODEWITHSUNDEEP
google-sheets
frankstuner
frankstuner

Reputation: 4500

Loop column content from another tab and add extra text to output

Objective

I have a column animal names in another tab (Animals) of the same google sheet.

Animals
ant
bat
cat
dog

I am attempting to source that column of data into another sheet and append string text either side for it's output.

Eg: This is a [animal]. Isn't it cool.

Animals Output
This is called ant. Isn't it cool.
This is called bat. Isn't it cool.
This is called cat. Isn't it cool.
This is called dog. Isn't it cool.

Progress

I've been able to list the animals column into the destination tab using:

=IMPORTRANGE("sheet_url", "Animals!A1:A4")

Problem

I haven't been able to add the string text to the left and right of the output.

I've looked at:

  1. Concatenate, but it breaks the reference for the import range:
=IMPORTRANGE("sheet_url", CONCATENATE(A2,"Animals!A1:A3"),A3)
  1. Arrayformula, but that doesn't work with importrange as per: Using IMPORTRANGE as an array formula

Can anyone point me in the right direct here please?

Upvotes: 0

Views: 24

Answers (1)

rockinfreakshow
rockinfreakshow

Reputation: 30120

You may try:

=let(Σ,importrange("URL","Sheet!A2:A"),
       map(Σ,lambda(z,if(z="",,"This is called "&z&". Isn't it cool."))))

enter image description here

Upvotes: 2

Related Questions