How can I create an INDEX and MATCH with 4 conditions?

Options

I am creating a Tracking Sheet where the vendors are assigned to specific team members. In the Tracking Sheet (Sheet #1), there are 2 columns to look at: Coverage Method and Service Provider. If the Coverage Method meets any of 4 criteria, then it should look at the Vendor Sheet (Sheet #2) and return the correct team member, if the Service Provider@row in Sheet #1 matches the Vendor column in Sheet #2. If the Coverage Method does NOT meet one of the 4 conditions, then the formula should return "N/A".

SO:

In each Tracking Sheet, I need to create a formula to show:

In Sheet #1, if Coverage Method is "A" or "B" or "C" or "D", then the formula should look at the {Sheet #2 - VR Manager} and MATCH if the Service Provider cell in Sheet #1 matches the {Sheet #2 - Service Provider}, otherwise it should return "N/A".


I have tried the following:

=INDEX({Vendor Directory Range Owner}, MATCH([Service Provider]@row, {Vendor Directory Range Vendor Name}, 0), IF([Coverage Method]@row = "COMPANY Manage - Scheduled Maintenance Contract", IF([Coverage Method]@row = "COMPANY Manage - Vendor Contract", IF([Coverage Method]@row = "Special Coverage", IF([Coverage Method]@row = "Hybrid Coverage"))), "N/A"))


It either returns "INVALID DATA TYPE" or "UNPARSABLE" . Any help?

Constance Fetter (she/her/elle)

Tags:

Best Answer

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 11/11/23 Answer ✓
    Options

    Hi @Constance Fetter

    The INDEX function's syntax is as follows;

    INDEX( range row_index [ column_index ])

    The structure of the formula should be as follows;

    IF ( OR( conditon1, condition2, condition3, condtion4) , INDEX(MATCH()), "N/A")

    So

    For readability:

    =IF(OR(

    [Coverage Method]@row = "COMPANY Manage - Scheduled Maintenance Contract",

    [Coverage Method]@row = "COMPANY Manage - Vendor Contract",

    [Coverage Method]@row = "Special Coverage",

    [Coverage Method]@row = "Hybrid Coverage"

    ),

    INDEX(

    {Vendor Directory Range Owner},

    MATCH([Service Provider]@row, {Vendor Directory Range Vendor Name}

    , 0))

    , "N/A")


    =IF(OR([Coverage Method]@row = "COMPANY Manage - Scheduled Maintenance Contract", [Coverage Method]@row = "COMPANY Manage - Vendor Contract", [Coverage Method]@row = "Special Coverage", [Coverage Method]@row = "Hybrid Coverage"), INDEX({Vendor Directory Range Owner}, MATCH([Service Provider]@row, {Vendor Directory Range Vendor Name}, 0)), "N/A")


Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @Constance Fetter

    Try this

    =IFERROR(INDEX(COLLECT({Vendor Directory Range Owner},{Vendor Directory Range Vendor Name},OR(@cell="COMPANY Manage - Scheduled Maintenance Contract", @cell="COMPANY Manage - Vendor Contract", @cell="Special Coverage", @cell="Hybrid Coverage")),1),"N/A")

    Will this work for you?

    Kelly

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp ✭✭✭✭✭✭
    edited 11/11/23 Answer ✓
    Options

    Hi @Constance Fetter

    The INDEX function's syntax is as follows;

    INDEX( range row_index [ column_index ])

    The structure of the formula should be as follows;

    IF ( OR( conditon1, condition2, condition3, condtion4) , INDEX(MATCH()), "N/A")

    So

    For readability:

    =IF(OR(

    [Coverage Method]@row = "COMPANY Manage - Scheduled Maintenance Contract",

    [Coverage Method]@row = "COMPANY Manage - Vendor Contract",

    [Coverage Method]@row = "Special Coverage",

    [Coverage Method]@row = "Hybrid Coverage"

    ),

    INDEX(

    {Vendor Directory Range Owner},

    MATCH([Service Provider]@row, {Vendor Directory Range Vendor Name}

    , 0))

    , "N/A")


    =IF(OR([Coverage Method]@row = "COMPANY Manage - Scheduled Maintenance Contract", [Coverage Method]@row = "COMPANY Manage - Vendor Contract", [Coverage Method]@row = "Special Coverage", [Coverage Method]@row = "Hybrid Coverage"), INDEX({Vendor Directory Range Owner}, MATCH([Service Provider]@row, {Vendor Directory Range Vendor Name}, 0)), "N/A")


  • Constance Fetter
    Options

    Thank you! That works.

    Constance Fetter (she/her/elle)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!