Return a value using Index/Match having multiple matching criteria

vnjeffery96856

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

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