Use Wildcards in an INDEX MATCH formula

Jose Rivera

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?

L_123

=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.