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!