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?

Find more posts tagged with

Sheets

Formulas

Solution Center

functionality

Update Requests

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.