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