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.


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!