+ Reply to Thread
Results 1 to 5 of 5

Variable "sheet-name" and "range-name" wanted in INDEX/MATCH-function

  1. #1
    Registered User
    Join Date
    08-09-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    9

    Variable "sheet-name" and "range-name" wanted in INDEX/MATCH-function

    Hello excel-experts,


    The example excel-file has two sheets, named "Source" and "Retrieve".
    In "Source" there is data that I want to look up in sheet "Retrieve" using the functions "index" and "match".
    Because the data is on a different sheet, I could use a function like "= index( Source!$A:$C , 10 , 3)".
    Later I will change that "10"-value with a match-function, but that's besides the point right now.

    I want some flexibility. On the sheet "Retrieve" cell B1 has the text "Source" (so the name of the data sheet), cell B2 has the text "A" (so the first column in the range) and cell B3 has the text "C" (so the last column in the range).

    Instead of using the function "= index( Source!$A:$C , 10 , 3)" I want to use the texts in the cells B1, B2 and B3, so that my function still works if I decide to change the sheet-name or the column in the ranges.

    Is there a way to do this without declaring a range-name to the range Source!A:C ???


    I would really appreciate any help on this.


    Kind regards,
    Fie Buls


    P.S. Please let me know if you can help but my question or excel-sheet is not clear enough. I am Dutch, so I hope that downloading my excel-file translates the formulas automatically.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,898

    Re: Variable "sheet-name" and "range-name" wanted in INDEX/MATCH-function

    C1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    D1:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    D5:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Registered User
    Join Date
    08-09-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Variable "sheet-name" and "range-name" wanted in INDEX/MATCH-function

    Hi Ben van Jonhson,

    I really appreciate your help and am happy I got a response so quickly.
    I tried your formulas and in cells C1 I see the promising text-string 'Source'!$A:$C and in D1 I see 'Source'!$A:$A.

    In cell D5 you do not use your formulas in cells C1 and D1, but that is the whole idea I presume.
    However I get an "#N/B" error if I use in cell D5 the formula:

    =IF($B5="","",INDEX( C1 ,MATCH($B5, D1 ,0),3)) where C1 and D1 are the strings mentioned above.


    In the attached file you can see the result and the #N/B.

    Am I making a small, easily repairable, mistake?


    Kind regards,
    Fie Buls
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-09-2012
    Location
    Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Variable "sheet-name" and "range-name" wanted in INDEX/MATCH-function

    Hi Ben,

    I found out that if I use your formulas in C1 en D1 and then add the INDIRECT-function, it all works perfectly.
    Thanks for the initial help.


    Kind regards,
    Fie Buls

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,898

    Re: Variable "sheet-name" and "range-name" wanted in INDEX/MATCH-function

    My mistake, I posted the wrong formula for D5 . I intended to post with the INDIRECT...

+ 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