Can I use Index/Match and Left functions in one formula?

Hello, can someone help me with the left function within the index/match? This is what I tried but it always returns NO MATCH:

=INDEX({Master Job SO}, MATCH(LEFT([Job Number]@row,5), {Master Job #}, 0))

I also tried creating a helper column, Job Prefix, using =LEFT([Job Number]@row, 5) and using that result in the SO column formula, but it doesn't work. If I manually type my prefix, a regular INDEX/MATCH formula works, so I think the data exists correctly in the other sheet.

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

There we go. The [SO] column looks like it is being manually populated which is storing the data as numerical data, but the LEFT function outputs text.

Insert a column on the reference sheet and use

=SO@row + ""

plus quote quote

This will convert all entries into a text string so that data types match. You would then match on this helper column in your INDEX/MATCH.

All comments

Paul Newcome

Can you provide a screenshot of the reference table?

amber.lange

Thanks!

Paul Newcome

There we go. The [SO] column looks like it is being manually populated which is storing the data as numerical data, but the LEFT function outputs text.

Insert a column on the reference sheet and use

=SO@row + ""

plus quote quote

This will convert all entries into a text string so that data types match. You would then match on this helper column in your INDEX/MATCH.

amber.lange

Paul, so helpful! I actually needed a Job # helper column since on the job numbers are manually entered as well and is what determines what SO # is returned as a result.

Thank you very much!

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.