Searching with INDEX(MATCH(

Dakota Haeffner

Hello,

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?

Jason Tarpinian

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))

Paul Newcome

Are you able to provide screenshots for context?

Jason Tarpinian

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))

Dakota Haeffner

@Jason Tarpinian This did it! I replaced your []:[] with {} to reference another sheet but it worked overall. Well done and thank you for the assistance.

@Paul Newcome Sorry I usually include those my mistake! Jason's solution did it for me.