Index Match

Amy V

I am trying to use and Index/Match equation to pull data from one SmartSheet to another. My Course Dev Dashboard SmartSheet contains basic information for all projects that we currently have in development. Each of those projects has its own SmartSheet with details from the contract to track progress. I'm trying to use the Index Match formula on the project sheets so that the basic project data pulls directly from the Course Dev Dashboard and can be automatically updated.

Here is the formula I am using.

=INDEX({Course Dev Dashboard_Course Name}, MATCH($[Assigned to]$1, {Course Dev Dashboard_Course Number}))

The information that I am Matching are typically course numbers like En 102, or Ma 101. There are also a few special projects that don't have the same format. These numbers are all in the primary column in the Course Dev Dashboard.

The formula currently returns #No Match on most data (even when there is a match). It will find the data for only a few of the non-standard lines (such as "xxx" or 321). It also sometimes gives the wrong data.

Any ideas why this isn't working?

Paul Newcome

Does the [Assigned to] column have the course numbers in it?

You are definitely going to want to use a zero in the 3rd portion of the MATCH function to get an exact match.

MATCH($[Assigned to]$1, {Course Dev Dashboard_Course Number}, 0)

The non-standard rows... You say you have "321" in them but most other rows start with a letter? If that is the case then you have two different data types (text and numbers) which can throw things off. You are going to need to insert a helper column and use a column formula to convert all rows into a text value (on both the source sheet and the target sheet).

=[Column Name]@row + ""

Then you would reference these columns so that you are always referencing only text values.

Paul Newcome

Does the [Assigned to] column have the course numbers in it?

You are definitely going to want to use a zero in the 3rd portion of the MATCH function to get an exact match.

MATCH($[Assigned to]$1, {Course Dev Dashboard_Course Number}, 0)

The non-standard rows... You say you have "321" in them but most other rows start with a letter? If that is the case then you have two different data types (text and numbers) which can throw things off. You are going to need to insert a helper column and use a column formula to convert all rows into a text value (on both the source sheet and the target sheet).

=[Column Name]@row + ""

Then you would reference these columns so that you are always referencing only text values.

Amy V

Yes, the [Assigned to] column as the course number in it in the individual project sheets. The zero in the MATCH function definitely helps.

I didn't know it would cause an issue to mix numbers and text. I played around with things a bit and ended up adjusting my formula to index the "Course Name" column instead of the "Course Number" since the date in that column was more consistent.

One more question if you have a moment, I've tried linking the "Course Number" cell in the project sheet to the Course Dev Dashboard. When I link it though, the cells with the Index/MATCH equation all return "#No Match". Does INDEX/MATCH not work when referencing linked cell information?

Thanks so much for your help!

Amy V

I think I just answered my own question. I accidently linked a cell that was not in the INDEX/MATCH range. I have that fixed and the formulas are returning the right information now based on the linked cell.

Thanks again so much!

Paul Newcome

Happy to help. 👍️