Results 1 to 2 of 2

Problem with the INDEX function

This is a discussion on Problem with the INDEX function within the Excel Questions forums, part of the Question Forums category; could someone help with this problem? let's say i have a 10X10 array that i've named ARRAY. i'm using an ...

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Massachusetts
    Posts
    10

    Default

    could someone help with this problem?

    let's say i have a 10X10 array that i've named ARRAY.

    i'm using an INDEX call that references the array:
    =INDEX(ARRAY,1,1). this works correctly and returns '1000' as a value.

    however, when i use the following function:
    =INDEX(G17,1,1) {G17 contains the word 'Array'), the function doesn't work. it returns 'Array'. i thought using G17 in the first argument (G17 contains the word 'Array'), would reference the array named 'Array', not the actual cell G17 itself. how do i fix this?

    when i use the following function:
    =INDEX(vlookup(x,x:x,1),1,1) where the function vlookup(x,x:x,1) returns 'Array', the index function doesn't work at all; it returns #VALUE!.

    the bottomline is this; i need an INDEX function that's flexible enough so that the first argument (the INDEX function looks like this by the way: =INDEX(array, row_num, column_num) can be vary based on certain conditions. for example, if user chooses X, the 1st argument references Array, if user chooses Y, the 1st argument references Array1, etc, etc.


    please help.



    han

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    61,998

    Default

    On 2002-03-01 06:04, Hansoh wrote:
    could someone help with this problem?

    let's say i have a 10X10 array that i've named ARRAY.

    i'm using an INDEX call that references the array:
    =INDEX(ARRAY,1,1). this works correctly and returns '1000' as a value.

    however, when i use the following function:
    =INDEX(G17,1,1) {G17 contains the word 'Array'), the function doesn't work. it returns 'Array'. i thought using G17 in the first argument (G17 contains the word 'Array'), would reference the array named 'Array', not the actual cell G17 itself. how do i fix this?

    when i use the following function:
    =INDEX(vlookup(x,x:x,1),1,1) where the function vlookup(x,x:x,1) returns 'Array', the index function doesn't work at all; it returns #VALUE!.

    the bottomline is this; i need an INDEX function that's flexible enough so that the first argument (the INDEX function looks like this by the way: =INDEX(array, row_num, column_num) can be vary based on certain conditions. for example, if user chooses X, the 1st argument references Array, if user chooses Y, the 1st argument references Array1, etc, etc.


    please help.



    han
    Han,

    If what INDEX retrieves is a name that references a range, you should apply INDIRECT on the result INDEX retrives: Not sure but in your case it should be somthing like:

    INDIRECT(INDEX(G17,1,1)) as part of a formula where you need the range.

    Otherwise, describe your problem a bit more(without invoking formulas that you use, if you can).

    Aladin

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com