+ Reply to Thread
Results 1 to 7 of 7

Choosing Name Range inside Index / Match Function

  1. #1
    Registered User
    Join Date
    10-05-2012
    Location
    Brighton
    MS-Off Ver
    Excel 2010
    Posts
    3

    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. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,144

    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. #3
    Registered User
    Join Date
    10-05-2012
    Location
    Brighton
    MS-Off Ver
    Excel 2010
    Posts
    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. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    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)))))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,144

    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. #6
    Registered User
    Join Date
    10-05-2012
    Location
    Brighton
    MS-Off Ver
    Excel 2010
    Posts
    3

    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. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Choosing Name Range inside Index / Match Function

    here is one method
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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