Return a value using Index/Match having multiple matching criteria

vnjeffery96856 · 09/19/19

Hello Smartsheet Community,

Does anyone know if we are able to return a value using the Index/Match but match two criteria?

I have one sheet that is searching in the other sheet for two values in two different columns and will return a value in the third column. It will only return the value if the other two values match. The formula I am using:

=INDEX({Position Number}, MATCH([Job Title]5, {Job Title}, MATCH([Department Description]5, {Department Description}, 0)))

I keep getting an invalid value error. However, if I remove one of the MATCH criteria - it will return a value.

Any help is appreciated!

Thanks,

Veronica

L_123 · 09/19/19

This is not possible the way you have it written. Instead of index(match()match())you can use index(collect(),1)

=INDEX(collect({Position Number}, {Job Title}, [Job Title]5,{Department Description}, [Department Description]5),1)

https://help.smartsheet.com/function/collect

Andrée Starå · 09/19/19

Hi Veronica,

If I understand you correctly, you could also use two a helper column and the JOIN function on each sheet and match against that.

Would that work?

Hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

vnjeffery96856 · 09/24/19

All,

Thank you! Index/Collect worked like a charm:

=INDEX(COLLECT({POC}, {Job Title}, [Job Title]1, {Department Description}, [Department Description]1), 1)

Appreciate all your help!

-Veronica

Chizu Hieida · 07/15/22

https://community.smartsheet.com/discussion/comment/209631#Comment_209631

OMG!!! Thank you so much for this one!!! It worked for what I was trying to figure out also!!! 😍

Return a value using Index/Match having multiple matching criteria

Comments

Help Article Resources

Categories