Formula to match and pull in information across multiple rows

Raquel Love

I want to create a formula in Sheet 1 that looks at the information in a column in Sheet 1 and searches for matches in a specific column in Sheet 2. If a match is found in that column in any of the rows in Sheet 2, the formula will pull in information from another column in all rows in Sheet 2, not just from the row of the first match.

Formula steps:

Sheet 1 - looks at information in the Facility Name column

Sheet 2 - looks to Sheet 2 to find any matches across all rows in the Facility Name column

Sheet 2 - if there's a match, looks at the Parent Name column in each row where there's a match

Sheet 1 - brings back the Parent Name column information from all matches in Sheet 2 and populates in the Parent Name column in Sheet 1 (comma separated if there are multiple results)

I don't know if anything like this is even possible, but if so would love to try out some formulas to see if they'll work here!

brianw

Hi Raquel

Have you tried using an INDEX(MATCH function? One of the admins helped me with something similar. Here's another Community Post that goes in to more detail that she had provided to me that helped.

CAN Program Team

Hi Raquel,

Were you able to figure this out? The community post linked above doesn't seem to explain how to get Index Match to return values from multiple matches in a sheet!

Genevieve P.

Hi @CAN Program Team

If you have multiple matches, INDEX(MATCH will return the first matching value.

An alternative would be to use JOIN(COLLECT to bring back all of the matching values into one cell, or you could use INDEX(COLLECT to filter down your criteria to ensure there's only one match.

Here's a help article that you may find useful: Formula combinations for cross sheet references

Cheers,

Genevieve