CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
Jinan Dangor
Jinan Dangor

Reputation: 142

ARRAYFORMULA - Not Working on CONCATENATE

I have the following formula in a Google Sheets document:

=TRIM(CONCATENATE(LOOKUP($D3, MetaData_Classes!$B$3:$B, MetaData_Classes!$D$3:$D), " ", $E3, " ", LOOKUP($D3, MetaData_Classes!$B$3:$B, MetaData_Classes!$E$3:$E)))

(Spreadsheet available here, the code is in the 'MetaData_Subclasses' tab in the 'Formatted Subclasses' table).

While quite long, it is basically broken down into the following plain language command:

Get two values associated with D3, and concatenate them on either side of E3 (trimming whitespace).

This formula works fine and can be dragged down the entire column it sits in to get the desired results.

However, I wanted to automate this process so that you didn't need to manually copy the formula down the column whenever you add new elements, and it appears ARRAYFORMULA was built for this task.

The problem seems to be that ARRAYFORMULA doesn't interact well with trying to convert this into a formula it can comprehend. Even just trying the following test command fails:

=ARRAYFORMULA(CONCATENATE(D3:D))

Because CONCATENATE gets to the array values before ARRAYFORMULA does (and hence outputs a string containing every element in D3:D back-to-back).

How can I use a function like CONCATENATE, LOOKUP, etc while still being able to use ARRAYFORMULA?

Or is there another way to get this formula down the whole column?

Upvotes: 1

Views: 866

Answers (1)

player0
player0

Reputation: 1

=ARRAYFORMULA(TRIM(
 IFERROR(VLOOKUP(C2:C, MetaData_Classes!A2:D, 3, 0))&" "&D2:D&" "&
 IFERROR(VLOOKUP(C2:C, MetaData_Classes!A2:D, 4, 0))))

0

Upvotes: 1

Related Questions