Need help with Index formula across sheets

webrock6455

I have a Master sheet that I would like to source information to subsheets for Project columns, of which then the % complete and date complete on each subsheet gets Indexed back to the Master sheet. However, I am needing to add 2 lines on the Master sheet to populate on all of the subsheets, but when I assign them unique Row ID's, it causes all of the subsheets to break.

Column formula on each subsheet:

=INDEX({FL Project Mgmt Master Range 2}, MATCH([Row ID]@row , {FL Project Mgmt Master Range 1}), 0)

First picture is Master sheet with the new additions, the subsheets have blank rows in the correct spots.

Second picture is adding the new unique Row ID's

Third picture is one of the subsheets after the new Row ID's populate

Please help!

Isaac A.

Hi @webrock6455!

This is a great question, and I can see exactly what's happening from your screenshots!

The issue is with your MATCH function and the sort order of your Row ID column.

Your current formula is:=INDEX({FL Project Mgmt Master Range 2}, MATCH([Row ID]@row , {FL Project Mgmt Master Range 1}), 0)

The problem is that the 0 should be inside the MATCH function, not at the end of the INDEX function.

When you added Row IDs 108 and 109, your Row ID column on the Master sheet became unsorted (...10, 108, 109, 11...). The MATCH function, by default, assumes a list is sorted.

You need to add a 0 inside the MATCH function for the [search_type]. This 0 makes sure the formula knows the column is not sorted in a specific order, so it will look through the whole column for an exact match.

As is mentioned in this community thread, "you’ll need to make sure that 0 is there at the end of the MATCH function because it identifies that the values aren’t sorted in the other sheet.”

Here is the corrected formula. Just move that 0 to the end of your MATCH function:

=INDEX({FL Project Mgmt Master Range 2}, MATCH([Row ID]@row , {FL Project Mgmt Master Range 1}, 0))

Here is the Help Center article for the MATCH function if you want to read more about that [search_type] argument: MATCH Function.

Hope that helps!

Cheers,
Isaac.