INDEX MATCH or JOIN COLLECT formula for multiple criteria's

Adriane Price

Hello,

I am attempting to take a previous intake from and update the new form will allow for more "automation". On the form the requestor/submitter selects a theater and region/country. Once the intake form is received the data in the sheet populates (based on formulas) the following: division, country, PRM, RPL, or SRM. Currently they are looked up by using either a VLOOKUP or single INDEX/MATCH from their respective division. Since our company has changed this but our services as a team are still based on our division I am having issues with pulling in data. Current routing is based on division and I have not been asked/requested to change that.

One issue I am having in the new form-to-be is some (PRM, RPL, SRM) only pull in base on their region, because there are two or three people covering that region, division or country. Second issue I am having I would like to stream line the columns as much as possible. For reporting purposes I have created helper columns to simplify by using a JOIN/COLLECT.

I thought it would be possible to keep it simple so I do not have three - five extra columns for each division, country, PRM, RPL, or SRM. Possibly by doing either a JOIN/COLLECT or an INDEX/MATCH? Hoping for some guidance or second set of eyes, I think I am getting tunnel vision on the project, on what possibly would be best for these columns. I am able to share the working intake sheet.

Screenshot sample of the vast amount of columns I have been creating to automate, consolidate as much as possible.

kirstie858

Ha! I think I might be even more confused now. What I was picturing was two non-formula columns on the left: Theatre, Region/Country. These get populated on the sheet by the form. But I don't see a generic Region/Country column on your first screenshot. Then you get the Division, and use this value as the single lookup value to get the PRM, RPL, or SRM. Does this mean you would only have one set of PRM, RPL OR SRM lookup information? Or for one Division, you could have all three types (PRM Email, RPL Email or SRM Email). If it's only one set per Division, I would simply have an Email column, a Name column and a Partner ID column.

How are your reference sheets organized? Is it possible to combined this data in a single lookup source?

If not, I have used a nested IFERROR(INDEX(MATCH)), where if the first INDEX(MATCH) returns an error, it looks at a second sheet. Example: =IFERROR(IFERROR(INDEX({Forecast1}, MATCH([Opportunity ID]@row, {Key1}, 0)), INDEX({Forecast2}, MATCH([Opportunity ID]@row, {Key2}, 0))), "")

Regarding your issue of having multiple people for one division, I would do a JOIN(COLLECT)), to return a comma delimited cell with more than one contact (when applicable).

Sorry I can't provide more guidance without seeing the layout of the underlying reference sheets.

kirstie858

Hi! This is a very interesting question, and I think I've got a technique to help you, but I'm a little confused about the PRM, RPL, SRM business. I only see PRM on the sheet. Can you clarify what concept these acronyms indicate? I assume the Theatre and Region/Country are the only two pieces of information you need to populate the rest of the columns?

Would you be able to screenshot your sheet with the two inputs on the left, and only your desired output columns on the right, without any helpers? Trying to get a feel for the ideal scenario.

Adriane Price

Hello @kirstie858 - Thank you for checking in and seeing if this is do-able.

Can you clarify what concept these acronyms indicate? clarification for the acronyms are based upon resource managers, PRM = Partner resource manager, SRM = Strategic resource manager. RPL however is not and it is a residency practice lead.

I assume the Theatre and Region/Country are the only two pieces of information you need to populate the rest of the columns? Correct.

Would you be able to screenshot your sheet with the two inputs on the left, and only your desired output columns on the right, without any helpers? For PRM, SRM, RPL what I am working on (top) what is desired (bottom)

Working on [screenshot] - PRMs emails are highlighted in yellow they are pulled in based upon country.

Desired [screenshot] - the PRM that should be in the list that are highlighted in yellow in the above screenshot. PRMs emails are pulled in based upon Theater/Division column.

kirstie858

Ha! I think I might be even more confused now. What I was picturing was two non-formula columns on the left: Theatre, Region/Country. These get populated on the sheet by the form. But I don't see a generic Region/Country column on your first screenshot. Then you get the Division, and use this value as the single lookup value to get the PRM, RPL, or SRM. Does this mean you would only have one set of PRM, RPL OR SRM lookup information? Or for one Division, you could have all three types (PRM Email, RPL Email or SRM Email). If it's only one set per Division, I would simply have an Email column, a Name column and a Partner ID column.

How are your reference sheets organized? Is it possible to combined this data in a single lookup source?

If not, I have used a nested IFERROR(INDEX(MATCH)), where if the first INDEX(MATCH) returns an error, it looks at a second sheet. Example: =IFERROR(IFERROR(INDEX({Forecast1}, MATCH([Opportunity ID]@row, {Key1}, 0)), INDEX({Forecast2}, MATCH([Opportunity ID]@row, {Key2}, 0))), "")

Regarding your issue of having multiple people for one division, I would do a JOIN(COLLECT)), to return a comma delimited cell with more than one contact (when applicable).

Sorry I can't provide more guidance without seeing the layout of the underlying reference sheets.

Adriane Price

@kirstie858 - I am happy to provide access to the sheet, I believe it may help. Let me know if you would like access.

kirstie858

@Adriane Price Sure! You can share to <email_address_removed>

Adriane Price

@kirstie858 - I finally got through it all and everything is working. Thank you for your insight. This ended up being more detailed (creating more helper columns) than I thought it would be. However, end results are what I am looking for. I ended up doing this formula for each group.

=JOIN(DISTINCT(COLLECT([PRM Name (LOB Consult)]@row:[PRM Name (Theater/Division/Region)]@row, [PRM Name (LOB Consult)]@row:[PRM Name (Theater/Division/Region)]@row, <>"")))