#INVALID VALUE on index and match function

Gregory Zalecki

I am using an index and match function to show how much weight someone has lost each week. I was able to get the values for Week 1, but I cannot produce beyond that. I am copying over the formula and it is still pulling based off of name and week number but getting #INVALID VALUE.

Paul Newcome

It is because your second MATCH statement is generating a column number for the INDEX statement. The reason it works for the first week is because you are generating a 1 for the column number. Since you are only referencing a single column in your INDEX function, this works. When you move to week two, you are telling the INDEX function to pull from column 2 of only 1 selected.

You are going to want to try something more along the lines of an INDEX/COLLECT similar to...

=INDEX(COLLECT({Column To Pull From}, {Name Column}, $[Primary Column]@row, {Week Number Column}, [1]$1), 1)

Put the above in [1]2 and you should be able to dragfill it across the columns and down the rows to get what you are needing.

Paul Newcome

It is because your second MATCH statement is generating a column number for the INDEX statement. The reason it works for the first week is because you are generating a 1 for the column number. Since you are only referencing a single column in your INDEX function, this works. When you move to week two, you are telling the INDEX function to pull from column 2 of only 1 selected.

You are going to want to try something more along the lines of an INDEX/COLLECT similar to...

=INDEX(COLLECT({Column To Pull From}, {Name Column}, $[Primary Column]@row, {Week Number Column}, [1]$1), 1)

Put the above in [1]2 and you should be able to dragfill it across the columns and down the rows to get what you are needing.

Gregory Zalecki

Thanks Paul! I did some digging and found out the calculation you have above work perfectly. I created a cheater column to combine name and week number to make grabbing the value easier (for me :)).

Paul Newcome

Happy to help! 👍️