Searching with INDEX(MATCH(


I am working on building a portfolio level sheet that can operate as a searching tool within Smarthsheets and I'm looking for a little assistance to create a method to find based on key words rather than exact matches. Is this possible? What would the returns look like if there were multiple values?

Also the below formula is only pulling the parent row, can index match search children as well?

If I use the formula: =INDEX({DJLIPP - TN}, MATCH([Search Value]@row, {DJLIPP - TN}))

DJLIPP - TN being the project plan to search

[Search Value]@row being what project they are searching for

..are any of the above questions achievable?


Best Answer

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭
    edited 08/23/23 Answer ✓

    The COLLECT function can sort of work like INDEX, and is especially useful for multiple matches as you mention. The formula below will return all of your values that contain the word "Banana" and separate out multiple matches by using the combination of JOIN and CHAR(10) - line break. Make sure that you wrap text on your formula cell so that multiple matches flow to the next row.

    =JOIN(COLLECT([DJLIPP - TN]:[DJLIPP - TN], [DJLIPP - TN]:[DJLIPP - TN], CONTAINS([Search Value]@row, @cell)), CHAR(10))

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!