Help combining: IF INDEX MATCH

A Rose

Hi,

Please help me with my formula,

I'm trying to GET a Contact Column, based on matching criteria,

(The columns referenced are from a separate sheet.)

I tried using: =IF({Sheet 1 Range Status Column}, "Approved", INDEX({Sheet 1 Range Contact Column}, MATCH(HAS(@cell, Reason@row), {Sheet 1 Range Reason Column}, 0)))

It's giving me the error #INVALID DATA TYPE

The sheet I have has a Text column with a list of all reasons, then the formula is within a Multiple Contact column, the idea is: to collect all contacts that Approved with the reason@row.

The sheet I'm referencing has a Reason column that is a Dropdown with multi select, and a single select Contact Column.

I'm probably using the wrong formula when I need to get all contacts matching the reason.

Thank you!

Genevieve P.

Hi @A Rose

My apologies! There was meant to only be one formula, I believe I didn't copy/paste it properly. Here's what I meant to suggest:

=JOIN(COLLECT({Sheet 1 Range Contact Column}, {Sheet 1 Range Status Column}, "Approved", {Sheet 1 Range Reason Column}, HAS(@cell, Reason@row)), CHAR(10))

This will then look at your Reason in the current row, then check the other sheet's Reason column. For any row where that same reason appears, it will bring together all the unique Contacts associated, as long as that row is also "Approved".

Here's an example of a source sheet:

Then the formula output:

There isn't a direct way to then convert the multi-select options back into Contacts using a formula.

Yes, you could use a VLOOKUP or INDEX(MATCH. In this instance you would need to have a reference sheet with all possible contact combinations. For example, a multi-select column with each possible combination on the left, and a contact column with the Contact equivalent.

In my image above, I have 3 contacts. This means the reference grid would have 7 rows/options:

Genevieve / Krissia / Mirka / Genevieve & Krissa / Genevieve & Mirka / Krissia & Mirka / Genevieve, Krissia, and Mirka

Let me know if I can help clarify anything further!

Cheers,

Genevieve

Hollie Green

From all the research I have done it is not possible to bring in multiple contacts with a formula. There is something about the contact column type that prevents this. It would be a great feature to be added. Below is the discussion I referenced to find the information.

https://community.smartsheet.com/discussion/71775/using-index-collect-to-return-multiple-values

A Rose

Thanks for that information @Hollie Green!

Would you know if it's possible that on the second sheet it gathers a list of Contacts of that sheet and then somehow bring it over to my summary sheet?

@Paul Newcome @Andrée Starå @Genevieve P.

Would anyone of you have the answer to this?

Thanks you! 🙂

Genevieve P.

Hi @A Rose

Hollie is correct that you currently cannot use a formula to combine contacts into one cell.

That said, we could bring back the contact values as text, if that would help? You can use a JOIN(COLLECT formula to join together all the values of a column into a multi-select drop-down, something like this:

=JOIN(COLLECT({Sheet 1 Range Contact Column}, {Sheet 1 Range Status Column}, "Approved", {Sheet 1 Range Reason Column}, HAS(@cell, Reason@row)), CHAR(10))

The CHAR(10) at the end separates the values in a multi-select cell.

See: Formula combinations for cross sheet references

Cheers,

Genevieve

A Rose

Thanks for that @Genevieve P.!

Your second formula is not working, I'm getting #INCORRECT ARGUMENT SET,

Here's what I want to get, I'll try to explain in detail;

Is there any way for me to get the list of contacts in a text version AND have the list in 1 column with 1 contact-group per row?

Meaning I want to see which contacts use which reasons,

So I have a list of reasons and a Multi-Select Contact column (this could be a text column if needed..), In the contact column I want to list all contacts matching the reasons listed in the reasons column.

To enhance it, if the above could be done, maybe once it's imported, I can convert the text to a contact by using a formula that connects to a text column and a 2nd Contact column, The text column has a formula to remove duplicate values of the imported contact list, then another 2nd contact column so that my original contact column can VLOOKUP and convert the text to contacts...?

I Appreciate any help I can get!

Genevieve P.

Hi @A Rose

My apologies! There was meant to only be one formula, I believe I didn't copy/paste it properly. Here's what I meant to suggest:

=JOIN(COLLECT({Sheet 1 Range Contact Column}, {Sheet 1 Range Status Column}, "Approved", {Sheet 1 Range Reason Column}, HAS(@cell, Reason@row)), CHAR(10))

This will then look at your Reason in the current row, then check the other sheet's Reason column. For any row where that same reason appears, it will bring together all the unique Contacts associated, as long as that row is also "Approved".

Here's an example of a source sheet:

Then the formula output:

There isn't a direct way to then convert the multi-select options back into Contacts using a formula.

Yes, you could use a VLOOKUP or INDEX(MATCH. In this instance you would need to have a reference sheet with all possible contact combinations. For example, a multi-select column with each possible combination on the left, and a contact column with the Contact equivalent.

In my image above, I have 3 contacts. This means the reference grid would have 7 rows/options:

Genevieve / Krissia / Mirka / Genevieve & Krissa / Genevieve & Mirka / Krissia & Mirka / Genevieve, Krissia, and Mirka

Let me know if I can help clarify anything further!

Cheers,

Genevieve

A Rose

Thank you so much @Genevieve P. !