Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    using Match & Index Functions alone & together

    Hi Everyone:
    XL 97 HELP for INDEX Function says:
    "INDEX({1,2;3,4},0,2) equals {2;4}" BUT when I copy and paste this into my WS, the answer comes up as 2 regardless of whether I enter it as an array formula or not.
    Also, the following equation: =MATCH("ccc",B3:F3,0) yields 1, when ccc is in cell B3? What do I use if I want to return a row # rather than a column number?
    Also, suppose you have an array, say in cells B3:G3 and one of the values in the array is duplicated in cell A1. Then, what should MATCH(A1,B3:G3,0)) return? Should it return the cell address within the array.

    All the above is prelude to my problem, which is that I am trying to get something like the following to work:
    INDEX(B$1:F$1,???,0) where in place of "???" I would have an equivalent function to MATCH(H3,B3:G3,0) that would return the row # for which I want INDEX() to return the vector that is in that row(within my array, of course).
    Thanks for any help


    <img src=/S/blank.gif border=0 alt=blank width=15 height=15>

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using Match & Index Functions alone & together

    The only way the Index example will work, is if you select the number of cells = to the number of expected returned values (in this case two) and array enter the formula in this range. It returns 2 in the first cell and 4 in the second.

    Your first Match example works as described -- Match returns the 'position' within the array or range so if you want to interpret it as a 'ROW' number, place your values in a Column starting in Row #1.

    Your problem as stated is a bit confusing since you have six elements in your Match range and 5 in your Index range, but you've indicated you want to return a vector row from an array. Where is this array?

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using Match & Index Functions alone & together

    HiGary:
    Let me try some values for range D3:G5 and lets say we have ccc entered as a criteria in A10

    aaa|smith,John|address01|Phone01
    bbb|brown,Charles|address02|Phone02
    ccc|Knight,Gladys|address03|phone03

    My equations would not work if I use "ccc" but they do if I use the ref A10 instead. The eq MATCH(A10,D25,0) returns
    3 for the 3rd row of the array and then
    =INDEX(D2:G5,MATCH(a10,D25,0),0) returns ccc BUT when I highlight the equation in formula bar and press F9, I get the entire 3rd row part returned in that window. I don't know how to get the row returned to cells so I could use them -- your message sounds like you know how to do it but it is late and I won't have time to try it till this weekend.
    Oh , I still want to know "what should MATCH(A1,B3:G5,0)) -- had a typo in my 1st posting -- return?". In other words is there a way to return the cell address with the value equal to one placed in cell Thanks againA1?

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using Match & Index Functions alone & together

    >Oh , I still want to know "what should MATCH(A1,B3:G5,0)) ->- had a typo in my 1st posting -- return?".

    Apparently, #N/A

    Although the Help says:

    Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array can be an array or an array reference.

    It looks like they're definition of contiguous means in a continuous line -- within a single row or single column. It appears that MATCH, despite calling the second argument "lookup_array", does not like an array for its input

    This is all from Excel8 SR-2

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    St. George, Maine, USA
    Posts
    158
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using Match & Index Functions alone & together

    There are a number of functions in Excel that require a set of cells to be selected to house the results of the function.

    From the help on the INDEX function reread the following:

    If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells. To enter an array formula, press CTRL+SHIFT+ENTER in Microsoft Excel 97 for Windows or +ENTER in Microsoft Excel 97 for the Macintosh.

    Because your formula: " =INDEX(D2:G5,Match(a10,D25,0),0)"
    has a zero for the colum_num the result expected is for a maximum of 4 values.
    If you enter the formula in cell F1, doing a CTRL+SHIFT+ENTER, this results in "ccc" in F1.
    If you then select cells F1 and F2 (in that order), click in formula bar and do a CTRL+SHIFT+ENTER then F1 and F2 will be filled with "ccc" and "Knight,Gladys" respectively.
    If you had selected cells F1 thru F4, then all 4 cells from the data table will be returned.

    You asked for a way to acquire the cell address with the value equal to one placed in cell A10.
    Well the following will develop the address of the cell containing "ccc" or the value in A10.
    ="D"&TEXT(2+MATCH(A10,D35,0),0)
    With the sample data the result is the text string "D5"
    To use this address, become familiar with the INDIRECT function.

    Hope this helps.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using Match & Index Functions alone & together

    Bob:
    I don't think I replied to you. Anyway, I want to thank you for the help. It opened up some doors for me in the application I was working on and I have continued to use these functions in other work.
    Thanks

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using Match & Index Functions alone & together

    And of course you'd get the same functionality using VLOOKUP:

    =VLOOKUP(A10,D2:G4,1,FALSE) returns the aaa,bbb,ccc
    =VLOOKUP(A10,D2:G4,2,FALSE) returns smith, brown, ..
    =VLOOKUP(A10,D2:G4,3,FALSE) returns address01, ....
    =VLOOKUP(A10,D2:G4,4,FALSE) returns Phone01,....
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: using Match & Index Functions alone & together

    Jan:
    I think you may have read the post too quickly. The data are all in separate cells and as far as know VLOOKUP can only return one cell at a time.

Posting Permissions

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