+ Reply to Thread
Results 1 to 163 of 163

Array index, match problem

  1. #1
    Duke Carey
    Guest

    RE: Array index, match problem

    How about

    =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))

    which assumes the colors are in cells G1:G6



    "RAP" wrote:

    > Hello, Folks. This is my first post to this Discussion Group. I must have
    > Dain Bramage to not be able to come up with an answer, but I can't. I need
    > some help.
    > Below is an example of my problem.
    > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > I need a formula that will match value H26 from the following table and
    > return "Green" as the result.
    >
    >
    > H2 H3 H4 H5 H6 H7 Red
    > H9 H10 H11 H12 H13 H14 White
    > H16 H17 H18 H19 H20 H21 Blue
    > H23 H24 H25 H26 H27 H28 Green
    > H30 H31 H32 H33 H34 H35 Black
    > H37 H38 H39 H40 H41 H42 Purple
    >
    > Any help, pointers, suggestions or direction will be greatly appreciated.
    > Thanks, - Randy


  2. #2
    RAP
    Guest

    RE: Array index, match problem

    Duke,
    You are awesome! I haven't even heard of all those commands. Worked like a
    charm. Thank you so much. - Randy

    "Duke Carey" wrote:

    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly appreciated.
    > > Thanks, - Randy


  3. #3
    Bob Phillips
    Guest

    Re: Array index, match problem



    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Duke Carey" <[email protected]> wrote in message
    news:[email protected]...
    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must

    have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I

    need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly

    appreciated.
    > > Thanks, - Randy




  4. #4
    Bob Phillips
    Guest

    Re: Array index, match problem

    Just for interest, two less functions

    =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Duke Carey" <[email protected]> wrote in message
    news:[email protected]...
    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must

    have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I

    need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly

    appreciated.
    > > Thanks, - Randy




  5. #5
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Thanks a lot for the formula. I will start to disect it and try to learn as
    much as I can from it. I appreciate it.
    Randy

    "Bob Phillips" wrote:

    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Duke Carey" <[email protected]> wrote in message
    > news:[email protected]...
    > > How about
    > >
    > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > >
    > > which assumes the colors are in cells G1:G6
    > >
    > >
    > >
    > > "RAP" wrote:
    > >
    > > > Hello, Folks. This is my first post to this Discussion Group. I must

    > have
    > > > Dain Bramage to not be able to come up with an answer, but I can't. I

    > need
    > > > some help.
    > > > Below is an example of my problem.
    > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > I need a formula that will match value H26 from the following table and
    > > > return "Green" as the result.
    > > >
    > > >
    > > > H2 H3 H4 H5 H6 H7 Red
    > > > H9 H10 H11 H12 H13 H14 White
    > > > H16 H17 H18 H19 H20 H21 Blue
    > > > H23 H24 H25 H26 H27 H28 Green
    > > > H30 H31 H32 H33 H34 H35 Black
    > > > H37 H38 H39 H40 H41 H42 Purple
    > > >
    > > > Any help, pointers, suggestions or direction will be greatly

    > appreciated.
    > > > Thanks, - Randy

    >
    >
    >


  6. #6
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Thanks again for the formula. I have my app running now, thanks to you. I
    would like to ask you another question, but I think it belongs in the
    "Programming" board. How would I place your formula in VB script, instead of
    inserting it into a cell? Also, the data in the "J19 input cell" is being
    placed there (pasted) by a range variable, "X".

    Like I said, my app is functioning, but I want to achieve results using more
    programming and less cursoring around, like I mentioned before.

    Thanks,
    Randy


    "Bob Phillips" wrote:

    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Duke Carey" <[email protected]> wrote in message
    > news:[email protected]...
    > > How about
    > >
    > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > >
    > > which assumes the colors are in cells G1:G6
    > >
    > >
    > >
    > > "RAP" wrote:
    > >
    > > > Hello, Folks. This is my first post to this Discussion Group. I must

    > have
    > > > Dain Bramage to not be able to come up with an answer, but I can't. I

    > need
    > > > some help.
    > > > Below is an example of my problem.
    > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > I need a formula that will match value H26 from the following table and
    > > > return "Green" as the result.
    > > >
    > > >
    > > > H2 H3 H4 H5 H6 H7 Red
    > > > H9 H10 H11 H12 H13 H14 White
    > > > H16 H17 H18 H19 H20 H21 Blue
    > > > H23 H24 H25 H26 H27 H28 Green
    > > > H30 H31 H32 H33 H34 H35 Black
    > > > H37 H38 H39 H40 H41 H42 Purple
    > > >
    > > > Any help, pointers, suggestions or direction will be greatly

    > appreciated.
    > > > Thanks, - Randy

    >
    >
    >


  7. #7
    Bob Phillips
    Guest

    Re: Array index, match problem

    Randy,

    In VBA the code would look like

    Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"), 2,
    9) - 1) / 7 + 1)

    If you want to use the variable X without going via J19, use

    Dim x
    x = "H30"
    Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) / 7 +
    1)



    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "RAP" <[email protected]> wrote in message
    news:[email protected]...
    > Bob,
    > Thanks again for the formula. I have my app running now, thanks to you.

    I
    > would like to ask you another question, but I think it belongs in the
    > "Programming" board. How would I place your formula in VB script, instead

    of
    > inserting it into a cell? Also, the data in the "J19 input cell" is being
    > placed there (pasted) by a range variable, "X".
    >
    > Like I said, my app is functioning, but I want to achieve results using

    more
    > programming and less cursoring around, like I mentioned before.
    >
    > Thanks,
    > Randy
    >
    >
    > "Bob Phillips" wrote:
    >
    > > Just for interest, two less functions
    > >
    > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Duke Carey" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > How about
    > > >
    > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > >
    > > > which assumes the colors are in cells G1:G6
    > > >
    > > >
    > > >
    > > > "RAP" wrote:
    > > >
    > > > > Hello, Folks. This is my first post to this Discussion Group. I

    must
    > > have
    > > > > Dain Bramage to not be able to come up with an answer, but I can't.

    I
    > > need
    > > > > some help.
    > > > > Below is an example of my problem.
    > > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > > I need a formula that will match value H26 from the following table

    and
    > > > > return "Green" as the result.
    > > > >
    > > > >
    > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > H9 H10 H11 H12 H13 H14 White
    > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > >
    > > > > Any help, pointers, suggestions or direction will be greatly

    > > appreciated.
    > > > > Thanks, - Randy

    > >
    > >
    > >




  8. #8
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Once again, thanks. Works great. Now, how do I get the result of the
    formula (Red, White, etc...) pasted into yet another cell? I still have the
    mind-set that the result is in a cell somewhere. As soon as I can start
    thinking "programmatically", these simple questions will, well, at least they
    should diminish.

    Thanks again,
    Randy

    "Bob Phillips" wrote:

    > Randy,
    >
    > In VBA the code would look like
    >
    > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"), 2,
    > 9) - 1) / 7 + 1)
    >
    > If you want to use the variable X without going via J19, use
    >
    > Dim x
    > x = "H30"
    > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) / 7 +
    > 1)
    >
    >
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "RAP" <[email protected]> wrote in message
    > news:[email protected]...
    > > Bob,
    > > Thanks again for the formula. I have my app running now, thanks to you.

    > I
    > > would like to ask you another question, but I think it belongs in the
    > > "Programming" board. How would I place your formula in VB script, instead

    > of
    > > inserting it into a cell? Also, the data in the "J19 input cell" is being
    > > placed there (pasted) by a range variable, "X".
    > >
    > > Like I said, my app is functioning, but I want to achieve results using

    > more
    > > programming and less cursoring around, like I mentioned before.
    > >
    > > Thanks,
    > > Randy
    > >
    > >
    > > "Bob Phillips" wrote:
    > >
    > > > Just for interest, two less functions
    > > >
    > > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > > >
    > > > --
    > > >
    > > > HTH
    > > >
    > > > RP
    > > > (remove nothere from the email address if mailing direct)
    > > >
    > > >
    > > > "Duke Carey" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > How about
    > > > >
    > > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > > >
    > > > > which assumes the colors are in cells G1:G6
    > > > >
    > > > >
    > > > >
    > > > > "RAP" wrote:
    > > > >
    > > > > > Hello, Folks. This is my first post to this Discussion Group. I

    > must
    > > > have
    > > > > > Dain Bramage to not be able to come up with an answer, but I can't.

    > I
    > > > need
    > > > > > some help.
    > > > > > Below is an example of my problem.
    > > > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > > > I need a formula that will match value H26 from the following table

    > and
    > > > > > return "Green" as the result.
    > > > > >
    > > > > >
    > > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > > H9 H10 H11 H12 H13 H14 White
    > > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > > >
    > > > > > Any help, pointers, suggestions or direction will be greatly
    > > > appreciated.
    > > > > > Thanks, - Randy
    > > >
    > > >
    > > >

    >
    >
    >


  9. #9
    Bob Phillips
    Guest

    Re: Array index, match problem

    Hi again Randy,

    That would be a single assignment to a cell, say M2, and assuming variable X
    has that lookup value

    Range("M2").Value = Application.Index(Range("$G$1:$G$6"), (Mid(X, 2, 9) - 1)
    / 7 + 1)


    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "RAP" <[email protected]> wrote in message
    news:[email protected]...
    > Bob,
    > Once again, thanks. Works great. Now, how do I get the result of the
    > formula (Red, White, etc...) pasted into yet another cell? I still have

    the
    > mind-set that the result is in a cell somewhere. As soon as I can start
    > thinking "programmatically", these simple questions will, well, at least

    they
    > should diminish.
    >
    > Thanks again,
    > Randy
    >
    > "Bob Phillips" wrote:
    >
    > > Randy,
    > >
    > > In VBA the code would look like
    > >
    > > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"),

    2,
    > > 9) - 1) / 7 + 1)
    > >
    > > If you want to use the variable X without going via J19, use
    > >
    > > Dim x
    > > x = "H30"
    > > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) /

    7 +
    > > 1)
    > >
    > >
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "RAP" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Bob,
    > > > Thanks again for the formula. I have my app running now, thanks to

    you.
    > > I
    > > > would like to ask you another question, but I think it belongs in the
    > > > "Programming" board. How would I place your formula in VB script,

    instead
    > > of
    > > > inserting it into a cell? Also, the data in the "J19 input cell" is

    being
    > > > placed there (pasted) by a range variable, "X".
    > > >
    > > > Like I said, my app is functioning, but I want to achieve results

    using
    > > more
    > > > programming and less cursoring around, like I mentioned before.
    > > >
    > > > Thanks,
    > > > Randy
    > > >
    > > >
    > > > "Bob Phillips" wrote:
    > > >
    > > > > Just for interest, two less functions
    > > > >
    > > > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > > > >
    > > > > --
    > > > >
    > > > > HTH
    > > > >
    > > > > RP
    > > > > (remove nothere from the email address if mailing direct)
    > > > >
    > > > >
    > > > > "Duke Carey" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > > > How about
    > > > > >
    > > > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > > > >
    > > > > > which assumes the colors are in cells G1:G6
    > > > > >
    > > > > >
    > > > > >
    > > > > > "RAP" wrote:
    > > > > >
    > > > > > > Hello, Folks. This is my first post to this Discussion Group.

    I
    > > must
    > > > > have
    > > > > > > Dain Bramage to not be able to come up with an answer, but I

    can't.
    > > I
    > > > > need
    > > > > > > some help.
    > > > > > > Below is an example of my problem.
    > > > > > > Input cell = J19. Input Value = H26. Formula placed in cell

    J20.
    > > > > > > I need a formula that will match value H26 from the following

    table
    > > and
    > > > > > > return "Green" as the result.
    > > > > > >
    > > > > > >
    > > > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > > > H9 H10 H11 H12 H13 H14 White
    > > > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > > > >
    > > > > > > Any help, pointers, suggestions or direction will be greatly
    > > > > appreciated.
    > > > > > > Thanks, - Randy
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




  10. #10
    Alan Beban
    Guest

    Re: Array index, match problem

    Bob Phillips wrote:
    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >

    For a formula that is independent of the size of the table of data or
    the regularity of its contents, if the functions in the freely
    downloadable file at http://home.pacbell.net are available to your workbook

    =OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))

    Alan Beban

  11. #11
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    >Bob Phillips wrote:
    >>Just for interest, two less functions
    >>
    >>=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    >
    >For a formula that is independent of the size of the table of data or
    >the regularity of its contents, if the functions in the freely
    >downloadable file at http://home.pacbell.net are available to your workbook
    >
    >=OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,
    >-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))


    Assuming someone would use your function library, wouldn't they want to
    do so efficiently? Only one udf call needed (MakeArray).

    =INDEX(DataTable,INT((MATCH(J19,MakeArray(DataTable,1),0)-1)
    /COLUMNS(DataTable))+1,COLUMNS(DataTable))


  12. #12
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    >
    >>Bob Phillips wrote:
    >>
    >>>Just for interest, two less functions
    >>>
    >>>=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    >>
    >>For a formula that is independent of the size of the table of data or
    >>the regularity of its contents, if the functions in the freely
    >>downloadable file at http://home.pacbell.net are available to your workbook
    >>
    >>=OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,
    >>-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))

    >
    >
    > Assuming someone would use your function library, wouldn't they want to
    > do so efficiently? Only one udf call needed (MakeArray).
    >
    > =INDEX(DataTable,INT((MATCH(J19,MakeArray(DataTable,1),0)-1)
    > /COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >

    Well, isn't that interesting! I would have thought the suggestion would
    be something like fill down a range on Sheet8, for example, named CxRV with

    =INDIRECT(ADDRESS(
    ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))
    (compliments of Chip Pearson)

    and then enter something like

    =INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))

    in order to use only built-in functions and avoid at all costs the
    dreaded Array Functions.

    But no, here's Harlan Grove, instead carping about which of the dreaded
    Array Functions is more efficient. Onward and upward!

    Alan Beban

  13. #13
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >But no, here's Harlan Grove, instead carping about which of the dreaded
    >Array Functions is more efficient. Onward and upward!


    Just pointing out that you don't know how to use your own function
    library efficiently.

    Onward perhaps. Never upward.


  14. #14
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >=INDIRECT(ADDRESS(
    >ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),
    >COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))

    ....
    >=INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >
    >in order to use only built-in functions and avoid at all costs the
    >dreaded Array Functions.

    ....

    Ugh! Not the best way by a long shot! All it takes is a single array
    formula

    =INDEX(DataTable,MATCH(TRUE,COUNTIF(OFFSET(DataTable,
    ROW(DataTable)-CELL("Row",DataTable),0,1,),J1)>0,0),COLUMNS(DataTable))


  15. #15
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>=INDIRECT(ADDRESS(
    >>ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),
    >>COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))

    >
    > ...
    >
    >>=INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >>
    >>in order to use only built-in functions and avoid at all costs the
    >>dreaded Array Functions.

    >
    > ...
    >
    > Ugh! Not the best way by a long shot! All it takes is a single array
    > formula
    >
    > =INDEX(DataTable,MATCH(TRUE,COUNTIF(OFFSET(DataTable,
    > ROW(DataTable)-CELL("Row",DataTable),0,1,),J1)>0,0),COLUMNS(DataTable))
    >


    J1 should be J19 to conform to the original specification.

    Alan Beban

  16. #16
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>But no, here's Harlan Grove, instead carping about which of the dreaded
    >>Array Functions is more efficient. Onward and upward!

    >
    >
    > Just pointing out that you don't know how to use your own function
    > library efficiently.
    >


    Perhaps you could quantify for the users the difference in efficiency.

    Alan Beban

  17. #17
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >Perhaps you could quantify for the users the difference in efficiency.


    Nah, I'll leave that for you as an exercise since you're the one who
    needs to learn about efficiency. Here's a hint: one udf call will
    invariably be faster than two, even when there are a few extra built-in
    function calls with the single udf call.


  18. #18
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>Perhaps you could quantify for the users the difference in efficiency.

    >
    >
    > Nah, I'll leave that for you as an exercise since you're the one who
    > needs to learn about efficiency.


    Cute. But I suspect the real reason you don't want to deal with it is
    that the so-called "efficiency" to which you and many
    programmer/developers sometimes refer often involves nanoseconds of
    difference that are totally irrelevant to most users in most
    applications; interesting to you for purposes of posting oneupmanship,
    but somewhat misleading for users generally.

    Alan Beban

  19. #19
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ..=2E.
    >Cute. But I suspect the real reason you don't want to deal with it is
    >that the so-called "efficiency" to which you and many
    >programmer/developers sometimes refer often involves nanoseconds of
    >difference that are totally irrelevant to most users in most
    >applications; interesting to you for purposes of posting oneupmanship,
    >but somewhat misleading for users generally.


    Fine. Then consider whether the MakeArray formula,

    =3DINDEX(DataTable,INT((MATCH(J1=AD9,MakeArray(DataTable,1),0)-1)
    /COLUMNS(DataTable))+1,COLUMNS=AD(DataTable))

    a single MATCH against the data range transformed into a 1D array, with
    the result adjusted by a division inside INT to return the row number,
    against the ArrayMatch formula,

    =3DOFFSET(INDIRECT(ArrayMatch(J=AD19,dataTable,"A")),0,
    -INDEX(ArrayMatch(J19,dataTab=ADle),1,2)+COLUMNS(dataTable))

    first returning the cell address of the matching cell then using
    another call to fix the column offset. It's subjective whether the row
    index contortions of the MakeArray formula are more obscure than the
    column offset contortions of the 2 ArrayMatch formula.

    For that matter, you could also have used

    =3DINDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))

    which would have been a LOT simpler than either of the others.
    Simplicity is good.

    Both the MakeArray and the single ArrayMatch formulas involve no
    volatile function calls, so they won't cause Excel to prompt users to
    save any file containing them if users try to close such workbooks
    without making any changes. Your two ArrayMatch formula, due to OFFSET
    and INDIRECT calls, would cause such confusing prompts.

    Is that an acceptable user consideration?


  20. #20
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>Cute. But I suspect the real reason you don't want to deal with it is
    >>that the so-called "efficiency" to which you and many
    >>programmer/developers sometimes refer often involves nanoseconds of
    >>difference that are totally irrelevant to most users in most
    >>applications; interesting to you for purposes of posting oneupmanship,
    >>but somewhat misleading for users generally.

    >
    >
    > Fine. Then consider whether the MakeArray formula,
    >
    > =INDEX(DataTable,INT((MATCH(J1*9,MakeArray(DataTable,1),0)-1)
    > /COLUMNS(DataTable))+1,COLUMNS*(DataTable))
    >
    > a single MATCH against the data range transformed into a 1D array, with
    > the result adjusted by a division inside INT to return the row number,
    > against the ArrayMatch formula,
    >
    > =OFFSET(INDIRECT(ArrayMatch(J*19,dataTable,"A")),0,
    > -INDEX(ArrayMatch(J19,dataTab*le),1,2)+COLUMNS(dataTable))
    >
    > first returning the cell address of the matching cell then using
    > another call to fix the column offset. It's subjective whether the row
    > index contortions of the MakeArray formula are more obscure than the
    > column offset contortions of the 2 ArrayMatch formula.
    >
    > For that matter, you could also have used
    >
    > =INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))
    >
    > which would have been a LOT simpler than either of the others.
    > Simplicity is good.
    >
    > Both the MakeArray and the single ArrayMatch formulas involve no
    > volatile function calls, so they won't cause Excel to prompt users to
    > save any file containing them if users try to close such workbooks
    > without making any changes. Your two ArrayMatch formula, due to OFFSET
    > and INDIRECT calls, would cause such confusing prompts.
    >
    > Is that an acceptable user consideration?
    >

    It's certainly more constructive, particularly the suggestion of

    =INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))

    which is significantly faster than the one including the two ArrayMatch
    function calls that I originally posted. Would have been nice had you
    focused on the instructional value for the users in the first place,
    rather than just on stroking your ego. But then, there you go!

    By the way, the formula with the MakeArray function call seems to return
    an error if the data table exceeds 65536 elements (I haven't yet
    identified why; it might be fixable), while neither of the ArrayMatch
    formulas seems to--though they are slower.

    Alan Beban

  21. #21
    Alan Beban
    Guest

    Re: Array index, match problem

    Alan Beban wrote:
    > Harlan Grove wrote:
    > . . .Then consider whether the MakeArray formula,
    >>
    >> =INDEX(DataTable,INT((MATCH(J1*9,MakeArray(DataTable,1),0)-1)
    >> /COLUMNS(DataTable))+1,COLUMNS*(DataTable))
    >>
    >> a single MATCH against the data range transformed into a 1D array, with
    >> the result adjusted by a division inside INT to return the row number,
    >> against the ArrayMatch formula,
    >>
    >> =OFFSET(INDIRECT(ArrayMatch(J*19,dataTable,"A")),0,
    >> -INDEX(ArrayMatch(J19,dataTab*le),1,2)+COLUMNS(dataTable))
    >>
    >> first returning the cell address of the matching cell then using
    >> another call to fix the column offset. It's subjective whether the row
    >> index contortions of the MakeArray formula are more obscure than the
    >> column offset contortions of the 2 ArrayMatch formula.
    >>
    >>...By the way, the formula with the MakeArray function call seems to return

    > an error if the data table exceeds 65536 elements . . . .


    The problem is with the built-in INDEX function; it fails if the array
    or reference contains more than 65536 elements.

    Alan Beban

  22. #22
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >The problem is with the built-in INDEX function; it fails if the array
    >or reference contains more than 65536 elements.


    Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    chokes after 65,535 elements.

    Which would argue in favor of using the COUNTIF function provided in
    another branch of this thread. It's one drawback is the volatile OFFSET
    call. It'd always recalc, but it'd be lots faster than even a single
    udf call.


  23. #23
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >=INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))
    >
    >which is significantly faster than the one including the two ArrayMatch
    >function calls that I originally posted. Would have been nice had you
    >focused on the instructional value for the users in the first place,
    >rather than just on stroking your ego. But then, there you go!


    Of course you could have offerred it too, if you had thought of it. But
    then, there you go!

    >By the way, the formula with the MakeArray function call seems to return
    >an error if the data table exceeds 65536 elements (I haven't yet
    >identified why; it might be fixable), while neither of the ArrayMatch
    >formulas seems to--though they are slower.


    Excel can't handle any arrays with more than 65,535 entries in either
    of 1 or 2 dimensions, as you should know. It's questionable whether
    anyone should try to use brute force matching on so many cells. It'd be
    slow even without any udfs or volatile functions. There are tasks for
    which indexed database searches would be far more appropriate than
    unindexed spreadsheet searches. If the OP has so many entries to
    search, the OP is being foolish using a spreadsheet for the task.
    However, if the OP is only searching a few hundred entries or fewer,
    your caveat provides completeness of specification but is of no
    practical relevance.


  24. #24
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>The problem is with the built-in INDEX function; it fails if the array
    >>or reference contains more than 65536 elements.

    >
    >
    > Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    > chokes after 65,535 elements. . . .


    Dim arr, i
    ReDim arr(1 To 65536)
    For i = 1 To 65536
    arr(i) = i * 2
    Next
    MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.

    In xl2000 and earlier it fails on 5462 elements or greater.

    Alan Beban

  25. #25
    Alan Beban
    Guest

    Re: Array index, match problem

    Alan Beban wrote:
    > Harlan Grove wrote:
    >
    >> Alan Beban wrote...
    >> ...
    >>
    >>> The problem is with the built-in INDEX function; it fails if the array
    >>> or reference contains more than 65536 elements.

    >>
    >>
    >>
    >> Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >> chokes after 65,535 elements. . . .

    >
    >
    > Dim arr, i
    > ReDim arr(1 To 65536)
    > For i = 1 To 65536
    > arr(i) = i * 2
    > Next
    > MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >
    > In xl2000 and earlier it fails on 5462 elements or greater.
    >
    > Alan Beban


    Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    xl2000 or xl2002).

    Alan Beban

  26. #26
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    >>>Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >>>chokes after 65,535 elements. . . .

    >>
    >> Dim arr, i
    >> ReDim arr(1 To 65536)
    >> For i = 1 To 65536
    >> arr(i) = i * 2
    >> Next
    >> MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >>
    >> In xl2000 and earlier it fails on 5462 elements or greater.

    >
    >Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    >xl2000 or xl2002).


    Not comparable. A:B is a range, not an array. But you're correct that
    INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and
    subsequent arguments and converts them to long integers.


  27. #27
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    >
    >>>>Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >>>>chokes after 65,535 elements. . . .
    >>>
    >>> Dim arr, i
    >>> ReDim arr(1 To 65536)
    >>> For i = 1 To 65536
    >>> arr(i) = i * 2
    >>> Next
    >>> MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >>>
    >>>In xl2000 and earlier it fails on 5462 elements or greater.

    >>
    >>Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    >>xl2000 or xl2002).

    >
    >
    > Not comparable. A:B is a range, not an array. But you're correct that
    > INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and
    > subsequent arguments and converts them to long integers.
    >


    It seems that the limitation on the VBA invocation of the INDEX function
    is a bit subtler; it is not limited by the number of the elements in the
    array (see arr1 below) but apparently by the number of elements in a
    dimension.

    Sub testIt3a()
    Dim arr1, arr2, arr3
    Dim i As Long, j As Long
    Dim x, y, z

    '65536 rows, 2 columns
    ReDim arr1(1 To 65536, 1 To 2)
    For i = 1 To 65536: For j = 1 To 2
    arr1(i, j) = i * 2 + j
    Next: Next

    '1 row, 65536 columns
    ReDim arr2(1 To 65536)
    For i = 1 To 65536
    arr2(i) = i
    Next

    '1 row, 65537 columns
    ReDim arr3(1 To 65537)
    For i = 1 To 65537
    arr3(i) = i
    Next

    x = Application.Index(arr1, 65536, 2)
    Debug.Print x '<---returns 131074
    y = Application.Index(arr2, 65536)
    Debug.Print y '<---returns 65536
    z = Application.Index(arr3, 65536) 'Type mismatch error
    End Sub

    Alan Beban

  28. #28
    Duke Carey
    Guest

    RE: Array index, match problem

    How about

    =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))

    which assumes the colors are in cells G1:G6



    "RAP" wrote:

    > Hello, Folks. This is my first post to this Discussion Group. I must have
    > Dain Bramage to not be able to come up with an answer, but I can't. I need
    > some help.
    > Below is an example of my problem.
    > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > I need a formula that will match value H26 from the following table and
    > return "Green" as the result.
    >
    >
    > H2 H3 H4 H5 H6 H7 Red
    > H9 H10 H11 H12 H13 H14 White
    > H16 H17 H18 H19 H20 H21 Blue
    > H23 H24 H25 H26 H27 H28 Green
    > H30 H31 H32 H33 H34 H35 Black
    > H37 H38 H39 H40 H41 H42 Purple
    >
    > Any help, pointers, suggestions or direction will be greatly appreciated.
    > Thanks, - Randy


  29. #29
    RAP
    Guest

    RE: Array index, match problem

    Duke,
    You are awesome! I haven't even heard of all those commands. Worked like a
    charm. Thank you so much. - Randy

    "Duke Carey" wrote:

    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly appreciated.
    > > Thanks, - Randy


  30. #30
    Bob Phillips
    Guest

    Re: Array index, match problem



    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Duke Carey" <[email protected]> wrote in message
    news:[email protected]...
    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must

    have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I

    need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly

    appreciated.
    > > Thanks, - Randy




  31. #31
    Bob Phillips
    Guest

    Re: Array index, match problem

    Just for interest, two less functions

    =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Duke Carey" <[email protected]> wrote in message
    news:[email protected]...
    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must

    have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I

    need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly

    appreciated.
    > > Thanks, - Randy




  32. #32
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Thanks a lot for the formula. I will start to disect it and try to learn as
    much as I can from it. I appreciate it.
    Randy

    "Bob Phillips" wrote:

    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Duke Carey" <[email protected]> wrote in message
    > news:[email protected]...
    > > How about
    > >
    > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > >
    > > which assumes the colors are in cells G1:G6
    > >
    > >
    > >
    > > "RAP" wrote:
    > >
    > > > Hello, Folks. This is my first post to this Discussion Group. I must

    > have
    > > > Dain Bramage to not be able to come up with an answer, but I can't. I

    > need
    > > > some help.
    > > > Below is an example of my problem.
    > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > I need a formula that will match value H26 from the following table and
    > > > return "Green" as the result.
    > > >
    > > >
    > > > H2 H3 H4 H5 H6 H7 Red
    > > > H9 H10 H11 H12 H13 H14 White
    > > > H16 H17 H18 H19 H20 H21 Blue
    > > > H23 H24 H25 H26 H27 H28 Green
    > > > H30 H31 H32 H33 H34 H35 Black
    > > > H37 H38 H39 H40 H41 H42 Purple
    > > >
    > > > Any help, pointers, suggestions or direction will be greatly

    > appreciated.
    > > > Thanks, - Randy

    >
    >
    >


  33. #33
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Thanks again for the formula. I have my app running now, thanks to you. I
    would like to ask you another question, but I think it belongs in the
    "Programming" board. How would I place your formula in VB script, instead of
    inserting it into a cell? Also, the data in the "J19 input cell" is being
    placed there (pasted) by a range variable, "X".

    Like I said, my app is functioning, but I want to achieve results using more
    programming and less cursoring around, like I mentioned before.

    Thanks,
    Randy


    "Bob Phillips" wrote:

    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Duke Carey" <[email protected]> wrote in message
    > news:[email protected]...
    > > How about
    > >
    > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > >
    > > which assumes the colors are in cells G1:G6
    > >
    > >
    > >
    > > "RAP" wrote:
    > >
    > > > Hello, Folks. This is my first post to this Discussion Group. I must

    > have
    > > > Dain Bramage to not be able to come up with an answer, but I can't. I

    > need
    > > > some help.
    > > > Below is an example of my problem.
    > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > I need a formula that will match value H26 from the following table and
    > > > return "Green" as the result.
    > > >
    > > >
    > > > H2 H3 H4 H5 H6 H7 Red
    > > > H9 H10 H11 H12 H13 H14 White
    > > > H16 H17 H18 H19 H20 H21 Blue
    > > > H23 H24 H25 H26 H27 H28 Green
    > > > H30 H31 H32 H33 H34 H35 Black
    > > > H37 H38 H39 H40 H41 H42 Purple
    > > >
    > > > Any help, pointers, suggestions or direction will be greatly

    > appreciated.
    > > > Thanks, - Randy

    >
    >
    >


  34. #34
    Bob Phillips
    Guest

    Re: Array index, match problem

    Randy,

    In VBA the code would look like

    Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"), 2,
    9) - 1) / 7 + 1)

    If you want to use the variable X without going via J19, use

    Dim x
    x = "H30"
    Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) / 7 +
    1)



    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "RAP" <[email protected]> wrote in message
    news:[email protected]...
    > Bob,
    > Thanks again for the formula. I have my app running now, thanks to you.

    I
    > would like to ask you another question, but I think it belongs in the
    > "Programming" board. How would I place your formula in VB script, instead

    of
    > inserting it into a cell? Also, the data in the "J19 input cell" is being
    > placed there (pasted) by a range variable, "X".
    >
    > Like I said, my app is functioning, but I want to achieve results using

    more
    > programming and less cursoring around, like I mentioned before.
    >
    > Thanks,
    > Randy
    >
    >
    > "Bob Phillips" wrote:
    >
    > > Just for interest, two less functions
    > >
    > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Duke Carey" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > How about
    > > >
    > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > >
    > > > which assumes the colors are in cells G1:G6
    > > >
    > > >
    > > >
    > > > "RAP" wrote:
    > > >
    > > > > Hello, Folks. This is my first post to this Discussion Group. I

    must
    > > have
    > > > > Dain Bramage to not be able to come up with an answer, but I can't.

    I
    > > need
    > > > > some help.
    > > > > Below is an example of my problem.
    > > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > > I need a formula that will match value H26 from the following table

    and
    > > > > return "Green" as the result.
    > > > >
    > > > >
    > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > H9 H10 H11 H12 H13 H14 White
    > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > >
    > > > > Any help, pointers, suggestions or direction will be greatly

    > > appreciated.
    > > > > Thanks, - Randy

    > >
    > >
    > >




  35. #35
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Once again, thanks. Works great. Now, how do I get the result of the
    formula (Red, White, etc...) pasted into yet another cell? I still have the
    mind-set that the result is in a cell somewhere. As soon as I can start
    thinking "programmatically", these simple questions will, well, at least they
    should diminish.

    Thanks again,
    Randy

    "Bob Phillips" wrote:

    > Randy,
    >
    > In VBA the code would look like
    >
    > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"), 2,
    > 9) - 1) / 7 + 1)
    >
    > If you want to use the variable X without going via J19, use
    >
    > Dim x
    > x = "H30"
    > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) / 7 +
    > 1)
    >
    >
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "RAP" <[email protected]> wrote in message
    > news:[email protected]...
    > > Bob,
    > > Thanks again for the formula. I have my app running now, thanks to you.

    > I
    > > would like to ask you another question, but I think it belongs in the
    > > "Programming" board. How would I place your formula in VB script, instead

    > of
    > > inserting it into a cell? Also, the data in the "J19 input cell" is being
    > > placed there (pasted) by a range variable, "X".
    > >
    > > Like I said, my app is functioning, but I want to achieve results using

    > more
    > > programming and less cursoring around, like I mentioned before.
    > >
    > > Thanks,
    > > Randy
    > >
    > >
    > > "Bob Phillips" wrote:
    > >
    > > > Just for interest, two less functions
    > > >
    > > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > > >
    > > > --
    > > >
    > > > HTH
    > > >
    > > > RP
    > > > (remove nothere from the email address if mailing direct)
    > > >
    > > >
    > > > "Duke Carey" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > How about
    > > > >
    > > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > > >
    > > > > which assumes the colors are in cells G1:G6
    > > > >
    > > > >
    > > > >
    > > > > "RAP" wrote:
    > > > >
    > > > > > Hello, Folks. This is my first post to this Discussion Group. I

    > must
    > > > have
    > > > > > Dain Bramage to not be able to come up with an answer, but I can't.

    > I
    > > > need
    > > > > > some help.
    > > > > > Below is an example of my problem.
    > > > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > > > I need a formula that will match value H26 from the following table

    > and
    > > > > > return "Green" as the result.
    > > > > >
    > > > > >
    > > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > > H9 H10 H11 H12 H13 H14 White
    > > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > > >
    > > > > > Any help, pointers, suggestions or direction will be greatly
    > > > appreciated.
    > > > > > Thanks, - Randy
    > > >
    > > >
    > > >

    >
    >
    >


  36. #36
    Bob Phillips
    Guest

    Re: Array index, match problem

    Hi again Randy,

    That would be a single assignment to a cell, say M2, and assuming variable X
    has that lookup value

    Range("M2").Value = Application.Index(Range("$G$1:$G$6"), (Mid(X, 2, 9) - 1)
    / 7 + 1)


    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "RAP" <[email protected]> wrote in message
    news:[email protected]...
    > Bob,
    > Once again, thanks. Works great. Now, how do I get the result of the
    > formula (Red, White, etc...) pasted into yet another cell? I still have

    the
    > mind-set that the result is in a cell somewhere. As soon as I can start
    > thinking "programmatically", these simple questions will, well, at least

    they
    > should diminish.
    >
    > Thanks again,
    > Randy
    >
    > "Bob Phillips" wrote:
    >
    > > Randy,
    > >
    > > In VBA the code would look like
    > >
    > > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"),

    2,
    > > 9) - 1) / 7 + 1)
    > >
    > > If you want to use the variable X without going via J19, use
    > >
    > > Dim x
    > > x = "H30"
    > > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) /

    7 +
    > > 1)
    > >
    > >
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "RAP" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Bob,
    > > > Thanks again for the formula. I have my app running now, thanks to

    you.
    > > I
    > > > would like to ask you another question, but I think it belongs in the
    > > > "Programming" board. How would I place your formula in VB script,

    instead
    > > of
    > > > inserting it into a cell? Also, the data in the "J19 input cell" is

    being
    > > > placed there (pasted) by a range variable, "X".
    > > >
    > > > Like I said, my app is functioning, but I want to achieve results

    using
    > > more
    > > > programming and less cursoring around, like I mentioned before.
    > > >
    > > > Thanks,
    > > > Randy
    > > >
    > > >
    > > > "Bob Phillips" wrote:
    > > >
    > > > > Just for interest, two less functions
    > > > >
    > > > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > > > >
    > > > > --
    > > > >
    > > > > HTH
    > > > >
    > > > > RP
    > > > > (remove nothere from the email address if mailing direct)
    > > > >
    > > > >
    > > > > "Duke Carey" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > > > How about
    > > > > >
    > > > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > > > >
    > > > > > which assumes the colors are in cells G1:G6
    > > > > >
    > > > > >
    > > > > >
    > > > > > "RAP" wrote:
    > > > > >
    > > > > > > Hello, Folks. This is my first post to this Discussion Group.

    I
    > > must
    > > > > have
    > > > > > > Dain Bramage to not be able to come up with an answer, but I

    can't.
    > > I
    > > > > need
    > > > > > > some help.
    > > > > > > Below is an example of my problem.
    > > > > > > Input cell = J19. Input Value = H26. Formula placed in cell

    J20.
    > > > > > > I need a formula that will match value H26 from the following

    table
    > > and
    > > > > > > return "Green" as the result.
    > > > > > >
    > > > > > >
    > > > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > > > H9 H10 H11 H12 H13 H14 White
    > > > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > > > >
    > > > > > > Any help, pointers, suggestions or direction will be greatly
    > > > > appreciated.
    > > > > > > Thanks, - Randy
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




  37. #37
    Alan Beban
    Guest

    Re: Array index, match problem

    Bob Phillips wrote:
    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >

    For a formula that is independent of the size of the table of data or
    the regularity of its contents, if the functions in the freely
    downloadable file at http://home.pacbell.net are available to your workbook

    =OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))

    Alan Beban

  38. #38
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    >Bob Phillips wrote:
    >>Just for interest, two less functions
    >>
    >>=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    >
    >For a formula that is independent of the size of the table of data or
    >the regularity of its contents, if the functions in the freely
    >downloadable file at http://home.pacbell.net are available to your workbook
    >
    >=OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,
    >-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))


    Assuming someone would use your function library, wouldn't they want to
    do so efficiently? Only one udf call needed (MakeArray).

    =INDEX(DataTable,INT((MATCH(J19,MakeArray(DataTable,1),0)-1)
    /COLUMNS(DataTable))+1,COLUMNS(DataTable))


  39. #39
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    >
    >>Bob Phillips wrote:
    >>
    >>>Just for interest, two less functions
    >>>
    >>>=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    >>
    >>For a formula that is independent of the size of the table of data or
    >>the regularity of its contents, if the functions in the freely
    >>downloadable file at http://home.pacbell.net are available to your workbook
    >>
    >>=OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,
    >>-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))

    >
    >
    > Assuming someone would use your function library, wouldn't they want to
    > do so efficiently? Only one udf call needed (MakeArray).
    >
    > =INDEX(DataTable,INT((MATCH(J19,MakeArray(DataTable,1),0)-1)
    > /COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >

    Well, isn't that interesting! I would have thought the suggestion would
    be something like fill down a range on Sheet8, for example, named CxRV with

    =INDIRECT(ADDRESS(
    ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))
    (compliments of Chip Pearson)

    and then enter something like

    =INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))

    in order to use only built-in functions and avoid at all costs the
    dreaded Array Functions.

    But no, here's Harlan Grove, instead carping about which of the dreaded
    Array Functions is more efficient. Onward and upward!

    Alan Beban

  40. #40
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >But no, here's Harlan Grove, instead carping about which of the dreaded
    >Array Functions is more efficient. Onward and upward!


    Just pointing out that you don't know how to use your own function
    library efficiently.

    Onward perhaps. Never upward.


  41. #41
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >=INDIRECT(ADDRESS(
    >ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),
    >COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))

    ....
    >=INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >
    >in order to use only built-in functions and avoid at all costs the
    >dreaded Array Functions.

    ....

    Ugh! Not the best way by a long shot! All it takes is a single array
    formula

    =INDEX(DataTable,MATCH(TRUE,COUNTIF(OFFSET(DataTable,
    ROW(DataTable)-CELL("Row",DataTable),0,1,),J1)>0,0),COLUMNS(DataTable))


  42. #42
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>=INDIRECT(ADDRESS(
    >>ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),
    >>COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))

    >
    > ...
    >
    >>=INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >>
    >>in order to use only built-in functions and avoid at all costs the
    >>dreaded Array Functions.

    >
    > ...
    >
    > Ugh! Not the best way by a long shot! All it takes is a single array
    > formula
    >
    > =INDEX(DataTable,MATCH(TRUE,COUNTIF(OFFSET(DataTable,
    > ROW(DataTable)-CELL("Row",DataTable),0,1,),J1)>0,0),COLUMNS(DataTable))
    >


    J1 should be J19 to conform to the original specification.

    Alan Beban

  43. #43
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>But no, here's Harlan Grove, instead carping about which of the dreaded
    >>Array Functions is more efficient. Onward and upward!

    >
    >
    > Just pointing out that you don't know how to use your own function
    > library efficiently.
    >


    Perhaps you could quantify for the users the difference in efficiency.

    Alan Beban

  44. #44
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >Perhaps you could quantify for the users the difference in efficiency.


    Nah, I'll leave that for you as an exercise since you're the one who
    needs to learn about efficiency. Here's a hint: one udf call will
    invariably be faster than two, even when there are a few extra built-in
    function calls with the single udf call.


  45. #45
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>Perhaps you could quantify for the users the difference in efficiency.

    >
    >
    > Nah, I'll leave that for you as an exercise since you're the one who
    > needs to learn about efficiency.


    Cute. But I suspect the real reason you don't want to deal with it is
    that the so-called "efficiency" to which you and many
    programmer/developers sometimes refer often involves nanoseconds of
    difference that are totally irrelevant to most users in most
    applications; interesting to you for purposes of posting oneupmanship,
    but somewhat misleading for users generally.

    Alan Beban

  46. #46
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ..=2E.
    >Cute. But I suspect the real reason you don't want to deal with it is
    >that the so-called "efficiency" to which you and many
    >programmer/developers sometimes refer often involves nanoseconds of
    >difference that are totally irrelevant to most users in most
    >applications; interesting to you for purposes of posting oneupmanship,
    >but somewhat misleading for users generally.


    Fine. Then consider whether the MakeArray formula,

    =3DINDEX(DataTable,INT((MATCH(J1=AD9,MakeArray(DataTable,1),0)-1)
    /COLUMNS(DataTable))+1,COLUMNS=AD(DataTable))

    a single MATCH against the data range transformed into a 1D array, with
    the result adjusted by a division inside INT to return the row number,
    against the ArrayMatch formula,

    =3DOFFSET(INDIRECT(ArrayMatch(J=AD19,dataTable,"A")),0,
    -INDEX(ArrayMatch(J19,dataTab=ADle),1,2)+COLUMNS(dataTable))

    first returning the cell address of the matching cell then using
    another call to fix the column offset. It's subjective whether the row
    index contortions of the MakeArray formula are more obscure than the
    column offset contortions of the 2 ArrayMatch formula.

    For that matter, you could also have used

    =3DINDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))

    which would have been a LOT simpler than either of the others.
    Simplicity is good.

    Both the MakeArray and the single ArrayMatch formulas involve no
    volatile function calls, so they won't cause Excel to prompt users to
    save any file containing them if users try to close such workbooks
    without making any changes. Your two ArrayMatch formula, due to OFFSET
    and INDIRECT calls, would cause such confusing prompts.

    Is that an acceptable user consideration?


  47. #47
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>Cute. But I suspect the real reason you don't want to deal with it is
    >>that the so-called "efficiency" to which you and many
    >>programmer/developers sometimes refer often involves nanoseconds of
    >>difference that are totally irrelevant to most users in most
    >>applications; interesting to you for purposes of posting oneupmanship,
    >>but somewhat misleading for users generally.

    >
    >
    > Fine. Then consider whether the MakeArray formula,
    >
    > =INDEX(DataTable,INT((MATCH(J1*9,MakeArray(DataTable,1),0)-1)
    > /COLUMNS(DataTable))+1,COLUMNS*(DataTable))
    >
    > a single MATCH against the data range transformed into a 1D array, with
    > the result adjusted by a division inside INT to return the row number,
    > against the ArrayMatch formula,
    >
    > =OFFSET(INDIRECT(ArrayMatch(J*19,dataTable,"A")),0,
    > -INDEX(ArrayMatch(J19,dataTab*le),1,2)+COLUMNS(dataTable))
    >
    > first returning the cell address of the matching cell then using
    > another call to fix the column offset. It's subjective whether the row
    > index contortions of the MakeArray formula are more obscure than the
    > column offset contortions of the 2 ArrayMatch formula.
    >
    > For that matter, you could also have used
    >
    > =INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))
    >
    > which would have been a LOT simpler than either of the others.
    > Simplicity is good.
    >
    > Both the MakeArray and the single ArrayMatch formulas involve no
    > volatile function calls, so they won't cause Excel to prompt users to
    > save any file containing them if users try to close such workbooks
    > without making any changes. Your two ArrayMatch formula, due to OFFSET
    > and INDIRECT calls, would cause such confusing prompts.
    >
    > Is that an acceptable user consideration?
    >

    It's certainly more constructive, particularly the suggestion of

    =INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))

    which is significantly faster than the one including the two ArrayMatch
    function calls that I originally posted. Would have been nice had you
    focused on the instructional value for the users in the first place,
    rather than just on stroking your ego. But then, there you go!

    By the way, the formula with the MakeArray function call seems to return
    an error if the data table exceeds 65536 elements (I haven't yet
    identified why; it might be fixable), while neither of the ArrayMatch
    formulas seems to--though they are slower.

    Alan Beban

  48. #48
    Alan Beban
    Guest

    Re: Array index, match problem

    Alan Beban wrote:
    > Harlan Grove wrote:
    > . . .Then consider whether the MakeArray formula,
    >>
    >> =INDEX(DataTable,INT((MATCH(J1*9,MakeArray(DataTable,1),0)-1)
    >> /COLUMNS(DataTable))+1,COLUMNS*(DataTable))
    >>
    >> a single MATCH against the data range transformed into a 1D array, with
    >> the result adjusted by a division inside INT to return the row number,
    >> against the ArrayMatch formula,
    >>
    >> =OFFSET(INDIRECT(ArrayMatch(J*19,dataTable,"A")),0,
    >> -INDEX(ArrayMatch(J19,dataTab*le),1,2)+COLUMNS(dataTable))
    >>
    >> first returning the cell address of the matching cell then using
    >> another call to fix the column offset. It's subjective whether the row
    >> index contortions of the MakeArray formula are more obscure than the
    >> column offset contortions of the 2 ArrayMatch formula.
    >>
    >>...By the way, the formula with the MakeArray function call seems to return

    > an error if the data table exceeds 65536 elements . . . .


    The problem is with the built-in INDEX function; it fails if the array
    or reference contains more than 65536 elements.

    Alan Beban

  49. #49
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >The problem is with the built-in INDEX function; it fails if the array
    >or reference contains more than 65536 elements.


    Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    chokes after 65,535 elements.

    Which would argue in favor of using the COUNTIF function provided in
    another branch of this thread. It's one drawback is the volatile OFFSET
    call. It'd always recalc, but it'd be lots faster than even a single
    udf call.


  50. #50
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >=INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))
    >
    >which is significantly faster than the one including the two ArrayMatch
    >function calls that I originally posted. Would have been nice had you
    >focused on the instructional value for the users in the first place,
    >rather than just on stroking your ego. But then, there you go!


    Of course you could have offerred it too, if you had thought of it. But
    then, there you go!

    >By the way, the formula with the MakeArray function call seems to return
    >an error if the data table exceeds 65536 elements (I haven't yet
    >identified why; it might be fixable), while neither of the ArrayMatch
    >formulas seems to--though they are slower.


    Excel can't handle any arrays with more than 65,535 entries in either
    of 1 or 2 dimensions, as you should know. It's questionable whether
    anyone should try to use brute force matching on so many cells. It'd be
    slow even without any udfs or volatile functions. There are tasks for
    which indexed database searches would be far more appropriate than
    unindexed spreadsheet searches. If the OP has so many entries to
    search, the OP is being foolish using a spreadsheet for the task.
    However, if the OP is only searching a few hundred entries or fewer,
    your caveat provides completeness of specification but is of no
    practical relevance.


  51. #51
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>The problem is with the built-in INDEX function; it fails if the array
    >>or reference contains more than 65536 elements.

    >
    >
    > Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    > chokes after 65,535 elements. . . .


    Dim arr, i
    ReDim arr(1 To 65536)
    For i = 1 To 65536
    arr(i) = i * 2
    Next
    MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.

    In xl2000 and earlier it fails on 5462 elements or greater.

    Alan Beban

  52. #52
    Alan Beban
    Guest

    Re: Array index, match problem

    Alan Beban wrote:
    > Harlan Grove wrote:
    >
    >> Alan Beban wrote...
    >> ...
    >>
    >>> The problem is with the built-in INDEX function; it fails if the array
    >>> or reference contains more than 65536 elements.

    >>
    >>
    >>
    >> Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >> chokes after 65,535 elements. . . .

    >
    >
    > Dim arr, i
    > ReDim arr(1 To 65536)
    > For i = 1 To 65536
    > arr(i) = i * 2
    > Next
    > MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >
    > In xl2000 and earlier it fails on 5462 elements or greater.
    >
    > Alan Beban


    Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    xl2000 or xl2002).

    Alan Beban

  53. #53
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    >>>Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >>>chokes after 65,535 elements. . . .

    >>
    >> Dim arr, i
    >> ReDim arr(1 To 65536)
    >> For i = 1 To 65536
    >> arr(i) = i * 2
    >> Next
    >> MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >>
    >> In xl2000 and earlier it fails on 5462 elements or greater.

    >
    >Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    >xl2000 or xl2002).


    Not comparable. A:B is a range, not an array. But you're correct that
    INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and
    subsequent arguments and converts them to long integers.


  54. #54
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    >
    >>>>Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >>>>chokes after 65,535 elements. . . .
    >>>
    >>> Dim arr, i
    >>> ReDim arr(1 To 65536)
    >>> For i = 1 To 65536
    >>> arr(i) = i * 2
    >>> Next
    >>> MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >>>
    >>>In xl2000 and earlier it fails on 5462 elements or greater.

    >>
    >>Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    >>xl2000 or xl2002).

    >
    >
    > Not comparable. A:B is a range, not an array. But you're correct that
    > INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and
    > subsequent arguments and converts them to long integers.
    >


    It seems that the limitation on the VBA invocation of the INDEX function
    is a bit subtler; it is not limited by the number of the elements in the
    array (see arr1 below) but apparently by the number of elements in a
    dimension.

    Sub testIt3a()
    Dim arr1, arr2, arr3
    Dim i As Long, j As Long
    Dim x, y, z

    '65536 rows, 2 columns
    ReDim arr1(1 To 65536, 1 To 2)
    For i = 1 To 65536: For j = 1 To 2
    arr1(i, j) = i * 2 + j
    Next: Next

    '1 row, 65536 columns
    ReDim arr2(1 To 65536)
    For i = 1 To 65536
    arr2(i) = i
    Next

    '1 row, 65537 columns
    ReDim arr3(1 To 65537)
    For i = 1 To 65537
    arr3(i) = i
    Next

    x = Application.Index(arr1, 65536, 2)
    Debug.Print x '<---returns 131074
    y = Application.Index(arr2, 65536)
    Debug.Print y '<---returns 65536
    z = Application.Index(arr3, 65536) 'Type mismatch error
    End Sub

    Alan Beban

  55. #55
    Duke Carey
    Guest

    RE: Array index, match problem

    How about

    =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))

    which assumes the colors are in cells G1:G6



    "RAP" wrote:

    > Hello, Folks. This is my first post to this Discussion Group. I must have
    > Dain Bramage to not be able to come up with an answer, but I can't. I need
    > some help.
    > Below is an example of my problem.
    > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > I need a formula that will match value H26 from the following table and
    > return "Green" as the result.
    >
    >
    > H2 H3 H4 H5 H6 H7 Red
    > H9 H10 H11 H12 H13 H14 White
    > H16 H17 H18 H19 H20 H21 Blue
    > H23 H24 H25 H26 H27 H28 Green
    > H30 H31 H32 H33 H34 H35 Black
    > H37 H38 H39 H40 H41 H42 Purple
    >
    > Any help, pointers, suggestions or direction will be greatly appreciated.
    > Thanks, - Randy


  56. #56
    RAP
    Guest

    RE: Array index, match problem

    Duke,
    You are awesome! I haven't even heard of all those commands. Worked like a
    charm. Thank you so much. - Randy

    "Duke Carey" wrote:

    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly appreciated.
    > > Thanks, - Randy


  57. #57
    Bob Phillips
    Guest

    Re: Array index, match problem



    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Duke Carey" <[email protected]> wrote in message
    news:[email protected]...
    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must

    have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I

    need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly

    appreciated.
    > > Thanks, - Randy




  58. #58
    Bob Phillips
    Guest

    Re: Array index, match problem

    Just for interest, two less functions

    =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Duke Carey" <[email protected]> wrote in message
    news:[email protected]...
    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must

    have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I

    need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly

    appreciated.
    > > Thanks, - Randy




  59. #59
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Thanks a lot for the formula. I will start to disect it and try to learn as
    much as I can from it. I appreciate it.
    Randy

    "Bob Phillips" wrote:

    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Duke Carey" <[email protected]> wrote in message
    > news:[email protected]...
    > > How about
    > >
    > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > >
    > > which assumes the colors are in cells G1:G6
    > >
    > >
    > >
    > > "RAP" wrote:
    > >
    > > > Hello, Folks. This is my first post to this Discussion Group. I must

    > have
    > > > Dain Bramage to not be able to come up with an answer, but I can't. I

    > need
    > > > some help.
    > > > Below is an example of my problem.
    > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > I need a formula that will match value H26 from the following table and
    > > > return "Green" as the result.
    > > >
    > > >
    > > > H2 H3 H4 H5 H6 H7 Red
    > > > H9 H10 H11 H12 H13 H14 White
    > > > H16 H17 H18 H19 H20 H21 Blue
    > > > H23 H24 H25 H26 H27 H28 Green
    > > > H30 H31 H32 H33 H34 H35 Black
    > > > H37 H38 H39 H40 H41 H42 Purple
    > > >
    > > > Any help, pointers, suggestions or direction will be greatly

    > appreciated.
    > > > Thanks, - Randy

    >
    >
    >


  60. #60
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Thanks again for the formula. I have my app running now, thanks to you. I
    would like to ask you another question, but I think it belongs in the
    "Programming" board. How would I place your formula in VB script, instead of
    inserting it into a cell? Also, the data in the "J19 input cell" is being
    placed there (pasted) by a range variable, "X".

    Like I said, my app is functioning, but I want to achieve results using more
    programming and less cursoring around, like I mentioned before.

    Thanks,
    Randy


    "Bob Phillips" wrote:

    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Duke Carey" <[email protected]> wrote in message
    > news:[email protected]...
    > > How about
    > >
    > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > >
    > > which assumes the colors are in cells G1:G6
    > >
    > >
    > >
    > > "RAP" wrote:
    > >
    > > > Hello, Folks. This is my first post to this Discussion Group. I must

    > have
    > > > Dain Bramage to not be able to come up with an answer, but I can't. I

    > need
    > > > some help.
    > > > Below is an example of my problem.
    > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > I need a formula that will match value H26 from the following table and
    > > > return "Green" as the result.
    > > >
    > > >
    > > > H2 H3 H4 H5 H6 H7 Red
    > > > H9 H10 H11 H12 H13 H14 White
    > > > H16 H17 H18 H19 H20 H21 Blue
    > > > H23 H24 H25 H26 H27 H28 Green
    > > > H30 H31 H32 H33 H34 H35 Black
    > > > H37 H38 H39 H40 H41 H42 Purple
    > > >
    > > > Any help, pointers, suggestions or direction will be greatly

    > appreciated.
    > > > Thanks, - Randy

    >
    >
    >


  61. #61
    Bob Phillips
    Guest

    Re: Array index, match problem

    Randy,

    In VBA the code would look like

    Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"), 2,
    9) - 1) / 7 + 1)

    If you want to use the variable X without going via J19, use

    Dim x
    x = "H30"
    Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) / 7 +
    1)



    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "RAP" <[email protected]> wrote in message
    news:[email protected]...
    > Bob,
    > Thanks again for the formula. I have my app running now, thanks to you.

    I
    > would like to ask you another question, but I think it belongs in the
    > "Programming" board. How would I place your formula in VB script, instead

    of
    > inserting it into a cell? Also, the data in the "J19 input cell" is being
    > placed there (pasted) by a range variable, "X".
    >
    > Like I said, my app is functioning, but I want to achieve results using

    more
    > programming and less cursoring around, like I mentioned before.
    >
    > Thanks,
    > Randy
    >
    >
    > "Bob Phillips" wrote:
    >
    > > Just for interest, two less functions
    > >
    > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Duke Carey" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > How about
    > > >
    > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > >
    > > > which assumes the colors are in cells G1:G6
    > > >
    > > >
    > > >
    > > > "RAP" wrote:
    > > >
    > > > > Hello, Folks. This is my first post to this Discussion Group. I

    must
    > > have
    > > > > Dain Bramage to not be able to come up with an answer, but I can't.

    I
    > > need
    > > > > some help.
    > > > > Below is an example of my problem.
    > > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > > I need a formula that will match value H26 from the following table

    and
    > > > > return "Green" as the result.
    > > > >
    > > > >
    > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > H9 H10 H11 H12 H13 H14 White
    > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > >
    > > > > Any help, pointers, suggestions or direction will be greatly

    > > appreciated.
    > > > > Thanks, - Randy

    > >
    > >
    > >




  62. #62
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Once again, thanks. Works great. Now, how do I get the result of the
    formula (Red, White, etc...) pasted into yet another cell? I still have the
    mind-set that the result is in a cell somewhere. As soon as I can start
    thinking "programmatically", these simple questions will, well, at least they
    should diminish.

    Thanks again,
    Randy

    "Bob Phillips" wrote:

    > Randy,
    >
    > In VBA the code would look like
    >
    > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"), 2,
    > 9) - 1) / 7 + 1)
    >
    > If you want to use the variable X without going via J19, use
    >
    > Dim x
    > x = "H30"
    > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) / 7 +
    > 1)
    >
    >
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "RAP" <[email protected]> wrote in message
    > news:[email protected]...
    > > Bob,
    > > Thanks again for the formula. I have my app running now, thanks to you.

    > I
    > > would like to ask you another question, but I think it belongs in the
    > > "Programming" board. How would I place your formula in VB script, instead

    > of
    > > inserting it into a cell? Also, the data in the "J19 input cell" is being
    > > placed there (pasted) by a range variable, "X".
    > >
    > > Like I said, my app is functioning, but I want to achieve results using

    > more
    > > programming and less cursoring around, like I mentioned before.
    > >
    > > Thanks,
    > > Randy
    > >
    > >
    > > "Bob Phillips" wrote:
    > >
    > > > Just for interest, two less functions
    > > >
    > > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > > >
    > > > --
    > > >
    > > > HTH
    > > >
    > > > RP
    > > > (remove nothere from the email address if mailing direct)
    > > >
    > > >
    > > > "Duke Carey" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > How about
    > > > >
    > > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > > >
    > > > > which assumes the colors are in cells G1:G6
    > > > >
    > > > >
    > > > >
    > > > > "RAP" wrote:
    > > > >
    > > > > > Hello, Folks. This is my first post to this Discussion Group. I

    > must
    > > > have
    > > > > > Dain Bramage to not be able to come up with an answer, but I can't.

    > I
    > > > need
    > > > > > some help.
    > > > > > Below is an example of my problem.
    > > > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > > > I need a formula that will match value H26 from the following table

    > and
    > > > > > return "Green" as the result.
    > > > > >
    > > > > >
    > > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > > H9 H10 H11 H12 H13 H14 White
    > > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > > >
    > > > > > Any help, pointers, suggestions or direction will be greatly
    > > > appreciated.
    > > > > > Thanks, - Randy
    > > >
    > > >
    > > >

    >
    >
    >


  63. #63
    Bob Phillips
    Guest

    Re: Array index, match problem

    Hi again Randy,

    That would be a single assignment to a cell, say M2, and assuming variable X
    has that lookup value

    Range("M2").Value = Application.Index(Range("$G$1:$G$6"), (Mid(X, 2, 9) - 1)
    / 7 + 1)


    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "RAP" <[email protected]> wrote in message
    news:[email protected]...
    > Bob,
    > Once again, thanks. Works great. Now, how do I get the result of the
    > formula (Red, White, etc...) pasted into yet another cell? I still have

    the
    > mind-set that the result is in a cell somewhere. As soon as I can start
    > thinking "programmatically", these simple questions will, well, at least

    they
    > should diminish.
    >
    > Thanks again,
    > Randy
    >
    > "Bob Phillips" wrote:
    >
    > > Randy,
    > >
    > > In VBA the code would look like
    > >
    > > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"),

    2,
    > > 9) - 1) / 7 + 1)
    > >
    > > If you want to use the variable X without going via J19, use
    > >
    > > Dim x
    > > x = "H30"
    > > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) /

    7 +
    > > 1)
    > >
    > >
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "RAP" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Bob,
    > > > Thanks again for the formula. I have my app running now, thanks to

    you.
    > > I
    > > > would like to ask you another question, but I think it belongs in the
    > > > "Programming" board. How would I place your formula in VB script,

    instead
    > > of
    > > > inserting it into a cell? Also, the data in the "J19 input cell" is

    being
    > > > placed there (pasted) by a range variable, "X".
    > > >
    > > > Like I said, my app is functioning, but I want to achieve results

    using
    > > more
    > > > programming and less cursoring around, like I mentioned before.
    > > >
    > > > Thanks,
    > > > Randy
    > > >
    > > >
    > > > "Bob Phillips" wrote:
    > > >
    > > > > Just for interest, two less functions
    > > > >
    > > > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > > > >
    > > > > --
    > > > >
    > > > > HTH
    > > > >
    > > > > RP
    > > > > (remove nothere from the email address if mailing direct)
    > > > >
    > > > >
    > > > > "Duke Carey" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > > > How about
    > > > > >
    > > > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > > > >
    > > > > > which assumes the colors are in cells G1:G6
    > > > > >
    > > > > >
    > > > > >
    > > > > > "RAP" wrote:
    > > > > >
    > > > > > > Hello, Folks. This is my first post to this Discussion Group.

    I
    > > must
    > > > > have
    > > > > > > Dain Bramage to not be able to come up with an answer, but I

    can't.
    > > I
    > > > > need
    > > > > > > some help.
    > > > > > > Below is an example of my problem.
    > > > > > > Input cell = J19. Input Value = H26. Formula placed in cell

    J20.
    > > > > > > I need a formula that will match value H26 from the following

    table
    > > and
    > > > > > > return "Green" as the result.
    > > > > > >
    > > > > > >
    > > > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > > > H9 H10 H11 H12 H13 H14 White
    > > > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > > > >
    > > > > > > Any help, pointers, suggestions or direction will be greatly
    > > > > appreciated.
    > > > > > > Thanks, - Randy
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




  64. #64
    Alan Beban
    Guest

    Re: Array index, match problem

    Bob Phillips wrote:
    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >

    For a formula that is independent of the size of the table of data or
    the regularity of its contents, if the functions in the freely
    downloadable file at http://home.pacbell.net are available to your workbook

    =OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))

    Alan Beban

  65. #65
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    >Bob Phillips wrote:
    >>Just for interest, two less functions
    >>
    >>=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    >
    >For a formula that is independent of the size of the table of data or
    >the regularity of its contents, if the functions in the freely
    >downloadable file at http://home.pacbell.net are available to your workbook
    >
    >=OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,
    >-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))


    Assuming someone would use your function library, wouldn't they want to
    do so efficiently? Only one udf call needed (MakeArray).

    =INDEX(DataTable,INT((MATCH(J19,MakeArray(DataTable,1),0)-1)
    /COLUMNS(DataTable))+1,COLUMNS(DataTable))


  66. #66
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    >
    >>Bob Phillips wrote:
    >>
    >>>Just for interest, two less functions
    >>>
    >>>=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    >>
    >>For a formula that is independent of the size of the table of data or
    >>the regularity of its contents, if the functions in the freely
    >>downloadable file at http://home.pacbell.net are available to your workbook
    >>
    >>=OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,
    >>-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))

    >
    >
    > Assuming someone would use your function library, wouldn't they want to
    > do so efficiently? Only one udf call needed (MakeArray).
    >
    > =INDEX(DataTable,INT((MATCH(J19,MakeArray(DataTable,1),0)-1)
    > /COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >

    Well, isn't that interesting! I would have thought the suggestion would
    be something like fill down a range on Sheet8, for example, named CxRV with

    =INDIRECT(ADDRESS(
    ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))
    (compliments of Chip Pearson)

    and then enter something like

    =INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))

    in order to use only built-in functions and avoid at all costs the
    dreaded Array Functions.

    But no, here's Harlan Grove, instead carping about which of the dreaded
    Array Functions is more efficient. Onward and upward!

    Alan Beban

  67. #67
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >But no, here's Harlan Grove, instead carping about which of the dreaded
    >Array Functions is more efficient. Onward and upward!


    Just pointing out that you don't know how to use your own function
    library efficiently.

    Onward perhaps. Never upward.


  68. #68
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >=INDIRECT(ADDRESS(
    >ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),
    >COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))

    ....
    >=INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >
    >in order to use only built-in functions and avoid at all costs the
    >dreaded Array Functions.

    ....

    Ugh! Not the best way by a long shot! All it takes is a single array
    formula

    =INDEX(DataTable,MATCH(TRUE,COUNTIF(OFFSET(DataTable,
    ROW(DataTable)-CELL("Row",DataTable),0,1,),J1)>0,0),COLUMNS(DataTable))


  69. #69
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>=INDIRECT(ADDRESS(
    >>ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),
    >>COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))

    >
    > ...
    >
    >>=INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >>
    >>in order to use only built-in functions and avoid at all costs the
    >>dreaded Array Functions.

    >
    > ...
    >
    > Ugh! Not the best way by a long shot! All it takes is a single array
    > formula
    >
    > =INDEX(DataTable,MATCH(TRUE,COUNTIF(OFFSET(DataTable,
    > ROW(DataTable)-CELL("Row",DataTable),0,1,),J1)>0,0),COLUMNS(DataTable))
    >


    J1 should be J19 to conform to the original specification.

    Alan Beban

  70. #70
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>But no, here's Harlan Grove, instead carping about which of the dreaded
    >>Array Functions is more efficient. Onward and upward!

    >
    >
    > Just pointing out that you don't know how to use your own function
    > library efficiently.
    >


    Perhaps you could quantify for the users the difference in efficiency.

    Alan Beban

  71. #71
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >Perhaps you could quantify for the users the difference in efficiency.


    Nah, I'll leave that for you as an exercise since you're the one who
    needs to learn about efficiency. Here's a hint: one udf call will
    invariably be faster than two, even when there are a few extra built-in
    function calls with the single udf call.


  72. #72
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>Perhaps you could quantify for the users the difference in efficiency.

    >
    >
    > Nah, I'll leave that for you as an exercise since you're the one who
    > needs to learn about efficiency.


    Cute. But I suspect the real reason you don't want to deal with it is
    that the so-called "efficiency" to which you and many
    programmer/developers sometimes refer often involves nanoseconds of
    difference that are totally irrelevant to most users in most
    applications; interesting to you for purposes of posting oneupmanship,
    but somewhat misleading for users generally.

    Alan Beban

  73. #73
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ..=2E.
    >Cute. But I suspect the real reason you don't want to deal with it is
    >that the so-called "efficiency" to which you and many
    >programmer/developers sometimes refer often involves nanoseconds of
    >difference that are totally irrelevant to most users in most
    >applications; interesting to you for purposes of posting oneupmanship,
    >but somewhat misleading for users generally.


    Fine. Then consider whether the MakeArray formula,

    =3DINDEX(DataTable,INT((MATCH(J1=AD9,MakeArray(DataTable,1),0)-1)
    /COLUMNS(DataTable))+1,COLUMNS=AD(DataTable))

    a single MATCH against the data range transformed into a 1D array, with
    the result adjusted by a division inside INT to return the row number,
    against the ArrayMatch formula,

    =3DOFFSET(INDIRECT(ArrayMatch(J=AD19,dataTable,"A")),0,
    -INDEX(ArrayMatch(J19,dataTab=ADle),1,2)+COLUMNS(dataTable))

    first returning the cell address of the matching cell then using
    another call to fix the column offset. It's subjective whether the row
    index contortions of the MakeArray formula are more obscure than the
    column offset contortions of the 2 ArrayMatch formula.

    For that matter, you could also have used

    =3DINDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))

    which would have been a LOT simpler than either of the others.
    Simplicity is good.

    Both the MakeArray and the single ArrayMatch formulas involve no
    volatile function calls, so they won't cause Excel to prompt users to
    save any file containing them if users try to close such workbooks
    without making any changes. Your two ArrayMatch formula, due to OFFSET
    and INDIRECT calls, would cause such confusing prompts.

    Is that an acceptable user consideration?


  74. #74
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>Cute. But I suspect the real reason you don't want to deal with it is
    >>that the so-called "efficiency" to which you and many
    >>programmer/developers sometimes refer often involves nanoseconds of
    >>difference that are totally irrelevant to most users in most
    >>applications; interesting to you for purposes of posting oneupmanship,
    >>but somewhat misleading for users generally.

    >
    >
    > Fine. Then consider whether the MakeArray formula,
    >
    > =INDEX(DataTable,INT((MATCH(J1*9,MakeArray(DataTable,1),0)-1)
    > /COLUMNS(DataTable))+1,COLUMNS*(DataTable))
    >
    > a single MATCH against the data range transformed into a 1D array, with
    > the result adjusted by a division inside INT to return the row number,
    > against the ArrayMatch formula,
    >
    > =OFFSET(INDIRECT(ArrayMatch(J*19,dataTable,"A")),0,
    > -INDEX(ArrayMatch(J19,dataTab*le),1,2)+COLUMNS(dataTable))
    >
    > first returning the cell address of the matching cell then using
    > another call to fix the column offset. It's subjective whether the row
    > index contortions of the MakeArray formula are more obscure than the
    > column offset contortions of the 2 ArrayMatch formula.
    >
    > For that matter, you could also have used
    >
    > =INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))
    >
    > which would have been a LOT simpler than either of the others.
    > Simplicity is good.
    >
    > Both the MakeArray and the single ArrayMatch formulas involve no
    > volatile function calls, so they won't cause Excel to prompt users to
    > save any file containing them if users try to close such workbooks
    > without making any changes. Your two ArrayMatch formula, due to OFFSET
    > and INDIRECT calls, would cause such confusing prompts.
    >
    > Is that an acceptable user consideration?
    >

    It's certainly more constructive, particularly the suggestion of

    =INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))

    which is significantly faster than the one including the two ArrayMatch
    function calls that I originally posted. Would have been nice had you
    focused on the instructional value for the users in the first place,
    rather than just on stroking your ego. But then, there you go!

    By the way, the formula with the MakeArray function call seems to return
    an error if the data table exceeds 65536 elements (I haven't yet
    identified why; it might be fixable), while neither of the ArrayMatch
    formulas seems to--though they are slower.

    Alan Beban

  75. #75
    Alan Beban
    Guest

    Re: Array index, match problem

    Alan Beban wrote:
    > Harlan Grove wrote:
    > . . .Then consider whether the MakeArray formula,
    >>
    >> =INDEX(DataTable,INT((MATCH(J1*9,MakeArray(DataTable,1),0)-1)
    >> /COLUMNS(DataTable))+1,COLUMNS*(DataTable))
    >>
    >> a single MATCH against the data range transformed into a 1D array, with
    >> the result adjusted by a division inside INT to return the row number,
    >> against the ArrayMatch formula,
    >>
    >> =OFFSET(INDIRECT(ArrayMatch(J*19,dataTable,"A")),0,
    >> -INDEX(ArrayMatch(J19,dataTab*le),1,2)+COLUMNS(dataTable))
    >>
    >> first returning the cell address of the matching cell then using
    >> another call to fix the column offset. It's subjective whether the row
    >> index contortions of the MakeArray formula are more obscure than the
    >> column offset contortions of the 2 ArrayMatch formula.
    >>
    >>...By the way, the formula with the MakeArray function call seems to return

    > an error if the data table exceeds 65536 elements . . . .


    The problem is with the built-in INDEX function; it fails if the array
    or reference contains more than 65536 elements.

    Alan Beban

  76. #76
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >The problem is with the built-in INDEX function; it fails if the array
    >or reference contains more than 65536 elements.


    Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    chokes after 65,535 elements.

    Which would argue in favor of using the COUNTIF function provided in
    another branch of this thread. It's one drawback is the volatile OFFSET
    call. It'd always recalc, but it'd be lots faster than even a single
    udf call.


  77. #77
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >=INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))
    >
    >which is significantly faster than the one including the two ArrayMatch
    >function calls that I originally posted. Would have been nice had you
    >focused on the instructional value for the users in the first place,
    >rather than just on stroking your ego. But then, there you go!


    Of course you could have offerred it too, if you had thought of it. But
    then, there you go!

    >By the way, the formula with the MakeArray function call seems to return
    >an error if the data table exceeds 65536 elements (I haven't yet
    >identified why; it might be fixable), while neither of the ArrayMatch
    >formulas seems to--though they are slower.


    Excel can't handle any arrays with more than 65,535 entries in either
    of 1 or 2 dimensions, as you should know. It's questionable whether
    anyone should try to use brute force matching on so many cells. It'd be
    slow even without any udfs or volatile functions. There are tasks for
    which indexed database searches would be far more appropriate than
    unindexed spreadsheet searches. If the OP has so many entries to
    search, the OP is being foolish using a spreadsheet for the task.
    However, if the OP is only searching a few hundred entries or fewer,
    your caveat provides completeness of specification but is of no
    practical relevance.


  78. #78
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>The problem is with the built-in INDEX function; it fails if the array
    >>or reference contains more than 65536 elements.

    >
    >
    > Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    > chokes after 65,535 elements. . . .


    Dim arr, i
    ReDim arr(1 To 65536)
    For i = 1 To 65536
    arr(i) = i * 2
    Next
    MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.

    In xl2000 and earlier it fails on 5462 elements or greater.

    Alan Beban

  79. #79
    Alan Beban
    Guest

    Re: Array index, match problem

    Alan Beban wrote:
    > Harlan Grove wrote:
    >
    >> Alan Beban wrote...
    >> ...
    >>
    >>> The problem is with the built-in INDEX function; it fails if the array
    >>> or reference contains more than 65536 elements.

    >>
    >>
    >>
    >> Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >> chokes after 65,535 elements. . . .

    >
    >
    > Dim arr, i
    > ReDim arr(1 To 65536)
    > For i = 1 To 65536
    > arr(i) = i * 2
    > Next
    > MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >
    > In xl2000 and earlier it fails on 5462 elements or greater.
    >
    > Alan Beban


    Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    xl2000 or xl2002).

    Alan Beban

  80. #80
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    >>>Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >>>chokes after 65,535 elements. . . .

    >>
    >> Dim arr, i
    >> ReDim arr(1 To 65536)
    >> For i = 1 To 65536
    >> arr(i) = i * 2
    >> Next
    >> MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >>
    >> In xl2000 and earlier it fails on 5462 elements or greater.

    >
    >Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    >xl2000 or xl2002).


    Not comparable. A:B is a range, not an array. But you're correct that
    INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and
    subsequent arguments and converts them to long integers.


  81. #81
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    >
    >>>>Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >>>>chokes after 65,535 elements. . . .
    >>>
    >>> Dim arr, i
    >>> ReDim arr(1 To 65536)
    >>> For i = 1 To 65536
    >>> arr(i) = i * 2
    >>> Next
    >>> MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >>>
    >>>In xl2000 and earlier it fails on 5462 elements or greater.

    >>
    >>Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    >>xl2000 or xl2002).

    >
    >
    > Not comparable. A:B is a range, not an array. But you're correct that
    > INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and
    > subsequent arguments and converts them to long integers.
    >


    It seems that the limitation on the VBA invocation of the INDEX function
    is a bit subtler; it is not limited by the number of the elements in the
    array (see arr1 below) but apparently by the number of elements in a
    dimension.

    Sub testIt3a()
    Dim arr1, arr2, arr3
    Dim i As Long, j As Long
    Dim x, y, z

    '65536 rows, 2 columns
    ReDim arr1(1 To 65536, 1 To 2)
    For i = 1 To 65536: For j = 1 To 2
    arr1(i, j) = i * 2 + j
    Next: Next

    '1 row, 65536 columns
    ReDim arr2(1 To 65536)
    For i = 1 To 65536
    arr2(i) = i
    Next

    '1 row, 65537 columns
    ReDim arr3(1 To 65537)
    For i = 1 To 65537
    arr3(i) = i
    Next

    x = Application.Index(arr1, 65536, 2)
    Debug.Print x '<---returns 131074
    y = Application.Index(arr2, 65536)
    Debug.Print y '<---returns 65536
    z = Application.Index(arr3, 65536) 'Type mismatch error
    End Sub

    Alan Beban

  82. #82
    Duke Carey
    Guest

    RE: Array index, match problem

    How about

    =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))

    which assumes the colors are in cells G1:G6



    "RAP" wrote:

    > Hello, Folks. This is my first post to this Discussion Group. I must have
    > Dain Bramage to not be able to come up with an answer, but I can't. I need
    > some help.
    > Below is an example of my problem.
    > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > I need a formula that will match value H26 from the following table and
    > return "Green" as the result.
    >
    >
    > H2 H3 H4 H5 H6 H7 Red
    > H9 H10 H11 H12 H13 H14 White
    > H16 H17 H18 H19 H20 H21 Blue
    > H23 H24 H25 H26 H27 H28 Green
    > H30 H31 H32 H33 H34 H35 Black
    > H37 H38 H39 H40 H41 H42 Purple
    >
    > Any help, pointers, suggestions or direction will be greatly appreciated.
    > Thanks, - Randy


  83. #83
    RAP
    Guest

    RE: Array index, match problem

    Duke,
    You are awesome! I haven't even heard of all those commands. Worked like a
    charm. Thank you so much. - Randy

    "Duke Carey" wrote:

    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly appreciated.
    > > Thanks, - Randy


  84. #84
    Bob Phillips
    Guest

    Re: Array index, match problem



    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Duke Carey" <[email protected]> wrote in message
    news:[email protected]...
    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must

    have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I

    need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly

    appreciated.
    > > Thanks, - Randy




  85. #85
    Bob Phillips
    Guest

    Re: Array index, match problem

    Just for interest, two less functions

    =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Duke Carey" <[email protected]> wrote in message
    news:[email protected]...
    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must

    have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I

    need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly

    appreciated.
    > > Thanks, - Randy




  86. #86
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Thanks a lot for the formula. I will start to disect it and try to learn as
    much as I can from it. I appreciate it.
    Randy

    "Bob Phillips" wrote:

    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Duke Carey" <[email protected]> wrote in message
    > news:[email protected]...
    > > How about
    > >
    > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > >
    > > which assumes the colors are in cells G1:G6
    > >
    > >
    > >
    > > "RAP" wrote:
    > >
    > > > Hello, Folks. This is my first post to this Discussion Group. I must

    > have
    > > > Dain Bramage to not be able to come up with an answer, but I can't. I

    > need
    > > > some help.
    > > > Below is an example of my problem.
    > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > I need a formula that will match value H26 from the following table and
    > > > return "Green" as the result.
    > > >
    > > >
    > > > H2 H3 H4 H5 H6 H7 Red
    > > > H9 H10 H11 H12 H13 H14 White
    > > > H16 H17 H18 H19 H20 H21 Blue
    > > > H23 H24 H25 H26 H27 H28 Green
    > > > H30 H31 H32 H33 H34 H35 Black
    > > > H37 H38 H39 H40 H41 H42 Purple
    > > >
    > > > Any help, pointers, suggestions or direction will be greatly

    > appreciated.
    > > > Thanks, - Randy

    >
    >
    >


  87. #87
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Thanks again for the formula. I have my app running now, thanks to you. I
    would like to ask you another question, but I think it belongs in the
    "Programming" board. How would I place your formula in VB script, instead of
    inserting it into a cell? Also, the data in the "J19 input cell" is being
    placed there (pasted) by a range variable, "X".

    Like I said, my app is functioning, but I want to achieve results using more
    programming and less cursoring around, like I mentioned before.

    Thanks,
    Randy


    "Bob Phillips" wrote:

    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Duke Carey" <[email protected]> wrote in message
    > news:[email protected]...
    > > How about
    > >
    > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > >
    > > which assumes the colors are in cells G1:G6
    > >
    > >
    > >
    > > "RAP" wrote:
    > >
    > > > Hello, Folks. This is my first post to this Discussion Group. I must

    > have
    > > > Dain Bramage to not be able to come up with an answer, but I can't. I

    > need
    > > > some help.
    > > > Below is an example of my problem.
    > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > I need a formula that will match value H26 from the following table and
    > > > return "Green" as the result.
    > > >
    > > >
    > > > H2 H3 H4 H5 H6 H7 Red
    > > > H9 H10 H11 H12 H13 H14 White
    > > > H16 H17 H18 H19 H20 H21 Blue
    > > > H23 H24 H25 H26 H27 H28 Green
    > > > H30 H31 H32 H33 H34 H35 Black
    > > > H37 H38 H39 H40 H41 H42 Purple
    > > >
    > > > Any help, pointers, suggestions or direction will be greatly

    > appreciated.
    > > > Thanks, - Randy

    >
    >
    >


  88. #88
    Bob Phillips
    Guest

    Re: Array index, match problem

    Randy,

    In VBA the code would look like

    Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"), 2,
    9) - 1) / 7 + 1)

    If you want to use the variable X without going via J19, use

    Dim x
    x = "H30"
    Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) / 7 +
    1)



    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "RAP" <[email protected]> wrote in message
    news:[email protected]...
    > Bob,
    > Thanks again for the formula. I have my app running now, thanks to you.

    I
    > would like to ask you another question, but I think it belongs in the
    > "Programming" board. How would I place your formula in VB script, instead

    of
    > inserting it into a cell? Also, the data in the "J19 input cell" is being
    > placed there (pasted) by a range variable, "X".
    >
    > Like I said, my app is functioning, but I want to achieve results using

    more
    > programming and less cursoring around, like I mentioned before.
    >
    > Thanks,
    > Randy
    >
    >
    > "Bob Phillips" wrote:
    >
    > > Just for interest, two less functions
    > >
    > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Duke Carey" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > How about
    > > >
    > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > >
    > > > which assumes the colors are in cells G1:G6
    > > >
    > > >
    > > >
    > > > "RAP" wrote:
    > > >
    > > > > Hello, Folks. This is my first post to this Discussion Group. I

    must
    > > have
    > > > > Dain Bramage to not be able to come up with an answer, but I can't.

    I
    > > need
    > > > > some help.
    > > > > Below is an example of my problem.
    > > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > > I need a formula that will match value H26 from the following table

    and
    > > > > return "Green" as the result.
    > > > >
    > > > >
    > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > H9 H10 H11 H12 H13 H14 White
    > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > >
    > > > > Any help, pointers, suggestions or direction will be greatly

    > > appreciated.
    > > > > Thanks, - Randy

    > >
    > >
    > >




  89. #89
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Once again, thanks. Works great. Now, how do I get the result of the
    formula (Red, White, etc...) pasted into yet another cell? I still have the
    mind-set that the result is in a cell somewhere. As soon as I can start
    thinking "programmatically", these simple questions will, well, at least they
    should diminish.

    Thanks again,
    Randy

    "Bob Phillips" wrote:

    > Randy,
    >
    > In VBA the code would look like
    >
    > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"), 2,
    > 9) - 1) / 7 + 1)
    >
    > If you want to use the variable X without going via J19, use
    >
    > Dim x
    > x = "H30"
    > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) / 7 +
    > 1)
    >
    >
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "RAP" <[email protected]> wrote in message
    > news:[email protected]...
    > > Bob,
    > > Thanks again for the formula. I have my app running now, thanks to you.

    > I
    > > would like to ask you another question, but I think it belongs in the
    > > "Programming" board. How would I place your formula in VB script, instead

    > of
    > > inserting it into a cell? Also, the data in the "J19 input cell" is being
    > > placed there (pasted) by a range variable, "X".
    > >
    > > Like I said, my app is functioning, but I want to achieve results using

    > more
    > > programming and less cursoring around, like I mentioned before.
    > >
    > > Thanks,
    > > Randy
    > >
    > >
    > > "Bob Phillips" wrote:
    > >
    > > > Just for interest, two less functions
    > > >
    > > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > > >
    > > > --
    > > >
    > > > HTH
    > > >
    > > > RP
    > > > (remove nothere from the email address if mailing direct)
    > > >
    > > >
    > > > "Duke Carey" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > How about
    > > > >
    > > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > > >
    > > > > which assumes the colors are in cells G1:G6
    > > > >
    > > > >
    > > > >
    > > > > "RAP" wrote:
    > > > >
    > > > > > Hello, Folks. This is my first post to this Discussion Group. I

    > must
    > > > have
    > > > > > Dain Bramage to not be able to come up with an answer, but I can't.

    > I
    > > > need
    > > > > > some help.
    > > > > > Below is an example of my problem.
    > > > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > > > I need a formula that will match value H26 from the following table

    > and
    > > > > > return "Green" as the result.
    > > > > >
    > > > > >
    > > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > > H9 H10 H11 H12 H13 H14 White
    > > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > > >
    > > > > > Any help, pointers, suggestions or direction will be greatly
    > > > appreciated.
    > > > > > Thanks, - Randy
    > > >
    > > >
    > > >

    >
    >
    >


  90. #90
    Bob Phillips
    Guest

    Re: Array index, match problem

    Hi again Randy,

    That would be a single assignment to a cell, say M2, and assuming variable X
    has that lookup value

    Range("M2").Value = Application.Index(Range("$G$1:$G$6"), (Mid(X, 2, 9) - 1)
    / 7 + 1)


    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "RAP" <[email protected]> wrote in message
    news:[email protected]...
    > Bob,
    > Once again, thanks. Works great. Now, how do I get the result of the
    > formula (Red, White, etc...) pasted into yet another cell? I still have

    the
    > mind-set that the result is in a cell somewhere. As soon as I can start
    > thinking "programmatically", these simple questions will, well, at least

    they
    > should diminish.
    >
    > Thanks again,
    > Randy
    >
    > "Bob Phillips" wrote:
    >
    > > Randy,
    > >
    > > In VBA the code would look like
    > >
    > > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"),

    2,
    > > 9) - 1) / 7 + 1)
    > >
    > > If you want to use the variable X without going via J19, use
    > >
    > > Dim x
    > > x = "H30"
    > > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) /

    7 +
    > > 1)
    > >
    > >
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "RAP" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Bob,
    > > > Thanks again for the formula. I have my app running now, thanks to

    you.
    > > I
    > > > would like to ask you another question, but I think it belongs in the
    > > > "Programming" board. How would I place your formula in VB script,

    instead
    > > of
    > > > inserting it into a cell? Also, the data in the "J19 input cell" is

    being
    > > > placed there (pasted) by a range variable, "X".
    > > >
    > > > Like I said, my app is functioning, but I want to achieve results

    using
    > > more
    > > > programming and less cursoring around, like I mentioned before.
    > > >
    > > > Thanks,
    > > > Randy
    > > >
    > > >
    > > > "Bob Phillips" wrote:
    > > >
    > > > > Just for interest, two less functions
    > > > >
    > > > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > > > >
    > > > > --
    > > > >
    > > > > HTH
    > > > >
    > > > > RP
    > > > > (remove nothere from the email address if mailing direct)
    > > > >
    > > > >
    > > > > "Duke Carey" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > > > How about
    > > > > >
    > > > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > > > >
    > > > > > which assumes the colors are in cells G1:G6
    > > > > >
    > > > > >
    > > > > >
    > > > > > "RAP" wrote:
    > > > > >
    > > > > > > Hello, Folks. This is my first post to this Discussion Group.

    I
    > > must
    > > > > have
    > > > > > > Dain Bramage to not be able to come up with an answer, but I

    can't.
    > > I
    > > > > need
    > > > > > > some help.
    > > > > > > Below is an example of my problem.
    > > > > > > Input cell = J19. Input Value = H26. Formula placed in cell

    J20.
    > > > > > > I need a formula that will match value H26 from the following

    table
    > > and
    > > > > > > return "Green" as the result.
    > > > > > >
    > > > > > >
    > > > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > > > H9 H10 H11 H12 H13 H14 White
    > > > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > > > >
    > > > > > > Any help, pointers, suggestions or direction will be greatly
    > > > > appreciated.
    > > > > > > Thanks, - Randy
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




  91. #91
    Alan Beban
    Guest

    Re: Array index, match problem

    Bob Phillips wrote:
    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >

    For a formula that is independent of the size of the table of data or
    the regularity of its contents, if the functions in the freely
    downloadable file at http://home.pacbell.net are available to your workbook

    =OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))

    Alan Beban

  92. #92
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    >Bob Phillips wrote:
    >>Just for interest, two less functions
    >>
    >>=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    >
    >For a formula that is independent of the size of the table of data or
    >the regularity of its contents, if the functions in the freely
    >downloadable file at http://home.pacbell.net are available to your workbook
    >
    >=OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,
    >-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))


    Assuming someone would use your function library, wouldn't they want to
    do so efficiently? Only one udf call needed (MakeArray).

    =INDEX(DataTable,INT((MATCH(J19,MakeArray(DataTable,1),0)-1)
    /COLUMNS(DataTable))+1,COLUMNS(DataTable))


  93. #93
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    >
    >>Bob Phillips wrote:
    >>
    >>>Just for interest, two less functions
    >>>
    >>>=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    >>
    >>For a formula that is independent of the size of the table of data or
    >>the regularity of its contents, if the functions in the freely
    >>downloadable file at http://home.pacbell.net are available to your workbook
    >>
    >>=OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,
    >>-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))

    >
    >
    > Assuming someone would use your function library, wouldn't they want to
    > do so efficiently? Only one udf call needed (MakeArray).
    >
    > =INDEX(DataTable,INT((MATCH(J19,MakeArray(DataTable,1),0)-1)
    > /COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >

    Well, isn't that interesting! I would have thought the suggestion would
    be something like fill down a range on Sheet8, for example, named CxRV with

    =INDIRECT(ADDRESS(
    ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))
    (compliments of Chip Pearson)

    and then enter something like

    =INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))

    in order to use only built-in functions and avoid at all costs the
    dreaded Array Functions.

    But no, here's Harlan Grove, instead carping about which of the dreaded
    Array Functions is more efficient. Onward and upward!

    Alan Beban

  94. #94
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >But no, here's Harlan Grove, instead carping about which of the dreaded
    >Array Functions is more efficient. Onward and upward!


    Just pointing out that you don't know how to use your own function
    library efficiently.

    Onward perhaps. Never upward.


  95. #95
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >=INDIRECT(ADDRESS(
    >ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),
    >COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))

    ....
    >=INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >
    >in order to use only built-in functions and avoid at all costs the
    >dreaded Array Functions.

    ....

    Ugh! Not the best way by a long shot! All it takes is a single array
    formula

    =INDEX(DataTable,MATCH(TRUE,COUNTIF(OFFSET(DataTable,
    ROW(DataTable)-CELL("Row",DataTable),0,1,),J1)>0,0),COLUMNS(DataTable))


  96. #96
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>=INDIRECT(ADDRESS(
    >>ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),
    >>COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))

    >
    > ...
    >
    >>=INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >>
    >>in order to use only built-in functions and avoid at all costs the
    >>dreaded Array Functions.

    >
    > ...
    >
    > Ugh! Not the best way by a long shot! All it takes is a single array
    > formula
    >
    > =INDEX(DataTable,MATCH(TRUE,COUNTIF(OFFSET(DataTable,
    > ROW(DataTable)-CELL("Row",DataTable),0,1,),J1)>0,0),COLUMNS(DataTable))
    >


    J1 should be J19 to conform to the original specification.

    Alan Beban

  97. #97
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>But no, here's Harlan Grove, instead carping about which of the dreaded
    >>Array Functions is more efficient. Onward and upward!

    >
    >
    > Just pointing out that you don't know how to use your own function
    > library efficiently.
    >


    Perhaps you could quantify for the users the difference in efficiency.

    Alan Beban

  98. #98
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >Perhaps you could quantify for the users the difference in efficiency.


    Nah, I'll leave that for you as an exercise since you're the one who
    needs to learn about efficiency. Here's a hint: one udf call will
    invariably be faster than two, even when there are a few extra built-in
    function calls with the single udf call.


  99. #99
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>Perhaps you could quantify for the users the difference in efficiency.

    >
    >
    > Nah, I'll leave that for you as an exercise since you're the one who
    > needs to learn about efficiency.


    Cute. But I suspect the real reason you don't want to deal with it is
    that the so-called "efficiency" to which you and many
    programmer/developers sometimes refer often involves nanoseconds of
    difference that are totally irrelevant to most users in most
    applications; interesting to you for purposes of posting oneupmanship,
    but somewhat misleading for users generally.

    Alan Beban

  100. #100
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ..=2E.
    >Cute. But I suspect the real reason you don't want to deal with it is
    >that the so-called "efficiency" to which you and many
    >programmer/developers sometimes refer often involves nanoseconds of
    >difference that are totally irrelevant to most users in most
    >applications; interesting to you for purposes of posting oneupmanship,
    >but somewhat misleading for users generally.


    Fine. Then consider whether the MakeArray formula,

    =3DINDEX(DataTable,INT((MATCH(J1=AD9,MakeArray(DataTable,1),0)-1)
    /COLUMNS(DataTable))+1,COLUMNS=AD(DataTable))

    a single MATCH against the data range transformed into a 1D array, with
    the result adjusted by a division inside INT to return the row number,
    against the ArrayMatch formula,

    =3DOFFSET(INDIRECT(ArrayMatch(J=AD19,dataTable,"A")),0,
    -INDEX(ArrayMatch(J19,dataTab=ADle),1,2)+COLUMNS(dataTable))

    first returning the cell address of the matching cell then using
    another call to fix the column offset. It's subjective whether the row
    index contortions of the MakeArray formula are more obscure than the
    column offset contortions of the 2 ArrayMatch formula.

    For that matter, you could also have used

    =3DINDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))

    which would have been a LOT simpler than either of the others.
    Simplicity is good.

    Both the MakeArray and the single ArrayMatch formulas involve no
    volatile function calls, so they won't cause Excel to prompt users to
    save any file containing them if users try to close such workbooks
    without making any changes. Your two ArrayMatch formula, due to OFFSET
    and INDIRECT calls, would cause such confusing prompts.

    Is that an acceptable user consideration?


  101. #101
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>Cute. But I suspect the real reason you don't want to deal with it is
    >>that the so-called "efficiency" to which you and many
    >>programmer/developers sometimes refer often involves nanoseconds of
    >>difference that are totally irrelevant to most users in most
    >>applications; interesting to you for purposes of posting oneupmanship,
    >>but somewhat misleading for users generally.

    >
    >
    > Fine. Then consider whether the MakeArray formula,
    >
    > =INDEX(DataTable,INT((MATCH(J1*9,MakeArray(DataTable,1),0)-1)
    > /COLUMNS(DataTable))+1,COLUMNS*(DataTable))
    >
    > a single MATCH against the data range transformed into a 1D array, with
    > the result adjusted by a division inside INT to return the row number,
    > against the ArrayMatch formula,
    >
    > =OFFSET(INDIRECT(ArrayMatch(J*19,dataTable,"A")),0,
    > -INDEX(ArrayMatch(J19,dataTab*le),1,2)+COLUMNS(dataTable))
    >
    > first returning the cell address of the matching cell then using
    > another call to fix the column offset. It's subjective whether the row
    > index contortions of the MakeArray formula are more obscure than the
    > column offset contortions of the 2 ArrayMatch formula.
    >
    > For that matter, you could also have used
    >
    > =INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))
    >
    > which would have been a LOT simpler than either of the others.
    > Simplicity is good.
    >
    > Both the MakeArray and the single ArrayMatch formulas involve no
    > volatile function calls, so they won't cause Excel to prompt users to
    > save any file containing them if users try to close such workbooks
    > without making any changes. Your two ArrayMatch formula, due to OFFSET
    > and INDIRECT calls, would cause such confusing prompts.
    >
    > Is that an acceptable user consideration?
    >

    It's certainly more constructive, particularly the suggestion of

    =INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))

    which is significantly faster than the one including the two ArrayMatch
    function calls that I originally posted. Would have been nice had you
    focused on the instructional value for the users in the first place,
    rather than just on stroking your ego. But then, there you go!

    By the way, the formula with the MakeArray function call seems to return
    an error if the data table exceeds 65536 elements (I haven't yet
    identified why; it might be fixable), while neither of the ArrayMatch
    formulas seems to--though they are slower.

    Alan Beban

  102. #102
    Alan Beban
    Guest

    Re: Array index, match problem

    Alan Beban wrote:
    > Harlan Grove wrote:
    > . . .Then consider whether the MakeArray formula,
    >>
    >> =INDEX(DataTable,INT((MATCH(J1*9,MakeArray(DataTable,1),0)-1)
    >> /COLUMNS(DataTable))+1,COLUMNS*(DataTable))
    >>
    >> a single MATCH against the data range transformed into a 1D array, with
    >> the result adjusted by a division inside INT to return the row number,
    >> against the ArrayMatch formula,
    >>
    >> =OFFSET(INDIRECT(ArrayMatch(J*19,dataTable,"A")),0,
    >> -INDEX(ArrayMatch(J19,dataTab*le),1,2)+COLUMNS(dataTable))
    >>
    >> first returning the cell address of the matching cell then using
    >> another call to fix the column offset. It's subjective whether the row
    >> index contortions of the MakeArray formula are more obscure than the
    >> column offset contortions of the 2 ArrayMatch formula.
    >>
    >>...By the way, the formula with the MakeArray function call seems to return

    > an error if the data table exceeds 65536 elements . . . .


    The problem is with the built-in INDEX function; it fails if the array
    or reference contains more than 65536 elements.

    Alan Beban

  103. #103
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >The problem is with the built-in INDEX function; it fails if the array
    >or reference contains more than 65536 elements.


    Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    chokes after 65,535 elements.

    Which would argue in favor of using the COUNTIF function provided in
    another branch of this thread. It's one drawback is the volatile OFFSET
    call. It'd always recalc, but it'd be lots faster than even a single
    udf call.


  104. #104
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >=INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))
    >
    >which is significantly faster than the one including the two ArrayMatch
    >function calls that I originally posted. Would have been nice had you
    >focused on the instructional value for the users in the first place,
    >rather than just on stroking your ego. But then, there you go!


    Of course you could have offerred it too, if you had thought of it. But
    then, there you go!

    >By the way, the formula with the MakeArray function call seems to return
    >an error if the data table exceeds 65536 elements (I haven't yet
    >identified why; it might be fixable), while neither of the ArrayMatch
    >formulas seems to--though they are slower.


    Excel can't handle any arrays with more than 65,535 entries in either
    of 1 or 2 dimensions, as you should know. It's questionable whether
    anyone should try to use brute force matching on so many cells. It'd be
    slow even without any udfs or volatile functions. There are tasks for
    which indexed database searches would be far more appropriate than
    unindexed spreadsheet searches. If the OP has so many entries to
    search, the OP is being foolish using a spreadsheet for the task.
    However, if the OP is only searching a few hundred entries or fewer,
    your caveat provides completeness of specification but is of no
    practical relevance.


  105. #105
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>The problem is with the built-in INDEX function; it fails if the array
    >>or reference contains more than 65536 elements.

    >
    >
    > Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    > chokes after 65,535 elements. . . .


    Dim arr, i
    ReDim arr(1 To 65536)
    For i = 1 To 65536
    arr(i) = i * 2
    Next
    MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.

    In xl2000 and earlier it fails on 5462 elements or greater.

    Alan Beban

  106. #106
    Alan Beban
    Guest

    Re: Array index, match problem

    Alan Beban wrote:
    > Harlan Grove wrote:
    >
    >> Alan Beban wrote...
    >> ...
    >>
    >>> The problem is with the built-in INDEX function; it fails if the array
    >>> or reference contains more than 65536 elements.

    >>
    >>
    >>
    >> Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >> chokes after 65,535 elements. . . .

    >
    >
    > Dim arr, i
    > ReDim arr(1 To 65536)
    > For i = 1 To 65536
    > arr(i) = i * 2
    > Next
    > MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >
    > In xl2000 and earlier it fails on 5462 elements or greater.
    >
    > Alan Beban


    Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    xl2000 or xl2002).

    Alan Beban

  107. #107
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    >>>Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >>>chokes after 65,535 elements. . . .

    >>
    >> Dim arr, i
    >> ReDim arr(1 To 65536)
    >> For i = 1 To 65536
    >> arr(i) = i * 2
    >> Next
    >> MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >>
    >> In xl2000 and earlier it fails on 5462 elements or greater.

    >
    >Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    >xl2000 or xl2002).


    Not comparable. A:B is a range, not an array. But you're correct that
    INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and
    subsequent arguments and converts them to long integers.


  108. #108
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    >
    >>>>Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >>>>chokes after 65,535 elements. . . .
    >>>
    >>> Dim arr, i
    >>> ReDim arr(1 To 65536)
    >>> For i = 1 To 65536
    >>> arr(i) = i * 2
    >>> Next
    >>> MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >>>
    >>>In xl2000 and earlier it fails on 5462 elements or greater.

    >>
    >>Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    >>xl2000 or xl2002).

    >
    >
    > Not comparable. A:B is a range, not an array. But you're correct that
    > INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and
    > subsequent arguments and converts them to long integers.
    >


    It seems that the limitation on the VBA invocation of the INDEX function
    is a bit subtler; it is not limited by the number of the elements in the
    array (see arr1 below) but apparently by the number of elements in a
    dimension.

    Sub testIt3a()
    Dim arr1, arr2, arr3
    Dim i As Long, j As Long
    Dim x, y, z

    '65536 rows, 2 columns
    ReDim arr1(1 To 65536, 1 To 2)
    For i = 1 To 65536: For j = 1 To 2
    arr1(i, j) = i * 2 + j
    Next: Next

    '1 row, 65536 columns
    ReDim arr2(1 To 65536)
    For i = 1 To 65536
    arr2(i) = i
    Next

    '1 row, 65537 columns
    ReDim arr3(1 To 65537)
    For i = 1 To 65537
    arr3(i) = i
    Next

    x = Application.Index(arr1, 65536, 2)
    Debug.Print x '<---returns 131074
    y = Application.Index(arr2, 65536)
    Debug.Print y '<---returns 65536
    z = Application.Index(arr3, 65536) 'Type mismatch error
    End Sub

    Alan Beban

  109. #109
    Duke Carey
    Guest

    RE: Array index, match problem

    How about

    =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))

    which assumes the colors are in cells G1:G6



    "RAP" wrote:

    > Hello, Folks. This is my first post to this Discussion Group. I must have
    > Dain Bramage to not be able to come up with an answer, but I can't. I need
    > some help.
    > Below is an example of my problem.
    > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > I need a formula that will match value H26 from the following table and
    > return "Green" as the result.
    >
    >
    > H2 H3 H4 H5 H6 H7 Red
    > H9 H10 H11 H12 H13 H14 White
    > H16 H17 H18 H19 H20 H21 Blue
    > H23 H24 H25 H26 H27 H28 Green
    > H30 H31 H32 H33 H34 H35 Black
    > H37 H38 H39 H40 H41 H42 Purple
    >
    > Any help, pointers, suggestions or direction will be greatly appreciated.
    > Thanks, - Randy


  110. #110
    RAP
    Guest

    RE: Array index, match problem

    Duke,
    You are awesome! I haven't even heard of all those commands. Worked like a
    charm. Thank you so much. - Randy

    "Duke Carey" wrote:

    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly appreciated.
    > > Thanks, - Randy


  111. #111
    Bob Phillips
    Guest

    Re: Array index, match problem



    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Duke Carey" <[email protected]> wrote in message
    news:[email protected]...
    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must

    have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I

    need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly

    appreciated.
    > > Thanks, - Randy




  112. #112
    Bob Phillips
    Guest

    Re: Array index, match problem

    Just for interest, two less functions

    =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Duke Carey" <[email protected]> wrote in message
    news:[email protected]...
    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must

    have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I

    need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly

    appreciated.
    > > Thanks, - Randy




  113. #113
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Thanks a lot for the formula. I will start to disect it and try to learn as
    much as I can from it. I appreciate it.
    Randy

    "Bob Phillips" wrote:

    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Duke Carey" <[email protected]> wrote in message
    > news:[email protected]...
    > > How about
    > >
    > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > >
    > > which assumes the colors are in cells G1:G6
    > >
    > >
    > >
    > > "RAP" wrote:
    > >
    > > > Hello, Folks. This is my first post to this Discussion Group. I must

    > have
    > > > Dain Bramage to not be able to come up with an answer, but I can't. I

    > need
    > > > some help.
    > > > Below is an example of my problem.
    > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > I need a formula that will match value H26 from the following table and
    > > > return "Green" as the result.
    > > >
    > > >
    > > > H2 H3 H4 H5 H6 H7 Red
    > > > H9 H10 H11 H12 H13 H14 White
    > > > H16 H17 H18 H19 H20 H21 Blue
    > > > H23 H24 H25 H26 H27 H28 Green
    > > > H30 H31 H32 H33 H34 H35 Black
    > > > H37 H38 H39 H40 H41 H42 Purple
    > > >
    > > > Any help, pointers, suggestions or direction will be greatly

    > appreciated.
    > > > Thanks, - Randy

    >
    >
    >


  114. #114
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Thanks again for the formula. I have my app running now, thanks to you. I
    would like to ask you another question, but I think it belongs in the
    "Programming" board. How would I place your formula in VB script, instead of
    inserting it into a cell? Also, the data in the "J19 input cell" is being
    placed there (pasted) by a range variable, "X".

    Like I said, my app is functioning, but I want to achieve results using more
    programming and less cursoring around, like I mentioned before.

    Thanks,
    Randy


    "Bob Phillips" wrote:

    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Duke Carey" <[email protected]> wrote in message
    > news:[email protected]...
    > > How about
    > >
    > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > >
    > > which assumes the colors are in cells G1:G6
    > >
    > >
    > >
    > > "RAP" wrote:
    > >
    > > > Hello, Folks. This is my first post to this Discussion Group. I must

    > have
    > > > Dain Bramage to not be able to come up with an answer, but I can't. I

    > need
    > > > some help.
    > > > Below is an example of my problem.
    > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > I need a formula that will match value H26 from the following table and
    > > > return "Green" as the result.
    > > >
    > > >
    > > > H2 H3 H4 H5 H6 H7 Red
    > > > H9 H10 H11 H12 H13 H14 White
    > > > H16 H17 H18 H19 H20 H21 Blue
    > > > H23 H24 H25 H26 H27 H28 Green
    > > > H30 H31 H32 H33 H34 H35 Black
    > > > H37 H38 H39 H40 H41 H42 Purple
    > > >
    > > > Any help, pointers, suggestions or direction will be greatly

    > appreciated.
    > > > Thanks, - Randy

    >
    >
    >


  115. #115
    Bob Phillips
    Guest

    Re: Array index, match problem

    Randy,

    In VBA the code would look like

    Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"), 2,
    9) - 1) / 7 + 1)

    If you want to use the variable X without going via J19, use

    Dim x
    x = "H30"
    Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) / 7 +
    1)



    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "RAP" <[email protected]> wrote in message
    news:[email protected]...
    > Bob,
    > Thanks again for the formula. I have my app running now, thanks to you.

    I
    > would like to ask you another question, but I think it belongs in the
    > "Programming" board. How would I place your formula in VB script, instead

    of
    > inserting it into a cell? Also, the data in the "J19 input cell" is being
    > placed there (pasted) by a range variable, "X".
    >
    > Like I said, my app is functioning, but I want to achieve results using

    more
    > programming and less cursoring around, like I mentioned before.
    >
    > Thanks,
    > Randy
    >
    >
    > "Bob Phillips" wrote:
    >
    > > Just for interest, two less functions
    > >
    > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Duke Carey" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > How about
    > > >
    > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > >
    > > > which assumes the colors are in cells G1:G6
    > > >
    > > >
    > > >
    > > > "RAP" wrote:
    > > >
    > > > > Hello, Folks. This is my first post to this Discussion Group. I

    must
    > > have
    > > > > Dain Bramage to not be able to come up with an answer, but I can't.

    I
    > > need
    > > > > some help.
    > > > > Below is an example of my problem.
    > > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > > I need a formula that will match value H26 from the following table

    and
    > > > > return "Green" as the result.
    > > > >
    > > > >
    > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > H9 H10 H11 H12 H13 H14 White
    > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > >
    > > > > Any help, pointers, suggestions or direction will be greatly

    > > appreciated.
    > > > > Thanks, - Randy

    > >
    > >
    > >




  116. #116
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Once again, thanks. Works great. Now, how do I get the result of the
    formula (Red, White, etc...) pasted into yet another cell? I still have the
    mind-set that the result is in a cell somewhere. As soon as I can start
    thinking "programmatically", these simple questions will, well, at least they
    should diminish.

    Thanks again,
    Randy

    "Bob Phillips" wrote:

    > Randy,
    >
    > In VBA the code would look like
    >
    > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"), 2,
    > 9) - 1) / 7 + 1)
    >
    > If you want to use the variable X without going via J19, use
    >
    > Dim x
    > x = "H30"
    > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) / 7 +
    > 1)
    >
    >
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "RAP" <[email protected]> wrote in message
    > news:[email protected]...
    > > Bob,
    > > Thanks again for the formula. I have my app running now, thanks to you.

    > I
    > > would like to ask you another question, but I think it belongs in the
    > > "Programming" board. How would I place your formula in VB script, instead

    > of
    > > inserting it into a cell? Also, the data in the "J19 input cell" is being
    > > placed there (pasted) by a range variable, "X".
    > >
    > > Like I said, my app is functioning, but I want to achieve results using

    > more
    > > programming and less cursoring around, like I mentioned before.
    > >
    > > Thanks,
    > > Randy
    > >
    > >
    > > "Bob Phillips" wrote:
    > >
    > > > Just for interest, two less functions
    > > >
    > > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > > >
    > > > --
    > > >
    > > > HTH
    > > >
    > > > RP
    > > > (remove nothere from the email address if mailing direct)
    > > >
    > > >
    > > > "Duke Carey" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > How about
    > > > >
    > > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > > >
    > > > > which assumes the colors are in cells G1:G6
    > > > >
    > > > >
    > > > >
    > > > > "RAP" wrote:
    > > > >
    > > > > > Hello, Folks. This is my first post to this Discussion Group. I

    > must
    > > > have
    > > > > > Dain Bramage to not be able to come up with an answer, but I can't.

    > I
    > > > need
    > > > > > some help.
    > > > > > Below is an example of my problem.
    > > > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > > > I need a formula that will match value H26 from the following table

    > and
    > > > > > return "Green" as the result.
    > > > > >
    > > > > >
    > > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > > H9 H10 H11 H12 H13 H14 White
    > > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > > >
    > > > > > Any help, pointers, suggestions or direction will be greatly
    > > > appreciated.
    > > > > > Thanks, - Randy
    > > >
    > > >
    > > >

    >
    >
    >


  117. #117
    Bob Phillips
    Guest

    Re: Array index, match problem

    Hi again Randy,

    That would be a single assignment to a cell, say M2, and assuming variable X
    has that lookup value

    Range("M2").Value = Application.Index(Range("$G$1:$G$6"), (Mid(X, 2, 9) - 1)
    / 7 + 1)


    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "RAP" <[email protected]> wrote in message
    news:[email protected]...
    > Bob,
    > Once again, thanks. Works great. Now, how do I get the result of the
    > formula (Red, White, etc...) pasted into yet another cell? I still have

    the
    > mind-set that the result is in a cell somewhere. As soon as I can start
    > thinking "programmatically", these simple questions will, well, at least

    they
    > should diminish.
    >
    > Thanks again,
    > Randy
    >
    > "Bob Phillips" wrote:
    >
    > > Randy,
    > >
    > > In VBA the code would look like
    > >
    > > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"),

    2,
    > > 9) - 1) / 7 + 1)
    > >
    > > If you want to use the variable X without going via J19, use
    > >
    > > Dim x
    > > x = "H30"
    > > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) /

    7 +
    > > 1)
    > >
    > >
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "RAP" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Bob,
    > > > Thanks again for the formula. I have my app running now, thanks to

    you.
    > > I
    > > > would like to ask you another question, but I think it belongs in the
    > > > "Programming" board. How would I place your formula in VB script,

    instead
    > > of
    > > > inserting it into a cell? Also, the data in the "J19 input cell" is

    being
    > > > placed there (pasted) by a range variable, "X".
    > > >
    > > > Like I said, my app is functioning, but I want to achieve results

    using
    > > more
    > > > programming and less cursoring around, like I mentioned before.
    > > >
    > > > Thanks,
    > > > Randy
    > > >
    > > >
    > > > "Bob Phillips" wrote:
    > > >
    > > > > Just for interest, two less functions
    > > > >
    > > > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > > > >
    > > > > --
    > > > >
    > > > > HTH
    > > > >
    > > > > RP
    > > > > (remove nothere from the email address if mailing direct)
    > > > >
    > > > >
    > > > > "Duke Carey" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > > > How about
    > > > > >
    > > > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > > > >
    > > > > > which assumes the colors are in cells G1:G6
    > > > > >
    > > > > >
    > > > > >
    > > > > > "RAP" wrote:
    > > > > >
    > > > > > > Hello, Folks. This is my first post to this Discussion Group.

    I
    > > must
    > > > > have
    > > > > > > Dain Bramage to not be able to come up with an answer, but I

    can't.
    > > I
    > > > > need
    > > > > > > some help.
    > > > > > > Below is an example of my problem.
    > > > > > > Input cell = J19. Input Value = H26. Formula placed in cell

    J20.
    > > > > > > I need a formula that will match value H26 from the following

    table
    > > and
    > > > > > > return "Green" as the result.
    > > > > > >
    > > > > > >
    > > > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > > > H9 H10 H11 H12 H13 H14 White
    > > > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > > > >
    > > > > > > Any help, pointers, suggestions or direction will be greatly
    > > > > appreciated.
    > > > > > > Thanks, - Randy
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




  118. #118
    Alan Beban
    Guest

    Re: Array index, match problem

    Bob Phillips wrote:
    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >

    For a formula that is independent of the size of the table of data or
    the regularity of its contents, if the functions in the freely
    downloadable file at http://home.pacbell.net are available to your workbook

    =OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))

    Alan Beban

  119. #119
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    >Bob Phillips wrote:
    >>Just for interest, two less functions
    >>
    >>=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    >
    >For a formula that is independent of the size of the table of data or
    >the regularity of its contents, if the functions in the freely
    >downloadable file at http://home.pacbell.net are available to your workbook
    >
    >=OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,
    >-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))


    Assuming someone would use your function library, wouldn't they want to
    do so efficiently? Only one udf call needed (MakeArray).

    =INDEX(DataTable,INT((MATCH(J19,MakeArray(DataTable,1),0)-1)
    /COLUMNS(DataTable))+1,COLUMNS(DataTable))


  120. #120
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    >
    >>Bob Phillips wrote:
    >>
    >>>Just for interest, two less functions
    >>>
    >>>=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    >>
    >>For a formula that is independent of the size of the table of data or
    >>the regularity of its contents, if the functions in the freely
    >>downloadable file at http://home.pacbell.net are available to your workbook
    >>
    >>=OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,
    >>-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))

    >
    >
    > Assuming someone would use your function library, wouldn't they want to
    > do so efficiently? Only one udf call needed (MakeArray).
    >
    > =INDEX(DataTable,INT((MATCH(J19,MakeArray(DataTable,1),0)-1)
    > /COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >

    Well, isn't that interesting! I would have thought the suggestion would
    be something like fill down a range on Sheet8, for example, named CxRV with

    =INDIRECT(ADDRESS(
    ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))
    (compliments of Chip Pearson)

    and then enter something like

    =INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))

    in order to use only built-in functions and avoid at all costs the
    dreaded Array Functions.

    But no, here's Harlan Grove, instead carping about which of the dreaded
    Array Functions is more efficient. Onward and upward!

    Alan Beban

  121. #121
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >But no, here's Harlan Grove, instead carping about which of the dreaded
    >Array Functions is more efficient. Onward and upward!


    Just pointing out that you don't know how to use your own function
    library efficiently.

    Onward perhaps. Never upward.


  122. #122
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >=INDIRECT(ADDRESS(
    >ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),
    >COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))

    ....
    >=INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >
    >in order to use only built-in functions and avoid at all costs the
    >dreaded Array Functions.

    ....

    Ugh! Not the best way by a long shot! All it takes is a single array
    formula

    =INDEX(DataTable,MATCH(TRUE,COUNTIF(OFFSET(DataTable,
    ROW(DataTable)-CELL("Row",DataTable),0,1,),J1)>0,0),COLUMNS(DataTable))


  123. #123
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>=INDIRECT(ADDRESS(
    >>ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),
    >>COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))

    >
    > ...
    >
    >>=INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >>
    >>in order to use only built-in functions and avoid at all costs the
    >>dreaded Array Functions.

    >
    > ...
    >
    > Ugh! Not the best way by a long shot! All it takes is a single array
    > formula
    >
    > =INDEX(DataTable,MATCH(TRUE,COUNTIF(OFFSET(DataTable,
    > ROW(DataTable)-CELL("Row",DataTable),0,1,),J1)>0,0),COLUMNS(DataTable))
    >


    J1 should be J19 to conform to the original specification.

    Alan Beban

  124. #124
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>But no, here's Harlan Grove, instead carping about which of the dreaded
    >>Array Functions is more efficient. Onward and upward!

    >
    >
    > Just pointing out that you don't know how to use your own function
    > library efficiently.
    >


    Perhaps you could quantify for the users the difference in efficiency.

    Alan Beban

  125. #125
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >Perhaps you could quantify for the users the difference in efficiency.


    Nah, I'll leave that for you as an exercise since you're the one who
    needs to learn about efficiency. Here's a hint: one udf call will
    invariably be faster than two, even when there are a few extra built-in
    function calls with the single udf call.


  126. #126
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>Perhaps you could quantify for the users the difference in efficiency.

    >
    >
    > Nah, I'll leave that for you as an exercise since you're the one who
    > needs to learn about efficiency.


    Cute. But I suspect the real reason you don't want to deal with it is
    that the so-called "efficiency" to which you and many
    programmer/developers sometimes refer often involves nanoseconds of
    difference that are totally irrelevant to most users in most
    applications; interesting to you for purposes of posting oneupmanship,
    but somewhat misleading for users generally.

    Alan Beban

  127. #127
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ..=2E.
    >Cute. But I suspect the real reason you don't want to deal with it is
    >that the so-called "efficiency" to which you and many
    >programmer/developers sometimes refer often involves nanoseconds of
    >difference that are totally irrelevant to most users in most
    >applications; interesting to you for purposes of posting oneupmanship,
    >but somewhat misleading for users generally.


    Fine. Then consider whether the MakeArray formula,

    =3DINDEX(DataTable,INT((MATCH(J1=AD9,MakeArray(DataTable,1),0)-1)
    /COLUMNS(DataTable))+1,COLUMNS=AD(DataTable))

    a single MATCH against the data range transformed into a 1D array, with
    the result adjusted by a division inside INT to return the row number,
    against the ArrayMatch formula,

    =3DOFFSET(INDIRECT(ArrayMatch(J=AD19,dataTable,"A")),0,
    -INDEX(ArrayMatch(J19,dataTab=ADle),1,2)+COLUMNS(dataTable))

    first returning the cell address of the matching cell then using
    another call to fix the column offset. It's subjective whether the row
    index contortions of the MakeArray formula are more obscure than the
    column offset contortions of the 2 ArrayMatch formula.

    For that matter, you could also have used

    =3DINDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))

    which would have been a LOT simpler than either of the others.
    Simplicity is good.

    Both the MakeArray and the single ArrayMatch formulas involve no
    volatile function calls, so they won't cause Excel to prompt users to
    save any file containing them if users try to close such workbooks
    without making any changes. Your two ArrayMatch formula, due to OFFSET
    and INDIRECT calls, would cause such confusing prompts.

    Is that an acceptable user consideration?


  128. #128
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>Cute. But I suspect the real reason you don't want to deal with it is
    >>that the so-called "efficiency" to which you and many
    >>programmer/developers sometimes refer often involves nanoseconds of
    >>difference that are totally irrelevant to most users in most
    >>applications; interesting to you for purposes of posting oneupmanship,
    >>but somewhat misleading for users generally.

    >
    >
    > Fine. Then consider whether the MakeArray formula,
    >
    > =INDEX(DataTable,INT((MATCH(J1*9,MakeArray(DataTable,1),0)-1)
    > /COLUMNS(DataTable))+1,COLUMNS*(DataTable))
    >
    > a single MATCH against the data range transformed into a 1D array, with
    > the result adjusted by a division inside INT to return the row number,
    > against the ArrayMatch formula,
    >
    > =OFFSET(INDIRECT(ArrayMatch(J*19,dataTable,"A")),0,
    > -INDEX(ArrayMatch(J19,dataTab*le),1,2)+COLUMNS(dataTable))
    >
    > first returning the cell address of the matching cell then using
    > another call to fix the column offset. It's subjective whether the row
    > index contortions of the MakeArray formula are more obscure than the
    > column offset contortions of the 2 ArrayMatch formula.
    >
    > For that matter, you could also have used
    >
    > =INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))
    >
    > which would have been a LOT simpler than either of the others.
    > Simplicity is good.
    >
    > Both the MakeArray and the single ArrayMatch formulas involve no
    > volatile function calls, so they won't cause Excel to prompt users to
    > save any file containing them if users try to close such workbooks
    > without making any changes. Your two ArrayMatch formula, due to OFFSET
    > and INDIRECT calls, would cause such confusing prompts.
    >
    > Is that an acceptable user consideration?
    >

    It's certainly more constructive, particularly the suggestion of

    =INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))

    which is significantly faster than the one including the two ArrayMatch
    function calls that I originally posted. Would have been nice had you
    focused on the instructional value for the users in the first place,
    rather than just on stroking your ego. But then, there you go!

    By the way, the formula with the MakeArray function call seems to return
    an error if the data table exceeds 65536 elements (I haven't yet
    identified why; it might be fixable), while neither of the ArrayMatch
    formulas seems to--though they are slower.

    Alan Beban

  129. #129
    Alan Beban
    Guest

    Re: Array index, match problem

    Alan Beban wrote:
    > Harlan Grove wrote:
    > . . .Then consider whether the MakeArray formula,
    >>
    >> =INDEX(DataTable,INT((MATCH(J1*9,MakeArray(DataTable,1),0)-1)
    >> /COLUMNS(DataTable))+1,COLUMNS*(DataTable))
    >>
    >> a single MATCH against the data range transformed into a 1D array, with
    >> the result adjusted by a division inside INT to return the row number,
    >> against the ArrayMatch formula,
    >>
    >> =OFFSET(INDIRECT(ArrayMatch(J*19,dataTable,"A")),0,
    >> -INDEX(ArrayMatch(J19,dataTab*le),1,2)+COLUMNS(dataTable))
    >>
    >> first returning the cell address of the matching cell then using
    >> another call to fix the column offset. It's subjective whether the row
    >> index contortions of the MakeArray formula are more obscure than the
    >> column offset contortions of the 2 ArrayMatch formula.
    >>
    >>...By the way, the formula with the MakeArray function call seems to return

    > an error if the data table exceeds 65536 elements . . . .


    The problem is with the built-in INDEX function; it fails if the array
    or reference contains more than 65536 elements.

    Alan Beban

  130. #130
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >The problem is with the built-in INDEX function; it fails if the array
    >or reference contains more than 65536 elements.


    Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    chokes after 65,535 elements.

    Which would argue in favor of using the COUNTIF function provided in
    another branch of this thread. It's one drawback is the volatile OFFSET
    call. It'd always recalc, but it'd be lots faster than even a single
    udf call.


  131. #131
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >=INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))
    >
    >which is significantly faster than the one including the two ArrayMatch
    >function calls that I originally posted. Would have been nice had you
    >focused on the instructional value for the users in the first place,
    >rather than just on stroking your ego. But then, there you go!


    Of course you could have offerred it too, if you had thought of it. But
    then, there you go!

    >By the way, the formula with the MakeArray function call seems to return
    >an error if the data table exceeds 65536 elements (I haven't yet
    >identified why; it might be fixable), while neither of the ArrayMatch
    >formulas seems to--though they are slower.


    Excel can't handle any arrays with more than 65,535 entries in either
    of 1 or 2 dimensions, as you should know. It's questionable whether
    anyone should try to use brute force matching on so many cells. It'd be
    slow even without any udfs or volatile functions. There are tasks for
    which indexed database searches would be far more appropriate than
    unindexed spreadsheet searches. If the OP has so many entries to
    search, the OP is being foolish using a spreadsheet for the task.
    However, if the OP is only searching a few hundred entries or fewer,
    your caveat provides completeness of specification but is of no
    practical relevance.


  132. #132
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>The problem is with the built-in INDEX function; it fails if the array
    >>or reference contains more than 65536 elements.

    >
    >
    > Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    > chokes after 65,535 elements. . . .


    Dim arr, i
    ReDim arr(1 To 65536)
    For i = 1 To 65536
    arr(i) = i * 2
    Next
    MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.

    In xl2000 and earlier it fails on 5462 elements or greater.

    Alan Beban

  133. #133
    Alan Beban
    Guest

    Re: Array index, match problem

    Alan Beban wrote:
    > Harlan Grove wrote:
    >
    >> Alan Beban wrote...
    >> ...
    >>
    >>> The problem is with the built-in INDEX function; it fails if the array
    >>> or reference contains more than 65536 elements.

    >>
    >>
    >>
    >> Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >> chokes after 65,535 elements. . . .

    >
    >
    > Dim arr, i
    > ReDim arr(1 To 65536)
    > For i = 1 To 65536
    > arr(i) = i * 2
    > Next
    > MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >
    > In xl2000 and earlier it fails on 5462 elements or greater.
    >
    > Alan Beban


    Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    xl2000 or xl2002).

    Alan Beban

  134. #134
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    >>>Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >>>chokes after 65,535 elements. . . .

    >>
    >> Dim arr, i
    >> ReDim arr(1 To 65536)
    >> For i = 1 To 65536
    >> arr(i) = i * 2
    >> Next
    >> MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >>
    >> In xl2000 and earlier it fails on 5462 elements or greater.

    >
    >Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    >xl2000 or xl2002).


    Not comparable. A:B is a range, not an array. But you're correct that
    INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and
    subsequent arguments and converts them to long integers.


  135. #135
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    >
    >>>>Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >>>>chokes after 65,535 elements. . . .
    >>>
    >>> Dim arr, i
    >>> ReDim arr(1 To 65536)
    >>> For i = 1 To 65536
    >>> arr(i) = i * 2
    >>> Next
    >>> MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >>>
    >>>In xl2000 and earlier it fails on 5462 elements or greater.

    >>
    >>Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    >>xl2000 or xl2002).

    >
    >
    > Not comparable. A:B is a range, not an array. But you're correct that
    > INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and
    > subsequent arguments and converts them to long integers.
    >


    It seems that the limitation on the VBA invocation of the INDEX function
    is a bit subtler; it is not limited by the number of the elements in the
    array (see arr1 below) but apparently by the number of elements in a
    dimension.

    Sub testIt3a()
    Dim arr1, arr2, arr3
    Dim i As Long, j As Long
    Dim x, y, z

    '65536 rows, 2 columns
    ReDim arr1(1 To 65536, 1 To 2)
    For i = 1 To 65536: For j = 1 To 2
    arr1(i, j) = i * 2 + j
    Next: Next

    '1 row, 65536 columns
    ReDim arr2(1 To 65536)
    For i = 1 To 65536
    arr2(i) = i
    Next

    '1 row, 65537 columns
    ReDim arr3(1 To 65537)
    For i = 1 To 65537
    arr3(i) = i
    Next

    x = Application.Index(arr1, 65536, 2)
    Debug.Print x '<---returns 131074
    y = Application.Index(arr2, 65536)
    Debug.Print y '<---returns 65536
    z = Application.Index(arr3, 65536) 'Type mismatch error
    End Sub

    Alan Beban

  136. #136
    RAP
    Guest

    Array index, match problem

    Hello, Folks. This is my first post to this Discussion Group. I must have
    Dain Bramage to not be able to come up with an answer, but I can't. I need
    some help.
    Below is an example of my problem.
    Input cell = J19. Input Value = H26. Formula placed in cell J20.
    I need a formula that will match value H26 from the following table and
    return "Green" as the result.


    H2 H3 H4 H5 H6 H7 Red
    H9 H10 H11 H12 H13 H14 White
    H16 H17 H18 H19 H20 H21 Blue
    H23 H24 H25 H26 H27 H28 Green
    H30 H31 H32 H33 H34 H35 Black
    H37 H38 H39 H40 H41 H42 Purple

    Any help, pointers, suggestions or direction will be greatly appreciated.
    Thanks, - Randy

  137. #137
    Duke Carey
    Guest

    RE: Array index, match problem

    How about

    =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))

    which assumes the colors are in cells G1:G6



    "RAP" wrote:

    > Hello, Folks. This is my first post to this Discussion Group. I must have
    > Dain Bramage to not be able to come up with an answer, but I can't. I need
    > some help.
    > Below is an example of my problem.
    > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > I need a formula that will match value H26 from the following table and
    > return "Green" as the result.
    >
    >
    > H2 H3 H4 H5 H6 H7 Red
    > H9 H10 H11 H12 H13 H14 White
    > H16 H17 H18 H19 H20 H21 Blue
    > H23 H24 H25 H26 H27 H28 Green
    > H30 H31 H32 H33 H34 H35 Black
    > H37 H38 H39 H40 H41 H42 Purple
    >
    > Any help, pointers, suggestions or direction will be greatly appreciated.
    > Thanks, - Randy


  138. #138
    RAP
    Guest

    RE: Array index, match problem

    Duke,
    You are awesome! I haven't even heard of all those commands. Worked like a
    charm. Thank you so much. - Randy

    "Duke Carey" wrote:

    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly appreciated.
    > > Thanks, - Randy


  139. #139
    Bob Phillips
    Guest

    Re: Array index, match problem



    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Duke Carey" <[email protected]> wrote in message
    news:[email protected]...
    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must

    have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I

    need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly

    appreciated.
    > > Thanks, - Randy




  140. #140
    Bob Phillips
    Guest

    Re: Array index, match problem

    Just for interest, two less functions

    =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Duke Carey" <[email protected]> wrote in message
    news:[email protected]...
    > How about
    >
    > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    >
    > which assumes the colors are in cells G1:G6
    >
    >
    >
    > "RAP" wrote:
    >
    > > Hello, Folks. This is my first post to this Discussion Group. I must

    have
    > > Dain Bramage to not be able to come up with an answer, but I can't. I

    need
    > > some help.
    > > Below is an example of my problem.
    > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > I need a formula that will match value H26 from the following table and
    > > return "Green" as the result.
    > >
    > >
    > > H2 H3 H4 H5 H6 H7 Red
    > > H9 H10 H11 H12 H13 H14 White
    > > H16 H17 H18 H19 H20 H21 Blue
    > > H23 H24 H25 H26 H27 H28 Green
    > > H30 H31 H32 H33 H34 H35 Black
    > > H37 H38 H39 H40 H41 H42 Purple
    > >
    > > Any help, pointers, suggestions or direction will be greatly

    appreciated.
    > > Thanks, - Randy




  141. #141
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Thanks a lot for the formula. I will start to disect it and try to learn as
    much as I can from it. I appreciate it.
    Randy

    "Bob Phillips" wrote:

    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Duke Carey" <[email protected]> wrote in message
    > news:[email protected]...
    > > How about
    > >
    > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > >
    > > which assumes the colors are in cells G1:G6
    > >
    > >
    > >
    > > "RAP" wrote:
    > >
    > > > Hello, Folks. This is my first post to this Discussion Group. I must

    > have
    > > > Dain Bramage to not be able to come up with an answer, but I can't. I

    > need
    > > > some help.
    > > > Below is an example of my problem.
    > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > I need a formula that will match value H26 from the following table and
    > > > return "Green" as the result.
    > > >
    > > >
    > > > H2 H3 H4 H5 H6 H7 Red
    > > > H9 H10 H11 H12 H13 H14 White
    > > > H16 H17 H18 H19 H20 H21 Blue
    > > > H23 H24 H25 H26 H27 H28 Green
    > > > H30 H31 H32 H33 H34 H35 Black
    > > > H37 H38 H39 H40 H41 H42 Purple
    > > >
    > > > Any help, pointers, suggestions or direction will be greatly

    > appreciated.
    > > > Thanks, - Randy

    >
    >
    >


  142. #142
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Thanks again for the formula. I have my app running now, thanks to you. I
    would like to ask you another question, but I think it belongs in the
    "Programming" board. How would I place your formula in VB script, instead of
    inserting it into a cell? Also, the data in the "J19 input cell" is being
    placed there (pasted) by a range variable, "X".

    Like I said, my app is functioning, but I want to achieve results using more
    programming and less cursoring around, like I mentioned before.

    Thanks,
    Randy


    "Bob Phillips" wrote:

    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Duke Carey" <[email protected]> wrote in message
    > news:[email protected]...
    > > How about
    > >
    > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > >
    > > which assumes the colors are in cells G1:G6
    > >
    > >
    > >
    > > "RAP" wrote:
    > >
    > > > Hello, Folks. This is my first post to this Discussion Group. I must

    > have
    > > > Dain Bramage to not be able to come up with an answer, but I can't. I

    > need
    > > > some help.
    > > > Below is an example of my problem.
    > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > I need a formula that will match value H26 from the following table and
    > > > return "Green" as the result.
    > > >
    > > >
    > > > H2 H3 H4 H5 H6 H7 Red
    > > > H9 H10 H11 H12 H13 H14 White
    > > > H16 H17 H18 H19 H20 H21 Blue
    > > > H23 H24 H25 H26 H27 H28 Green
    > > > H30 H31 H32 H33 H34 H35 Black
    > > > H37 H38 H39 H40 H41 H42 Purple
    > > >
    > > > Any help, pointers, suggestions or direction will be greatly

    > appreciated.
    > > > Thanks, - Randy

    >
    >
    >


  143. #143
    Bob Phillips
    Guest

    Re: Array index, match problem

    Randy,

    In VBA the code would look like

    Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"), 2,
    9) - 1) / 7 + 1)

    If you want to use the variable X without going via J19, use

    Dim x
    x = "H30"
    Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) / 7 +
    1)



    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "RAP" <[email protected]> wrote in message
    news:[email protected]...
    > Bob,
    > Thanks again for the formula. I have my app running now, thanks to you.

    I
    > would like to ask you another question, but I think it belongs in the
    > "Programming" board. How would I place your formula in VB script, instead

    of
    > inserting it into a cell? Also, the data in the "J19 input cell" is being
    > placed there (pasted) by a range variable, "X".
    >
    > Like I said, my app is functioning, but I want to achieve results using

    more
    > programming and less cursoring around, like I mentioned before.
    >
    > Thanks,
    > Randy
    >
    >
    > "Bob Phillips" wrote:
    >
    > > Just for interest, two less functions
    > >
    > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Duke Carey" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > How about
    > > >
    > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > >
    > > > which assumes the colors are in cells G1:G6
    > > >
    > > >
    > > >
    > > > "RAP" wrote:
    > > >
    > > > > Hello, Folks. This is my first post to this Discussion Group. I

    must
    > > have
    > > > > Dain Bramage to not be able to come up with an answer, but I can't.

    I
    > > need
    > > > > some help.
    > > > > Below is an example of my problem.
    > > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > > I need a formula that will match value H26 from the following table

    and
    > > > > return "Green" as the result.
    > > > >
    > > > >
    > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > H9 H10 H11 H12 H13 H14 White
    > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > >
    > > > > Any help, pointers, suggestions or direction will be greatly

    > > appreciated.
    > > > > Thanks, - Randy

    > >
    > >
    > >




  144. #144
    RAP
    Guest

    Re: Array index, match problem

    Bob,
    Once again, thanks. Works great. Now, how do I get the result of the
    formula (Red, White, etc...) pasted into yet another cell? I still have the
    mind-set that the result is in a cell somewhere. As soon as I can start
    thinking "programmatically", these simple questions will, well, at least they
    should diminish.

    Thanks again,
    Randy

    "Bob Phillips" wrote:

    > Randy,
    >
    > In VBA the code would look like
    >
    > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"), 2,
    > 9) - 1) / 7 + 1)
    >
    > If you want to use the variable X without going via J19, use
    >
    > Dim x
    > x = "H30"
    > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) / 7 +
    > 1)
    >
    >
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "RAP" <[email protected]> wrote in message
    > news:[email protected]...
    > > Bob,
    > > Thanks again for the formula. I have my app running now, thanks to you.

    > I
    > > would like to ask you another question, but I think it belongs in the
    > > "Programming" board. How would I place your formula in VB script, instead

    > of
    > > inserting it into a cell? Also, the data in the "J19 input cell" is being
    > > placed there (pasted) by a range variable, "X".
    > >
    > > Like I said, my app is functioning, but I want to achieve results using

    > more
    > > programming and less cursoring around, like I mentioned before.
    > >
    > > Thanks,
    > > Randy
    > >
    > >
    > > "Bob Phillips" wrote:
    > >
    > > > Just for interest, two less functions
    > > >
    > > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > > >
    > > > --
    > > >
    > > > HTH
    > > >
    > > > RP
    > > > (remove nothere from the email address if mailing direct)
    > > >
    > > >
    > > > "Duke Carey" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > How about
    > > > >
    > > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > > >
    > > > > which assumes the colors are in cells G1:G6
    > > > >
    > > > >
    > > > >
    > > > > "RAP" wrote:
    > > > >
    > > > > > Hello, Folks. This is my first post to this Discussion Group. I

    > must
    > > > have
    > > > > > Dain Bramage to not be able to come up with an answer, but I can't.

    > I
    > > > need
    > > > > > some help.
    > > > > > Below is an example of my problem.
    > > > > > Input cell = J19. Input Value = H26. Formula placed in cell J20.
    > > > > > I need a formula that will match value H26 from the following table

    > and
    > > > > > return "Green" as the result.
    > > > > >
    > > > > >
    > > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > > H9 H10 H11 H12 H13 H14 White
    > > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > > >
    > > > > > Any help, pointers, suggestions or direction will be greatly
    > > > appreciated.
    > > > > > Thanks, - Randy
    > > >
    > > >
    > > >

    >
    >
    >


  145. #145
    Bob Phillips
    Guest

    Re: Array index, match problem

    Hi again Randy,

    That would be a single assignment to a cell, say M2, and assuming variable X
    has that lookup value

    Range("M2").Value = Application.Index(Range("$G$1:$G$6"), (Mid(X, 2, 9) - 1)
    / 7 + 1)


    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "RAP" <[email protected]> wrote in message
    news:[email protected]...
    > Bob,
    > Once again, thanks. Works great. Now, how do I get the result of the
    > formula (Red, White, etc...) pasted into yet another cell? I still have

    the
    > mind-set that the result is in a cell somewhere. As soon as I can start
    > thinking "programmatically", these simple questions will, well, at least

    they
    > should diminish.
    >
    > Thanks again,
    > Randy
    >
    > "Bob Phillips" wrote:
    >
    > > Randy,
    > >
    > > In VBA the code would look like
    > >
    > > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(Range("J19"),

    2,
    > > 9) - 1) / 7 + 1)
    > >
    > > If you want to use the variable X without going via J19, use
    > >
    > > Dim x
    > > x = "H30"
    > > Debug.Print Application.Index(Range("$G$11:$G$16"), (Mid(x, 2, 9) - 1) /

    7 +
    > > 1)
    > >
    > >
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "RAP" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Bob,
    > > > Thanks again for the formula. I have my app running now, thanks to

    you.
    > > I
    > > > would like to ask you another question, but I think it belongs in the
    > > > "Programming" board. How would I place your formula in VB script,

    instead
    > > of
    > > > inserting it into a cell? Also, the data in the "J19 input cell" is

    being
    > > > placed there (pasted) by a range variable, "X".
    > > >
    > > > Like I said, my app is functioning, but I want to achieve results

    using
    > > more
    > > > programming and less cursoring around, like I mentioned before.
    > > >
    > > > Thanks,
    > > > Randy
    > > >
    > > >
    > > > "Bob Phillips" wrote:
    > > >
    > > > > Just for interest, two less functions
    > > > >
    > > > > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    > > > >
    > > > > --
    > > > >
    > > > > HTH
    > > > >
    > > > > RP
    > > > > (remove nothere from the email address if mailing direct)
    > > > >
    > > > >
    > > > > "Duke Carey" <[email protected]> wrote in message
    > > > > news:[email protected]...
    > > > > > How about
    > > > > >
    > > > > > =INDEX($G$1:$G$6,ROUND((3+SUBSTITUTE(UPPER(J19),"H",""))/7,0))
    > > > > >
    > > > > > which assumes the colors are in cells G1:G6
    > > > > >
    > > > > >
    > > > > >
    > > > > > "RAP" wrote:
    > > > > >
    > > > > > > Hello, Folks. This is my first post to this Discussion Group.

    I
    > > must
    > > > > have
    > > > > > > Dain Bramage to not be able to come up with an answer, but I

    can't.
    > > I
    > > > > need
    > > > > > > some help.
    > > > > > > Below is an example of my problem.
    > > > > > > Input cell = J19. Input Value = H26. Formula placed in cell

    J20.
    > > > > > > I need a formula that will match value H26 from the following

    table
    > > and
    > > > > > > return "Green" as the result.
    > > > > > >
    > > > > > >
    > > > > > > H2 H3 H4 H5 H6 H7 Red
    > > > > > > H9 H10 H11 H12 H13 H14 White
    > > > > > > H16 H17 H18 H19 H20 H21 Blue
    > > > > > > H23 H24 H25 H26 H27 H28 Green
    > > > > > > H30 H31 H32 H33 H34 H35 Black
    > > > > > > H37 H38 H39 H40 H41 H42 Purple
    > > > > > >
    > > > > > > Any help, pointers, suggestions or direction will be greatly
    > > > > appreciated.
    > > > > > > Thanks, - Randy
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




  146. #146
    Alan Beban
    Guest

    Re: Array index, match problem

    Bob Phillips wrote:
    > Just for interest, two less functions
    >
    > =INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)
    >

    For a formula that is independent of the size of the table of data or
    the regularity of its contents, if the functions in the freely
    downloadable file at http://home.pacbell.net are available to your workbook

    =OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))

    Alan Beban

  147. #147
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    >Bob Phillips wrote:
    >>Just for interest, two less functions
    >>
    >>=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    >
    >For a formula that is independent of the size of the table of data or
    >the regularity of its contents, if the functions in the freely
    >downloadable file at http://home.pacbell.net are available to your workbook
    >
    >=OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,
    >-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))


    Assuming someone would use your function library, wouldn't they want to
    do so efficiently? Only one udf call needed (MakeArray).

    =INDEX(DataTable,INT((MATCH(J19,MakeArray(DataTable,1),0)-1)
    /COLUMNS(DataTable))+1,COLUMNS(DataTable))


  148. #148
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    >
    >>Bob Phillips wrote:
    >>
    >>>Just for interest, two less functions
    >>>
    >>>=INDEX($G$1:$G$6,(MID(J19,2,9)-1)/7+1)

    >>
    >>For a formula that is independent of the size of the table of data or
    >>the regularity of its contents, if the functions in the freely
    >>downloadable file at http://home.pacbell.net are available to your workbook
    >>
    >>=OFFSET(INDIRECT(ArrayMatch(J19,dataTable,"A")),0,
    >>-INDEX(ArrayMatch(J19,dataTable),1,2)+COLUMNS(dataTable))

    >
    >
    > Assuming someone would use your function library, wouldn't they want to
    > do so efficiently? Only one udf call needed (MakeArray).
    >
    > =INDEX(DataTable,INT((MATCH(J19,MakeArray(DataTable,1),0)-1)
    > /COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >

    Well, isn't that interesting! I would have thought the suggestion would
    be something like fill down a range on Sheet8, for example, named CxRV with

    =INDIRECT(ADDRESS(
    ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))
    (compliments of Chip Pearson)

    and then enter something like

    =INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))

    in order to use only built-in functions and avoid at all costs the
    dreaded Array Functions.

    But no, here's Harlan Grove, instead carping about which of the dreaded
    Array Functions is more efficient. Onward and upward!

    Alan Beban

  149. #149
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >But no, here's Harlan Grove, instead carping about which of the dreaded
    >Array Functions is more efficient. Onward and upward!


    Just pointing out that you don't know how to use your own function
    library efficiently.

    Onward perhaps. Never upward.


  150. #150
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >=INDIRECT(ADDRESS(
    >ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),
    >COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))

    ....
    >=INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >
    >in order to use only built-in functions and avoid at all costs the
    >dreaded Array Functions.

    ....

    Ugh! Not the best way by a long shot! All it takes is a single array
    formula

    =INDEX(DataTable,MATCH(TRUE,COUNTIF(OFFSET(DataTable,
    ROW(DataTable)-CELL("Row",DataTable),0,1,),J1)>0,0),COLUMNS(DataTable))


  151. #151
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>=INDIRECT(ADDRESS(
    >>ROW(DataTable)+INT((ROW()-ROW(CxRV))/COLUMNS(DataTable)),
    >>COLUMN(DataTable)+MOD(ROW()-ROW(CxRV),COLUMNS(DataTable)),4,,"Sheet8"))

    >
    > ...
    >
    >>=INDEX(DataTable,INT((MATCH(J19,CxRV,0)-1)/COLUMNS(DataTable))+1,COLUMNS(DataTable))
    >>
    >>in order to use only built-in functions and avoid at all costs the
    >>dreaded Array Functions.

    >
    > ...
    >
    > Ugh! Not the best way by a long shot! All it takes is a single array
    > formula
    >
    > =INDEX(DataTable,MATCH(TRUE,COUNTIF(OFFSET(DataTable,
    > ROW(DataTable)-CELL("Row",DataTable),0,1,),J1)>0,0),COLUMNS(DataTable))
    >


    J1 should be J19 to conform to the original specification.

    Alan Beban

  152. #152
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>But no, here's Harlan Grove, instead carping about which of the dreaded
    >>Array Functions is more efficient. Onward and upward!

    >
    >
    > Just pointing out that you don't know how to use your own function
    > library efficiently.
    >


    Perhaps you could quantify for the users the difference in efficiency.

    Alan Beban

  153. #153
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >Perhaps you could quantify for the users the difference in efficiency.


    Nah, I'll leave that for you as an exercise since you're the one who
    needs to learn about efficiency. Here's a hint: one udf call will
    invariably be faster than two, even when there are a few extra built-in
    function calls with the single udf call.


  154. #154
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>Perhaps you could quantify for the users the difference in efficiency.

    >
    >
    > Nah, I'll leave that for you as an exercise since you're the one who
    > needs to learn about efficiency.


    Cute. But I suspect the real reason you don't want to deal with it is
    that the so-called "efficiency" to which you and many
    programmer/developers sometimes refer often involves nanoseconds of
    difference that are totally irrelevant to most users in most
    applications; interesting to you for purposes of posting oneupmanship,
    but somewhat misleading for users generally.

    Alan Beban

  155. #155
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ..=2E.
    >Cute. But I suspect the real reason you don't want to deal with it is
    >that the so-called "efficiency" to which you and many
    >programmer/developers sometimes refer often involves nanoseconds of
    >difference that are totally irrelevant to most users in most
    >applications; interesting to you for purposes of posting oneupmanship,
    >but somewhat misleading for users generally.


    Fine. Then consider whether the MakeArray formula,

    =3DINDEX(DataTable,INT((MATCH(J1=AD9,MakeArray(DataTable,1),0)-1)
    /COLUMNS(DataTable))+1,COLUMNS=AD(DataTable))

    a single MATCH against the data range transformed into a 1D array, with
    the result adjusted by a division inside INT to return the row number,
    against the ArrayMatch formula,

    =3DOFFSET(INDIRECT(ArrayMatch(J=AD19,dataTable,"A")),0,
    -INDEX(ArrayMatch(J19,dataTab=ADle),1,2)+COLUMNS(dataTable))

    first returning the cell address of the matching cell then using
    another call to fix the column offset. It's subjective whether the row
    index contortions of the MakeArray formula are more obscure than the
    column offset contortions of the 2 ArrayMatch formula.

    For that matter, you could also have used

    =3DINDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))

    which would have been a LOT simpler than either of the others.
    Simplicity is good.

    Both the MakeArray and the single ArrayMatch formulas involve no
    volatile function calls, so they won't cause Excel to prompt users to
    save any file containing them if users try to close such workbooks
    without making any changes. Your two ArrayMatch formula, due to OFFSET
    and INDIRECT calls, would cause such confusing prompts.

    Is that an acceptable user consideration?


  156. #156
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>Cute. But I suspect the real reason you don't want to deal with it is
    >>that the so-called "efficiency" to which you and many
    >>programmer/developers sometimes refer often involves nanoseconds of
    >>difference that are totally irrelevant to most users in most
    >>applications; interesting to you for purposes of posting oneupmanship,
    >>but somewhat misleading for users generally.

    >
    >
    > Fine. Then consider whether the MakeArray formula,
    >
    > =INDEX(DataTable,INT((MATCH(J1*9,MakeArray(DataTable,1),0)-1)
    > /COLUMNS(DataTable))+1,COLUMNS*(DataTable))
    >
    > a single MATCH against the data range transformed into a 1D array, with
    > the result adjusted by a division inside INT to return the row number,
    > against the ArrayMatch formula,
    >
    > =OFFSET(INDIRECT(ArrayMatch(J*19,dataTable,"A")),0,
    > -INDEX(ArrayMatch(J19,dataTab*le),1,2)+COLUMNS(dataTable))
    >
    > first returning the cell address of the matching cell then using
    > another call to fix the column offset. It's subjective whether the row
    > index contortions of the MakeArray formula are more obscure than the
    > column offset contortions of the 2 ArrayMatch formula.
    >
    > For that matter, you could also have used
    >
    > =INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))
    >
    > which would have been a LOT simpler than either of the others.
    > Simplicity is good.
    >
    > Both the MakeArray and the single ArrayMatch formulas involve no
    > volatile function calls, so they won't cause Excel to prompt users to
    > save any file containing them if users try to close such workbooks
    > without making any changes. Your two ArrayMatch formula, due to OFFSET
    > and INDIRECT calls, would cause such confusing prompts.
    >
    > Is that an acceptable user consideration?
    >

    It's certainly more constructive, particularly the suggestion of

    =INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))

    which is significantly faster than the one including the two ArrayMatch
    function calls that I originally posted. Would have been nice had you
    focused on the instructional value for the users in the first place,
    rather than just on stroking your ego. But then, there you go!

    By the way, the formula with the MakeArray function call seems to return
    an error if the data table exceeds 65536 elements (I haven't yet
    identified why; it might be fixable), while neither of the ArrayMatch
    formulas seems to--though they are slower.

    Alan Beban

  157. #157
    Alan Beban
    Guest

    Re: Array index, match problem

    Alan Beban wrote:
    > Harlan Grove wrote:
    > . . .Then consider whether the MakeArray formula,
    >>
    >> =INDEX(DataTable,INT((MATCH(J1*9,MakeArray(DataTable,1),0)-1)
    >> /COLUMNS(DataTable))+1,COLUMNS*(DataTable))
    >>
    >> a single MATCH against the data range transformed into a 1D array, with
    >> the result adjusted by a division inside INT to return the row number,
    >> against the ArrayMatch formula,
    >>
    >> =OFFSET(INDIRECT(ArrayMatch(J*19,dataTable,"A")),0,
    >> -INDEX(ArrayMatch(J19,dataTab*le),1,2)+COLUMNS(dataTable))
    >>
    >> first returning the cell address of the matching cell then using
    >> another call to fix the column offset. It's subjective whether the row
    >> index contortions of the MakeArray formula are more obscure than the
    >> column offset contortions of the 2 ArrayMatch formula.
    >>
    >>...By the way, the formula with the MakeArray function call seems to return

    > an error if the data table exceeds 65536 elements . . . .


    The problem is with the built-in INDEX function; it fails if the array
    or reference contains more than 65536 elements.

    Alan Beban

  158. #158
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >The problem is with the built-in INDEX function; it fails if the array
    >or reference contains more than 65536 elements.


    Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    chokes after 65,535 elements.

    Which would argue in favor of using the COUNTIF function provided in
    another branch of this thread. It's one drawback is the volatile OFFSET
    call. It'd always recalc, but it'd be lots faster than even a single
    udf call.


  159. #159
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    ....
    >=INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))
    >
    >which is significantly faster than the one including the two ArrayMatch
    >function calls that I originally posted. Would have been nice had you
    >focused on the instructional value for the users in the first place,
    >rather than just on stroking your ego. But then, there you go!


    Of course you could have offerred it too, if you had thought of it. But
    then, there you go!

    >By the way, the formula with the MakeArray function call seems to return
    >an error if the data table exceeds 65536 elements (I haven't yet
    >identified why; it might be fixable), while neither of the ArrayMatch
    >formulas seems to--though they are slower.


    Excel can't handle any arrays with more than 65,535 entries in either
    of 1 or 2 dimensions, as you should know. It's questionable whether
    anyone should try to use brute force matching on so many cells. It'd be
    slow even without any udfs or volatile functions. There are tasks for
    which indexed database searches would be far more appropriate than
    unindexed spreadsheet searches. If the OP has so many entries to
    search, the OP is being foolish using a spreadsheet for the task.
    However, if the OP is only searching a few hundred entries or fewer,
    your caveat provides completeness of specification but is of no
    practical relevance.


  160. #160
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    > ...
    >
    >>The problem is with the built-in INDEX function; it fails if the array
    >>or reference contains more than 65536 elements.

    >
    >
    > Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    > chokes after 65,535 elements. . . .


    Dim arr, i
    ReDim arr(1 To 65536)
    For i = 1 To 65536
    arr(i) = i * 2
    Next
    MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.

    In xl2000 and earlier it fails on 5462 elements or greater.

    Alan Beban

  161. #161
    Alan Beban
    Guest

    Re: Array index, match problem

    Alan Beban wrote:
    > Harlan Grove wrote:
    >
    >> Alan Beban wrote...
    >> ...
    >>
    >>> The problem is with the built-in INDEX function; it fails if the array
    >>> or reference contains more than 65536 elements.

    >>
    >>
    >>
    >> Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >> chokes after 65,535 elements. . . .

    >
    >
    > Dim arr, i
    > ReDim arr(1 To 65536)
    > For i = 1 To 65536
    > arr(i) = i * 2
    > Next
    > MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >
    > In xl2000 and earlier it fails on 5462 elements or greater.
    >
    > Alan Beban


    Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    xl2000 or xl2002).

    Alan Beban

  162. #162
    Harlan Grove
    Guest

    Re: Array index, match problem

    Alan Beban wrote...
    >>>Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >>>chokes after 65,535 elements. . . .

    >>
    >> Dim arr, i
    >> ReDim arr(1 To 65536)
    >> For i = 1 To 65536
    >> arr(i) = i * 2
    >> Next
    >> MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >>
    >> In xl2000 and earlier it fails on 5462 elements or greater.

    >
    >Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    >xl2000 or xl2002).


    Not comparable. A:B is a range, not an array. But you're correct that
    INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and
    subsequent arguments and converts them to long integers.


  163. #163
    Alan Beban
    Guest

    Re: Array index, match problem

    Harlan Grove wrote:
    > Alan Beban wrote...
    >
    >>>>Are you sure there isn't a problem with 65,536 elements? I'd suspect it
    >>>>chokes after 65,535 elements. . . .
    >>>
    >>> Dim arr, i
    >>> ReDim arr(1 To 65536)
    >>> For i = 1 To 65536
    >>> arr(i) = i * 2
    >>> Next
    >>> MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.
    >>>
    >>>In xl2000 and earlier it fails on 5462 elements or greater.

    >>
    >>Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
    >>xl2000 or xl2002).

    >
    >
    > Not comparable. A:B is a range, not an array. But you're correct that
    > INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and
    > subsequent arguments and converts them to long integers.
    >


    It seems that the limitation on the VBA invocation of the INDEX function
    is a bit subtler; it is not limited by the number of the elements in the
    array (see arr1 below) but apparently by the number of elements in a
    dimension.

    Sub testIt3a()
    Dim arr1, arr2, arr3
    Dim i As Long, j As Long
    Dim x, y, z

    '65536 rows, 2 columns
    ReDim arr1(1 To 65536, 1 To 2)
    For i = 1 To 65536: For j = 1 To 2
    arr1(i, j) = i * 2 + j
    Next: Next

    '1 row, 65536 columns
    ReDim arr2(1 To 65536)
    For i = 1 To 65536
    arr2(i) = i
    Next

    '1 row, 65537 columns
    ReDim arr3(1 To 65537)
    For i = 1 To 65537
    arr3(i) = i
    Next

    x = Application.Index(arr1, 65536, 2)
    Debug.Print x '<---returns 131074
    y = Application.Index(arr2, 65536)
    Debug.Print y '<---returns 65536
    z = Application.Index(arr3, 65536) 'Type mismatch error
    End Sub

    Alan Beban

+ 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