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

Options
2»

Answers

  • mistone
    mistone ✭✭✭✭
    Options

    @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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    mistone ✭✭✭✭
    Options

    @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.



  • mistone
    mistone ✭✭✭✭
    edited 05/03/23
    Options

    @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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    mistone ✭✭✭✭
    Options

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!