INDEX and MATCH using named ranges

I will recommend doing this in a better manner by using getpivotdata function as that is more powerful then index and match as it will automatically adjust itself as well when your values changed...

Enclosed is the sheet for your reference... Also for this you don't have to create any named ranges either...

Saurabh...
samplesheet61915.xlsx

Can I use this formula and copy across or down? Or do I have to do each field manually?

PLEASE FIND attached
samplesheet61915.xlsx

I just created a formula once..and copied it over..You can do the same and the results will be populated..

So I must be fat fingering something. I get a reference error when I try the index and match formula, the get pivot data is a bit harder for me to grasp. And I checked the formula through and through. I do not see when it's failing.

epamias...

Get pivot formula is simple..the first parameter is what you want to pick up and then basis of what combination you want to pick up so i define field name in the same and define the criteria in those field name which basically gives you the value that you are looking for...

Saurabh...

Here is detail explanation of the same about how it works...

When you get the Ref error because probably the name you used in the inside indirect function and your name range do not match
The name in the name range and the names in the cells should be exact the same if it has space in the slcells then you need to remove the space because named ranges do not accept space

OK I see what my problem is. The name range I select are random names and not in order like it is in this pivot. How would the formula change if you selected every other name for a range?

So for the current formula the names are selected in order. For example the first 3 name, then the next 3 names etc.... but in my file I select every other name for my regions.

Dan 1
Dawn 1
Jane 1
John 1
Ken 1
Lawrence 1
Stephanie 1
Tom 1
doug 1
kathy 1

Here is the updated file with the random name ranges.
samplesheet622.xlsx

For what I needed this was the best solution. Thank you!