How to create Nested IF formulas with INDEX / MATCH with CONTAINS, HAS, or...?

mistone

The workflow:

1. Customers fill out a form to give us information.

2. After a row is added from a form completion:

3. I need a column formula to return in the yellow cell the data in the green cell that is in a different sheet.

So I'm trying to reference the name entered on the form and, find the name on a different sheet, then return data on from row where that name is.

In this example the yellow cell would return "Full Moon Hike"

The problem:

The cell where the name is entered on the referenced sheet may contain more than 1 name. This will not always be the case but will happen on a frequent basis

The Question:

What is the formula I need to accomplish this? I have tried IF(CONTAINS with INDEX MATCH and few other ideas, and I'm stumped. Any suggestions are welcome.

 Cheers,

Mike

Paul Newcome

You will need an INDEX/COLLECT.

=INDEX(COLLECT({Itinerary}, {Name}, CONTAINS("Kermit", @cell)), 1)

Paul Newcome

@mistone Happy to help. 👍️

Paul Newcome

You will need an INDEX/COLLECT.

=INDEX(COLLECT({Itinerary}, {Name}, CONTAINS("Kermit", @cell)), 1)

mistone

Thanks! this worked. I appreciate the assist.

Paul Newcome

Happy to help. 👍️

mistone

@Paul Newcome -

I'm continuing to develop the workflow above. The first formula search for the a name in 1 column. That worked, but now I realize I have to separate the names and emails and have 1 per cell. So now I have 3 name columns and 3 email columns.

Is there away to expand the formula to search the name in multiple columns and return the Itinerary?

Any guidance is much appreciated

Paul Newcome

Is it possible you could end up outputting multiple itineraries?

mistone

I think the answer is no. Any name / email will be associated with 1 itinerary that is on the row where the names and emails are listed. I may end up with instance where I have multiple names / emails entered on a given row.

Paul Newcome

If that is the case then shouldn't you really only need to pull based on one such as just the first one?

Or is there the possibility that Pig Pen is not in the reference list at all and you need to search for Miss Piggy?

mistone

The second. Maybe it could be an if statement?

Might be implied, on any given roster of participants for our events we could have anywhere from 1 to 3+ people have "alerts." when they do this is what I'm trying to accomplish:

1. I enter their name(s) and email(s) (in this example, Pig Pen & Missy Piggy) in the source sheet called Beta 3.0. This:
2. triggers an email workflow sending an email to Pig Pen & Missy Piggy asking them to complete a click a link to fill out a smartsheet med form
3. Pig Pen & Missy Piggy fill out the med form
4. A row is added to med alert sheet (that the form is built on) with the Pig Pen & Missy Piggy's responses.
5. I'm trying to create column formula on the med alert sheet that indexes information on the source sheet (Beta 3.0) matching to Pig Pen & Missy Piggy to pull the start date, time, & itinerary from the row on the source sheet where I entered their names in step one into the med alert sheet.

So If there is only 1 Med alert on a roster, then I'd only use med alert name 1 and med alert email 1 fields. If there are 2 then I'd use the additional fields. So Med alert name 1 and Med alert email 1 will be used the most, and 2 and 3 will be used when there are more than 1 alert on a roster.

Does this make sense?

Paul Newcome

But from my understanding, Med Alert 1 and Med Alert 2 will (if on the same row) have the same itinerary, and Med Alert 1 will never be blank?

mistone

Yes, if there is a med alert on a roster, then Med Alert 1 always be used. If there are more than 1 then I'd push onto 2 and 3. If there are no med alerts, then the all the Med alert fields will be left blank.

Paul Newcome

I do understand that. But if there is a 2 or 3, they would have the same itinerary as 1, right?

mistone

Yes they would. So on the Medical alert sheet - each person will have their own row, so then I'd like the formula to find the name on the source sheet and return the start date, time, and

One option is to use the formula you gave me and create several helper columns - Med Alert Itinerary 1, 2, and 3 and then pull for INDEX/COLLECT on columns 1, 2, & 3. From there I could create JOIN formula to pull the data that is scattered on the helpler columns get everything into one column. Here is a screen shot of the med alert sheet. (The Formulas don't work.) and the source sheet.

Paul Newcome

I may be confused then. Which one is which in your latest screenshots?

mistone

@Paul Newcome - Apologies. Here are the accurate screen shots. The 1st one is the sheet that will populate from the form that customers complete. The bottom is the source sheet where I enter their name and email. As we've discussed - the goal is to pull the start date, time, Itinerary, & guide from the source sheet. Med Alert 1 on the source sheet will always be used 1st, followed by 2 and 3 if needed.

I have singular formula built to pull Med Alert 1 data, so that is why the details Pig Pen is there.

Paul Newcome

Ok. I had it backwards. I thought we were going from the first sheet to the second sheet which is why it confused me why you wanted to look at all three contacts.

You will need three separate INDEX/COLLECTs that can be joined together via IFERROR statements.

=IFERROR(IFERROR(INDEX(COLLECT({Itinerary}, {Name}, CONTAINS([Med Alert Name 1]@row, @cell)), 1), INDEX(COLLECT({Itinerary}, {Name}, CONTAINS([Med Alert Name 2]@row, @cell)), 1)), INDEX(COLLECT({Itinerary}, {Name}, CONTAINS([Med Alert Name 3]@row, @cell)), 1))