Index Match with "Starts with" match criteria

mkilroy

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.

Thanks!

Genevieve P.

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!

Genevieve

Genevieve P.

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!

Genevieve

mkilroy

Thank you so much!! This worked great :)

Genevieve P.

Wonderful! I'm glad I could help.