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 |
|---|---|---|---|---|---|---|---|
| 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 |
| 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 |
|---|---|---|---|---|---|
| Walmart | |||||
| Amazon | |||||
| Home Depot | |||||
| Apple | |||||
| CVS | |||||
| Microsoft | |||||
| BMW | |||||
| Toyota | |||||
| … |
Expected Output
| Account | Address | City | State | Zip | Country |
|---|---|---|---|---|---|
| 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 | |||||
| Interdum<br>Bibendum<br>Proin | Mountain View | CA | 94043 | United States | |
| Toyota |
>Solution :
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))))