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?


  • L_123
    L_123 ✭✭✭✭✭✭
    edited 05/27/20

    =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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!