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

Constance Fetter

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?

jmyzk_cloudsmart_jp

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")

https://app.smartsheet.com/b/publish?EQBCT=aaafc027f0344c6fb425a00b4e97f7c4

Kelly Moore

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

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")

https://app.smartsheet.com/b/publish?EQBCT=aaafc027f0344c6fb425a00b4e97f7c4

Constance Fetter

Thank you! That works.