Index Match returns "#No Match" for Project Manager but works for Project Sponsor

In Metadata, the Formula is basically the same between the two fields but Project Manager is returning #No Match and Project Sponsor works. The references are going to the correct places.

Can someone please help me find the discrepancy?

Thanks!

Tags:

Answers

  • Kathy PPT
    Kathy PPT ✭✭✭✭

    @Genevieve P. - this is related to the restoration of the Intake sheet I did. Should I go to Support for help?

  • Michelle Choate 2
    Michelle Choate 2 ✭✭✭✭✭✭

    Is the column a contact column?

    Michelle Choate

    michelle.choate@outlook.com

    Always happy to walk through any project you need help with!

  • Kathy PPT
    Kathy PPT ✭✭✭✭
    edited 10/25/24

    @michelle Choate 2, both the Project Manager and Project Sponsor are dropdown columns. Should they be contact?

  • Hi @Kathy PPT

    You will want the column types to match across sheets, so if the Project Manager column in sheet 1 is a dropdown, then it can be a dropdown in this current sheet. 🙂

    What I would personally double check is that the first row in the sheet you're referencing {in these} has the correct text in that header row. The text "Project Manager" will need to be letter-for-letter the same in order to bring back a match.

    I'll break down the formula for you:

    =INDEX( { sheet reference },

    this tells the formula to take a look at the entire sheet to bring back a specific cell, based on the next information you provide.

    =INDEX(sheet, MATCH( - Project ID - )

    The first match is now looking for the ROW that you want to get data from. How does it find the row? It takes a look at that Project ID to find the matching row with the same Project ID in the source.

    Great! But now our formula is looking at an entire row. We need to tell it what Column the cell is in.

    =INDEX(sheet, MATCH(row), MATCH(column name))

    Formulas can't reference a column name, so this final Match is looking at your helper top-row. This row repeats the column names so that the formula can read it properly.

    This does mean that in both sheets those column names will need to be identical, and repeated in the top row. Can you double check that the text is the same?

    If not, it would be helpful to see a screen capture of the row in the source sheet, showing the column names and helper top-row titles as well.

    Cheers,
    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Kathy PPT
    Kathy PPT ✭✭✭✭

    @Genevieve P. - turns out this was related to the re-installation of the Project Intake Sheet. I had to recreate the outbound links from the metadata to the Intake sheet. What a pain this has been!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!