Cross Sheet Formula Needing to return a value looking at a table
I am trying to look at a table of capacity percentages by week # and
by team member.
The table has a list of names down the Left Hand Side and the week number in a row along the top (see picture below).
I need to return a value based on the team member and the week # in a metrics sheet that I that will have the persons name and the week #.
My plan is to create a chart for a dashboard and only want to look and this week and next.
Is this possible?
Thank you in advance.,
Carl Vieira
Answers
-
-
Paul,
Yes I am familiar with Index/Match, it was what I was using but get an error each time I utilize so I am stumped.
Carl
-
Ok. Basically you are going to want something like this...
=INDEX({entire table excluding names}, MATCH(Name@row, {Name Column}, 0), Week@row)
It is a standard INDEX/MATCH, but for the INDEX range (1st one) you want to reference the entire table of data you want to pull. Then you MATCH on the name to grab the correct row for the INDEX and then use the Week Number to designate which column to pull from.
Help Article Resources
Categories
Check out the Formula Handbook template!