CONCATENATE or JOIN multiple columns from VLOOKUP into single string

Question

I have a worksheet with 2 tabs - Customers, Data. All tabs have a list of customers. The list on Data is a subset of all Customers. I need to pull available address information for Customers from Data.

I need the Address1-3 columns in Data to be joined using <br> and placed in the Address column in Customers. The situation seems similar to this other SO thread joining results into a single string, however those values are all vertical in different rows and the difference here is the values are horizontal in different columns.

Not working:

• =TEXTJOIN("<br>",1,VLOOKUP(A2,Data!A:D,{2,3,4},FALSE))
• =TEXTJOIN("<br>",1,QUERY("Data!A:H","SELECT B,C,D WHERE "&A2&"="&Data!A:A))
• =TEXTJOIN("<br>",1,FILTER(Data!A:D,A2))

Google Sheets example ready for copy/fiddle.

Example Data - the names have been changed to protect the innocent

Account	Address1	Address2	Address3	City	State	Zip	Country
Facebook	Lorem	Ipsum	Dolor	Menlo Park	CA	94025	United States
Amazon	Sit	Amet	Consectetur	Seattle	WA	98109	United States
Apple	Adipiscing	Elit	Ut	Cupertino	CA	95014	United States
Microsoft	Ultricies	Velit	Eu	Redmond	WA	98052	United States
Google	Interdum	Bibendum	Proin	Mountain View	CA	94043	United States

Example Customers - the names have been changed to protect the innocent

Account	Address	City	State	Zip	Country
Facebook
Walmart
Amazon
Home Depot
Apple
CVS
Microsoft
BMW
Google
Toyota
...

Expected Output

Account	Address	City	State	Zip	Country
Facebook	Lorem<br>Ipsum<br>Dolor	Menlo Park	CA	94025	United States
Walmart
Amazon	Sit<br>Amet<br>Consectetur	Seattle	WA	98109	United States
Home Depot
Apple	Adipiscing<br>Elit<br>Ut	Cupertino	CA	95014	United States
CVS
Microsoft	Ultricies<br>Velit<br>Eu	Redmond	WA	98052	United States
BMW
Google	Interdum<br>Bibendum<br>Proin	Mountain View	CA	94043	United States
Toyota

Answer 1

Try this:

=ARRAYFORMULA(JOIN("<br>",QUERY({Data!A1:H6},"SELECT Col2,Col3,Col4 WHERE Col1 = '"&A2&"'",0)))

Edit: Harun24hr has the better answer, this will not autofill down.

Answer 2

You need to FILTER() then join. Try-

=IFERROR(MAP(A2:A14,LAMBDA(x,JOIN("<br>",FILTER(Data!B2:D,Data!A2:A=x)))),"")

Answer 3

Your formula is fine, you just have to wrap it in an ArrayFormula():

=ArrayFormula(IFNA(TEXTJOIN("<br>",1,VLOOKUP(A2,Data!A:H,{2,3,4},FALSE))))