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

mistone

@Paul Newcome - I've been distracted by other work and now back on this project. I get an #UNPARSABLE error when I cut and paste the formula. I think it is because I need reference the Beta 3.0 Sheet columns. I'm 100% guessing right now and I tried this:

=IFERROR(IFERROR(INDEX(COLLECT({Beta 3.0 - Itinerary Col},{Beta 3.0 - Med Name 1}, CONTAINS([First and Last Name@row, @cell)), 1), INDEX(COLLECT({Beta 3.0 - Itinerary Col}, {Beta 3.0 - Med Name 2}, CONTAINS([First and Last Name]@row,@Cell,1)), INDEX(COLLECT(({Beta 3.0 - Itinerary Col}, {Beta 3.0 - Med Name 3}, CONTAINS([First and Last Name]@row, @cell)), 1))

I guess my questions are:

COLLECT{Itinerary} = is this is the itinerary column data I want to return from the Beta 3.0 sheet?

{Name} = is that the First and Last Name Column on the Medical Alert Form V2.0 sheet or the Med Alert Name 1, 2, or 3 from the Beta 3.0 Sheet?

CONTAINS([Med Alert Name 1]@row, @cell)), 1) = Is the Med Alert Name 1 from Beta 3.0 or is this the First Last Name Med Alert Form V2.0 sheet? (I don't understand the syntax here, so I'm just guessing).

I think if I get 1 of the INDEX collect syntax correct I can simply replicate the other 2 in the formula changing the reference columns.

Thanks,

Mike

Paul Newcome

Let's start with cleaning up some syntax issues. What does this do?

=IFERROR(IFERROR(INDEX(COLLECT({Beta 3.0 - Itinerary Col},{Beta 3.0 - Med Name 1}, CONTAINS([First and Last Name]@row, @cell)), 1), INDEX(COLLECT({Beta 3.0 - Itinerary Col}, {Beta 3.0 - Med Name 2}, CONTAINS([First and Last Name]@row,@cell)),1)), INDEX(COLLECT({Beta 3.0 - Itinerary Col}, {Beta 3.0 - Med Name 3}, CONTAINS([First and Last Name]@row, @cell)), 1))

mistone

@Paul Newcome -

Amazing! it is working. I've added data to test and so far so good. Sometimes Med Alert Form sheet is slow update after saving and refreshing, but all seems to be working.

Paul Newcome

@mistone Happy to help. 👍️

mistone

@Paul Newcome -

I'm making progress and have replicated the formula to pull additional columns from the beta 3.0 to the Medical Form V2.0. My problem now is that the guide(s) will only populate as a text name and not a contact. The column is formatted as contact list to match the column format in the beta 3.0, but I only get the text and not the contact with circle icon and email.

I need / want the guide contact to come over so I can create auto workflows from this sheet. In an earlier version of this sheet I had it working. Any suggestions on how to solve this one???

Paul Newcome

The indexed reference should be pulling from the contact column and the column being pulled from as well as the column housing the formula should both be set as contact type columns.

=IFERROR(IFERROR(INDEX(COLLECT({Beta 3.0 - Itinerary Col},{Beta 3.0 - Med Name 1}, CONTAINS([First and Last Name]@row, @cell)), 1), INDEX(COLLECT({Beta 3.0 - Itinerary Col}, {Beta 3.0 - Med Name 2}, CONTAINS([First and Last Name]@row,@cell)),1)), INDEX(COLLECT({Beta 3.0 - Itinerary Col}, {Beta 3.0 - Med Name 3}, CONTAINS([First and Last Name]@row, @cell)), 1))

mistone

Got it thanks...it was stupid error...I needed to change the format to allow for multiple contacts per cell...DUH!