Index match with Left

I'm trying to make an Index/Match formula and I can use some help.

I have a Smartsheet with a list of parts (Sheet1). I also have a Smartsheet that has a list of part prefixes and a contact for parts beginning with that prefix (Sheet2).

I would like to do an Index match on my parts smarsheet (sheet1) so that any parts beginning with the letters/numbers referenced on sheet 2 return the contact from sheet 2. I don't want to use CONTAINS, since I only want the prefix of one sheet to match

Find more posts tagged with

Formulas

INDEX

Accepted answers

All comments

Paul Newcome

What is your question?

BlueQuestion

@Paul Newcome - Sorry about that! I'm having trouble figuring out what formula to use and how to set it up.

I'm thinking I might need to use Index match with a Left function, but I can't seem to get it to work. Basically, I want the formula to look at my part number and tell me who the contact is, based on the prefix of the part. My sheet 2 is the "database" of prefixes and contact names, I just need to know how to set up the formula.

Paul Newcome

The update with details and screenshots definitely help. Give something like this a try:

=INDEX({Contact}, MATCH(LEFT([Part Number]@row, 2), {Prefix}, 0))

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.