Partial Match in an INDEX/MATCH

Options

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

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    Hi @Hannah H 

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

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hannah H
    Hannah H ✭✭✭✭✭
    Options

    Hi @Bassam.M Khalil,

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!