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

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Are you familiar with INDEX/MATCH formulas?

Carl Vieira

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

Paul Newcome

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.