INDEX and MATCH using named ranges

I have broken down my tech names by region and created named ranges. I want to do an index and match to grab the data and dynamically update the table I am creating as the pivot expands.samplesheet61915.xlsx
LVL 19
Edward PamiasTeam Lead RRS DeskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
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
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Can I use this formula and copy across or down? Or do I have to do each field manually?
0
ProfessorJimJamCommented:
PLEASE FIND attached
samplesheet61915.xlsx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Saurabh Singh TeotiaCommented:
I just created a formula once..and copied it over..You can do the same and the results will be populated..
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
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.
0
Saurabh Singh TeotiaCommented:
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...
0
Saurabh Singh TeotiaCommented:
Here is detail explanation of the same about how it works...
0
ProfessorJimJamCommented:
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
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
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?
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
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
0
Edward PamiasTeam Lead RRS DeskAuthor Commented:
Here is the updated file with the random name ranges.
samplesheet622.xlsx
0
Saurabh Singh TeotiaCommented:
Their you go updated with vlookup to do what you are looking for..

Saurabh...
samplesheet622.xlsx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Edward PamiasTeam Lead RRS DeskAuthor Commented:
For what I needed this was the best solution. Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.