Partial Match in an INDEX/MATCH

Hi,

I currently have an INDEX/MATCH that is pulling in a variety of data from sheet A into sheet B. I am using the Freelancer's name to match across the sheets.

The problem I am facing is that in sheet A, an internal employee adds the Freelancer's name. In sheet B, the Freelancer adds their own name (via a form) and when these aren't identical (because a nickname or an initial might be used), the data from sheet A doesn't pull across.

Is there a way I can create a partial match? I know Smartsheet doesn't support wildcards, so I think CONTAINS is my only option? Can I build a formula that would just look at the last name of the Freelancer? Although I wouldn't know the length of the last name...

Any help much appreciated!

Hannah

Find more posts tagged with

Accepted answers

All comments

Bassam Khalil

Hi @Hannah H

Hope you are fine, could you please supply a screenshot ( remover any sensitive data )

Hannah H

Hi @Bassam.M Khalil,

Screen Shot 2021-02-17 at 8.21.20 AM.png

Here is an example of the columns I'm using in both sheets.

My current formula is =IFERROR(INDEX({Requested By}, MATCH([Freelancer's Name]@row, {Freelancer's Name}, 0)), "")

It works great if I have a perfect match on the names, but not if people use a nickname, for instance.

Hannah

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.