#INVALID VALUE on index and match function
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.
Best Answer
-
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.
Answers
-
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.
-
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 :)).
Help Article Resources
Categories
Check out the Formula Handbook template!
