Why won't Index and Match column formulas work past row 1 on destination sheet?

Ann.Cracovaner

I'm working on developing a PMO solution and have started with the suggested intake and portfolio metadata sheets. I have set up Index and Match formulas to pull information from the intake sheet into the metadata sheet and they worked beautifully for the first row of data. However, when I entered a few additional test rows, the data is not pulling over. (see below).

I have double checked the formulas and they seem to be ok. This is what I am using to pull the Scope Change ID column:

=INDEX({Scope Change ID}, MATCH([CO Number]@row, {March 2025 Intake Sheet}, 0))

Does anyone know why this isn't working and how to fix?

Jgorsich

Ahhh… per Functions List | Smartsheet Learning Center, here is how the Match() function works:

MATCH Function | Smartsheet Learning Center

the relevant bit is "Smartsheet calculates the relative position of a search value by counting cells from left to right (across columns) and top to bottom (across rows). In a lookup table of two columns, the cell in the top row of the leftmost column is the first position, 1.". They have some extra information in the examples that makes it a bit more clear.

To summarize what it does, picture a set of data like this:

column1 column 2 column 3

position 1 position2 position3

position4 position5 position6

position7 position8 position9

If you tell Match to find you what position your 3rd entry in column 1 is and you have it search column 1, 2, and 3, the answer is 7, not 3.

In your particular case, your formula is working on row one because the value it is looking for corresponds to position 1. Your formula fails on row two because now instead of your match() function pulling the value "2" you are getting something like "13" (your number of columns +1) and you don't have 13 rows of fake data.

Change {March 2025 Intake Sheet} to just reference your [CO Number] column in your intake sheet and you'll likely be in business.

for each of the subsequent columns, replace your {Scope Change ID} with the correct reference, but leave your match() function just looking at the two [CO Number] columns.

Jgorsich

That seems like it should work. If you can edit your references to {Scope Change ID} and {March 2025 Intake Sheet} and take screenshots of EXACTLY what you are referencing, I suspect the problem lies there.

Ann.Cracovaner

Thanks for the response! As you can see, I'm just referencing the whole Column on the source sheet.

Jgorsich

What about for {March 2025 Intake Sheet}?

What column is that pulling from?

Jgorsich

Oh, one other thought - sometimes there is significant lag in letting one sheet know that another has been updated. I've seen it take upwards of 5+ minutes. Closing all my sheets, then opening them and saving them in the order I expect it to update usually forces a refresh when the "refresh" button isn't getting the job done.

Ann.Cracovaner

Time and closing/reopening these sheets sadly didn't do the trick.

As for the March 2025 Intake Sheet reference, I selected all of the columns in the Intake sheet. That wouldn't all fit in one screenshot to show you as it is 11 columns.

Jgorsich

Ahhh… per Functions List | Smartsheet Learning Center, here is how the Match() function works:

MATCH Function | Smartsheet Learning Center

the relevant bit is "Smartsheet calculates the relative position of a search value by counting cells from left to right (across columns) and top to bottom (across rows). In a lookup table of two columns, the cell in the top row of the leftmost column is the first position, 1.". They have some extra information in the examples that makes it a bit more clear.

To summarize what it does, picture a set of data like this:

column1 column 2 column 3

position 1 position2 position3

position4 position5 position6

position7 position8 position9

If you tell Match to find you what position your 3rd entry in column 1 is and you have it search column 1, 2, and 3, the answer is 7, not 3.

In your particular case, your formula is working on row one because the value it is looking for corresponds to position 1. Your formula fails on row two because now instead of your match() function pulling the value "2" you are getting something like "13" (your number of columns +1) and you don't have 13 rows of fake data.

Change {March 2025 Intake Sheet} to just reference your [CO Number] column in your intake sheet and you'll likely be in business.

for each of the subsequent columns, replace your {Scope Change ID} with the correct reference, but leave your match() function just looking at the two [CO Number] columns.

Ann.Cracovaner

This seemed to be resolved while I was still designing my solution, but is now popping up again in testing. I had new row created on the intake sheet via form. But the data is not showing up on the destination sheet, even with the revised cell references.