Pull data from one sheet into another using Index / match / contains

Dan123

Hi everyone. I have two sheets and need to pull from one to the other. One sheet lists widgets and data about them, the second lists press releases and includes {press release tracking Range 2}, which is a column listing all of the widgets listed in that press release.

So, the widget sheet is:

Widget Press release mentioned?

Widget 1 formula here

Widget 2 formula here

Widget 3 formula here

The press release sheet includes this sort of data:

Press release

Press release 122 Widget 1, Widget 3

Press release 125 Widget 2, Widget25

In the "formula here" section, I currently have =INDEX({press release tracking Range 1}, MATCH(widget@row, CONTAINS(widget@row, {press release tracking Range 2}))) but this gives me a #NO MATCH error. The expected behavior would be for the "widget sheet" to return "Press release 122" in the "formula here" cell for "widget 1". I'm not sure if INDEX, MATCH is my best bet, but I cannot find another way. Really hoping someone can help me out!

Genevieve P.

Hi @Dan123

Is it possible that you may have a Widget associated with more than one Press Release?

Instead of an INDEX(MATCH, try using a JOIN(COLLECT. If there's only one Press Release it will bring one back. If there are more than one with this widget associated, then it will bring back all of them:

=JOIN(COLLECT({press release tracking Range 1}, {press release tracking Range 2}, CONTAINS(widget@row, @cell)), " / ")

If this doesn't find a match, what type of column is the Widget column in your other sheet? Is it a Multi-Select column? If so, we should use the HAS function instead of CONTAINS:

=JOIN(COLLECT({press release tracking Range 1}, {press release tracking Range 2}, HAS(@cell, widget@row)), " / ")

If this still doesn't work, it would be helpful to see screen captures of both sheets, but please block out sensitive data.

Cheers,

Genevieve

Emily Zeiger

Try =INDEX({press release tracking Range 1}, MATCH(widget@row, CONTAINS(widget@row, {press release tracking Range 2}, 0))), if still a no match, try =INDEX({press release tracking Range 1}, MATCH(widget@row, {press release tracking Range 2}, 0))

Dan123

Hi Emily,

Thank you for these suggestions. The first formula gives me an "#Incorrect argument set". The second gives me a "#no match". Any other ideas?

I feel like this should be possible, but the formula is certainly elusive!

Could I maybe use "Find" instead of "Contains"?

Genevieve P.

Hi @Dan123

Is it possible that you may have a Widget associated with more than one Press Release?

Instead of an INDEX(MATCH, try using a JOIN(COLLECT. If there's only one Press Release it will bring one back. If there are more than one with this widget associated, then it will bring back all of them:

=JOIN(COLLECT({press release tracking Range 1}, {press release tracking Range 2}, CONTAINS(widget@row, @cell)), " / ")

If this doesn't find a match, what type of column is the Widget column in your other sheet? Is it a Multi-Select column? If so, we should use the HAS function instead of CONTAINS:

=JOIN(COLLECT({press release tracking Range 1}, {press release tracking Range 2}, HAS(@cell, widget@row)), " / ")

If this still doesn't work, it would be helpful to see screen captures of both sheets, but please block out sensitive data.

Cheers,

Genevieve

Dan123

First formula worked. Thanks!

Genevieve P.

No problem!

Ipshita

I need help in inserting INDEX MATCH formula to update values in a date column in a sheet from a target sheet that's getting uploaded with the help of data shuttle. I tried cell linking but that disappears every time a fresh workflow runs. The idea is to update construction start and complete dates in multiple sheets from the construction start and end dates from one target sheet, something as below -

Target Sheet Project Tracker 1 Project Tracker 2

Site Name Start Date End Date Site Name Start Date End Date Site Name Start Date End Date

Houston

Dallas

Austin

Genevieve P.

Hi @Ipshita

Do you have a formula you've already tried that you can post?

An INDEX(MATCH works like this:

=INDEX({Column to return}, MATCH("Matching Value", {Column with value to match}, 0))

Or in your case, something like:

=INDEX({Start Date Column Tracker 1}, MATCH([Site Name]@row, {Site Name Column Tracker 1}, 0))

See: Formula combinations for cross sheet references

Cheers,

Genevieve

Ipshita

Hey @Genevieve P.

Thank you so much for referencing the formula for me. Ideally, this should work in any given similar scenario but in my case it's returning as #UNPARSEABLE. Just so you know, the sheet "Brian Date" file in my case is a sheet that's getting uploaded into Smartsheet via Data Shuttle method and the date columns here are DATE type columns. Also, I am unable to insert a formula in the Start Date column. I can only insert a formula in the actual end date column in my target sheet. We have restrictions on sharing actual data but I'll try to add a few screenshots for this one.

Here is the formula I'm using -

=INDEX({Brian Date Preso Range 1}, MATCH({Brian Date Preso Range 2}@row, {Brian Date Preso Range 4}, 0))

where =INDEX({Brian Date Preso Range 1} - is the date column (in this case Design Complete Date)

MATCH({Brian Date Preso Range 2}@row - is the value "Birmingham" from the Site Name column, and

{Brian Date Preso Range 4}, 0)) is the Site Name column

Genevieve P.

Hi @Ipshita

The formula is giving you an error because of the first part of your MATCH formula:

{Brian Date Preso Range 2}@row

This should instead be something like

[Site Name]@row

Referencing a cell in the current sheet instead of in a different sheet:

=INDEX({Brian Date Preso Range 1}, MATCH([Site Name]@row, {Brian Date Preso Range 4}, 0))

Does that make sense? You would need to have at least one column in this second sheet that will always be updated with the value you're looking to match across sheets.

Cheers!

Genevieve

Ipshita

This "Brian Date Preso" is the actual source sheet from where I'm getting the relevant data "Design Complete" date for row number 15, in the Actual Finish Date cell in my target sheet which is "Birmingham" (attached below) -

I am referencing to the relevant site name in the source file and adding the @row to it in the formula for MATCH()

and then selecting the entire column "Site Name" from the Source Sheet again. Is this incorrect?

Genevieve P.

Hi @Ipshita

Thank you for this information!

Yes, so the part that's mixing up your formula is "Birmingham".

It sounds like the easiest thing to do would actually be to hard-code that word into your formula instead of referencing it across sheets.

Try:

=INDEX({Brian Date Preso Range 1}, MATCH("Birmingham", {Brian Date Preso Range 4}, 0))

Or if there's a cell in your "Birmingham Cage 1" sheet, you can use that cell instead:

=INDEX({Brian Date Preso Range 1}, MATCH([Task Name]$1, {Brian Date Preso Range 4}, 0))

Does this help?

Ipshita

It worked! Thank you! But we did try this before with the only difference that we didn't add the 0 at the end. But thank you!!

Genevieve P.

Glad to hear that it worked! 🙂