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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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