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

Dan123
Dan123 ✭
in Formulas and Functions

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!

Tags:
· Share on FacebookShare on Twitter

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

    · Share on FacebookShare on Twitter

Answers

  • Emily Zeiger
    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))

    · Share on FacebookShare on Twitter
  • Dan123
    Dan123 ✭
    edited 09/02/21

    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"?

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

    · Share on FacebookShare on Twitter
  • Dan123
    Dan123 ✭

    First formula worked. Thanks!

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    No problem!

    · Share on FacebookShare on Twitter
  • Ipshita
    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

    Ipshita Mukherjee

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    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

    · Share on FacebookShare on Twitter
  • Ipshita
    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

    Ipshita Mukherjee

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    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

    · Share on FacebookShare on Twitter
  • Ipshita
    Ipshita ✭✭✭✭✭✭
    edited 08/18/22

    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?

    Ipshita Mukherjee

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    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?

    · Share on FacebookShare on Twitter
  • Ipshita
    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!!

    Ipshita Mukherjee

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin

    Glad to hear that it worked! 🙂

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!