Use index, match and left to match a set of strings from another sheet

Jared P

Hello,

I am using the formula below in order to match two data sets together. It works fine.

For my next task I am trying to limit the match to only the first 12 characters of the string. I am trying to fit in the left function and using 12 as the character count, but I am getting errors.

Here is the current formula I am using and this works to pull data from a different sheet into my current sheet and have that data match the correct roll number.

=INDEX({Wet End Log T2-Gap}, MATCH([Roll #]@row , {Wet End Process Log Treater 2-FM7500.00 Range 2}, 0))

I need the match function to only match against the first 12 characters and not try to match anything past that.

What I tried is the following.

=INDEX({Wet End Log T2-Gap}, MATCH(left({Wet End Process Log Treater 2-FM7500.00 Range 2},12)[Roll #]@row , {Wet End Process Log Treater 2-FM7500.00 Range 2}, 0))

Any help using left or another function along with match would be great.

Thank you!

Cassie L

@Jared P Smartsheet formulas don’t support array transformations like Excel does, so you can't apply LEFTdirectly to a range inside MATCH

However, you can use helper columns

1. In your source sheet (Wet End Process Log Treater 2-FM7500.00), create a new column called something like Roll # (First 12).
2. In that new column, use the formula: =LEFT([Roll #]@row , 12)
3. Update your formula to: =INDEX({Wet End Log T2-Gap}, MATCH(LEFT([Roll #]@row, 12), {Roll # (First 12)}, 0))
4. Replace {Roll # (First 12)} with the actual cross-sheet reference to the new helper column.

Source: Copilot