Return a value using Index/Match having multiple matching criteria
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
Comments
-
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
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
-
OMG!!! Thank you so much for this one!!! It worked for what I was trying to figure out also!!! 😍
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!