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
Check out the Formula Handbook template!