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

amber.lange

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.

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.

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!