Formula to pull data from second worksheet by matching name and across many columns

[David Zissman]

Good morning,

I have a list on sheet one with column A last name, column B first name. I need to pull data from sheets MA and PSS by name. There is an ever growing list of data in the same row for each name and I need to pull that back to sheet 1 in the appropriate cell. I tried an index match but am stuck on how to pull the data from across all columns.

For example, I am currently using =COUNTA(MA!D21:AW21)/((COUNTA(MA!$D$1:$AW$1))) to get the data but the D21 is where I am having the issue.

 

[jayalaxmi]

Good morning,

I have a list on sheet one with column A last name, column B first name. I need to pull data from sheets MA and PSS by name. There is an ever growing list of data in the same row for each name and I need to pull that back to sheet 1 in the appropriate cell. I tried an index match but am stuck on how to pull the data from across all columns.

For example, I am currently using =COUNTA(MA!D21:AW21)/((COUNTA(MA!$D$1:$AW$1))) to get the data but the D21 is where I am having the issue.

Hi David,

Can you be little more precise on your desired output.

As the above formula that you mentioned doesn't make sense as there is no data available in the range MA!D2:AY2.

Thanks
Jaya

 

[David Zissman]

Hi David,

Can you be little more precise on your desired output.

As the above formula that you mentioned doesn't make sense as there is no data available in the range MA!D2:AY2.

Thanks
Jaya

While there is currently no data in the range, the range will be filled in as needed. The formula works when the name on the dashboard is in the same row as it appears on the MA tab or the PSS tab, but when the filter occurs and some names are removed, then the pull does not align. I can not figure out how to match the name from the first sheet to the last two in order to pull the data from the appropriate row.

 

[David Zissman]

Still looking for any assistance on this if possible please.