Use Wildcards in an INDEX MATCH formula
Hi everyone,
I need to use INDEX MATCH formula to get a DS# with a CNDT# in another report.
The easy formula would be: =INDEX({DS Report Range 2}, MATCH([CNDT Number]1, {DS Report Range 3}, 0)), "") but the thing is that sometimes there are more than one CNDT#'s in a single cell. Since INDEX MATCH looks for the exact match I would like to know a possible way to use wildcards like in Excel.
I found a workaround in another discussions in the forum and came up with the following formula using FIND, JOIN and COLLECT: =INDEX({DS Report Range 2}, MATCH(JOIN(COLLECT({DS Report Range 3}, {DS Report Range 3}, FIND([CNDT Number]1, @cell) > 0)), {DS Report Range 3}, 0))
It did work but it couldn't recognize the other CNDT#'s, see below the screenshots:
 Data Source report (Multiple CNDT's in a cell)
 Formula report (It only found 1)
Is it possible to use another method to use wildcards like in Excel?
Answers

=index(COLLECT({DS Report Range 2}, {DS Report Range 3}, contains([CNDT Number]1, @cell)),1
your join() was merging the array being held by the collect, so your match was only looking at one result, and returning a one. Your formula would always return the first value out of the indexed range.
You should name your ranges, it really helps with understanding/troubleshooting/upgrading formulas in the future.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!