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

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

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

--

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

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

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

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

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

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

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

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

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