Translating an Excel ConcatIf argument to Google's CONCAT formula

Question

I have an inventory spreadsheet created in really old Excel (2000!) with two pages; Inventory and Master. Everything looks fine in Google Sheets, until it comes to the ConcatIf argument on my Master page. The second page calls for words from the first page, using a fairly simple concatenation argument. Here is the formula for the first row on the Master page:

=ConcatIf(Inventory!A:A,"*"&Master!A4&"*",Inventory!B:B,", ")

It is matching the name on line 4 in Master with the name in Inventory on column A, then drawing out the words related to that name, found in column B of Inventory, and returning them as a list.

When I look at this spreadsheet in Google Sheets, the results on the Master page fail. It says I'm using four arguments, when only two are expected.

How can that formula be translated to CONCAT? I'm not a master of this language, the formula was written for me by one.

Some clarification:

This is about a spreadsheet for people and the names of photographs related to them. Inventory page has column A with various person's names, and column B with the names of images they appear in. Personal names are frequently repeated.

Master has column A with all personal names in alphabetical order, and column B seeks to concatenate all the image names relevant to the person named in A. For every name in Master A, there will be corresponding instances in Inventory A. I'm just trying to gather all the image names from Inventory B to Master B.

So concatenate still seems like the answer to me. It's only a question of translating it from Excel to Google.

Here is a link to an example spreadsheet created in Google, using the formula suggested by zaq. https://docs.google.com/spreadsheets/d/1kfxHnm5nHHrwLBCFCGLxoyuBqEFKj1l_AqCzIcVFYew/edit?usp=sharing

Answer 1

This is more dynamic if you also use regex instead of wildcards as the specific way in which you're using the wildcard is different in Google Sheets compared to Excel .... but this works:

=join(", ", filter(Inventory!B:B, istext(regexextract(Inventory!A:A,Master!A2))))

Note: you should also finally wrap your whole function with =iferror() so that you don't have to see that ugly #N/A when there are no matches... secondly, occasionally I convert everything to lowercase inside of the matching portion of the formula so as to avoid any one of many complications that can happen when you user generated content or input.

That would end up looking like this:

=iferror(join(", ", filter(Inventory!B:B, istext(regexextract(arrayformula(lower(Inventory!A:A)),lower(Master!A2))))))

Answer 2

To my knowledge, ConcatIf is not a standard Excel function but a custom function written by someone in VBA. My guess for what it means:

=ConcatIf(filter_range, value, concat_range, separator)

joins the entries of concat_range where the corresponding filter_range entry matches value, placing separator in between.

This logic can be expressed in Google Sheets by the built-in functions filter and join as follows:

=join(separator, filter(concat_range, filter_range = value))

In your case this would be

=join(", ", filter(Inventory!B:B, Inventory!A:A = "*"&Master!A4&"*"))

---

By the way, in Google Sheets one rarely needs concat function, which is for joining two strings, and is equivalent to "&" operator. The functions to use are concatenate and the aforementioned join.