Index Match with "Starts with" match criteria

I'd like to pull in a value from a separate sheet if that value starts with "xyz" (for example) and also matches a specific project ID.

For example, if I have "xyz" as a value in one of the columns on spreadsheet 1. On spreadsheet 2, the value is called "xyz123." The two values have matching project IDs. How can I pull "xyz123" into a separate column on spreadsheet 1?

The value I'm trying to pull is also a child in a hierarchy. Does that factor into why I might be having difficulty?

I've tried to do this with LEFT and/or CONTAINS functions, but am not getting the result I need.


Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @mkilroy

    CONTAINS is exactly what I'd suggest, using an INDEX(COLLECT formula. Try something like this:

    =INDEX(COLLECT({Column with xyz value to return}, {Project ID Column}, [Project ID]@row, {Column with xyz value to return}, CONTAINS("xyz",@cell)), 1)

    You'll notice we repeat the same range, the xyz range. The first range listed after COLLECT is the column to bring back. Then you list the column and criteria afterwards... so since the column to return has a criteria (containing xyz), we list it again later on.

    Here's information about this type of formula: Formula combinations for cross sheet references

    Let me know if this works for you!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!