# Choosing Name Range inside Index / Match Function

1. ## Choosing Name Range inside Index / Match Function

Hi All

I have been trying to solve this but nothing seems to work.

I need to do salary cost forecast for the next five years with different payrise options

I have a formula that finds the value (salary) based on payscale table.

=INDEX(P1.0,MATCH(D57,INDEX(P1.0,,1),0),MATCH(\$D\$55,INDEX(P1.0,1,),0))

where P1.0 is a name range referring to a table in another sheet.

Is there a way to change the name range in formula based on the value in another cell (dropdown box would be perfect but it's not essential).

I tried Indirect function but didn't get far. I have tried something like this:

=INDEX((IF(S37=P1.0,P1.0,0)),MATCH(D57,INDEX(P1.0,,1),0),MATCH(\$D\$55,INDEX(P1.0,1,),0))

but get the REF

Any ideas?

Hopefully you will understand what I mean.

Mike

2. ## Re: Choosing Name Range inside Index / Match Function

If you had a drop-down box in another cell, would you choose the named ranges directly, i.e. "P1.0", "P1.1", "P2.0" or whatever you use for the names? Which cell would be used for this drop-down?

Pete

3. ## Re: Choosing Name Range inside Index / Match Function

Yes, If I had a drop-down box in another cell I would choose directly. At the moment I have six name ranges (if you need them) - P1.0 , P1.5, P1.75, P2.0, P2.25, P2.5

I think cell B36 would be the best in my case.

Mike

4. ## Re: Choosing Name Range inside Index / Match Function

Hi

Put the names of the ranges in a column and then.

In a cell-let's say A1, create a validation list for this range.

Then you can use INDIRECT function.

=INDEX(INDIRECT(A1),MATCH(D57,INDEX(INDIRECT(A1),,1),0),MATCH(\$D\$55,INDEX(INDIRECT(A1),1,),0)))))

5. ## Re: Choosing Name Range inside Index / Match Function

Okay, the formula would become:

=INDEX(INDIRECT(\$B\$36),MATCH(D57,INDEX(INDIRECT(\$B\$36),,1),0),MATCH(\$D\$55,INDEX(INDIRECT(\$B\$36),1,),0))

Note that INDIRECT(\$B\$36) replaces P1.0 three times in your original formula.

Hope this helps.

Pete

6. ## Re: Choosing Name Range inside Index / Match Function

Oh yes!

Pete, you made me a very happy man and saved a lot of work. works like a dream.

Thanks a lot for all

Mike

7. ## Re: Choosing Name Range inside Index / Match Function

here is one method

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1