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

Kathy PPT

Index Match issue - Project Manager.PNG

Index Match issue - Project Sponsor.PNG

Index Match issue - References.PNG

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!

Genevieve P.

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

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!

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

Is the column a contact column?

Kathy PPT

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

Genevieve P.

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

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!