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
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
Tags:
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
- 66.7K Get Help
- 438 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 36 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
Check out the Formula Handbook template!
Trending in Formulas and Functions
- Hi! I cannot figure this out. I have a "% Complete" column (0, 25, 50, 75, 90, 100, Cancelled). I made a simple checkbox column that should be checked if the item's "% Complete" value is equal to anyt…
- This formula keeps coming up as #UNPARSEABLE. I have checked the name of the columns, they are correct. Any ideas? I am trying to show the days remaining in a project. =IF([% Complete]@row = 1, "", NE…
- Hi All! I´m trying to find the error in this formula but have not been able to find the solution after many attempts. Any ideas? =IFERROR(IF([To Date]@row <> "", IF([To Date]@row < TODAY(), "RED", IF(…
