Hi
Excel 2003
As per the example created below I'm using a formula to return a number
value (in D1) that can be anywhere between 0.00 & 6.00 (including 0.00 & 6.00)
I want my formula (in E1) to compare the value (D1) to min & max values in a
table (A4:A9), so whichever min & max my value equals or falls between, it
will return the value in a 3rd cell adjacent the appropriate min & max
cells(C4:C9) :

A B C D E
1 4.75 Pink
2
3 Min Max
4 0.00 0.99 Blue
5 1.00 1.99 Green
6 2.00 2.99 Yellow
7 3.00 3.99 Red
8 4.00 4.99 Pink
9 5.00 6.00 Orange


My calculated value is in D1
The formula (in E1) needs to compare D1 with the table in A4:B9
then return the corresponding value (colour) from C4:C9 and place it in E1

Hope my explanation is clear enough, thanks for looking & hope you can help.
Cheers.

Try this.

With your MAX values in D1 to D6 and the colors in E1 to E6 and the MAX
value to look up is in A1....

=VLOOKUP(A1,D1:E6,2,1)

HTH
Regards,
Howard

"Struggling in Sheffield"
wrote in message ...
Hi
Excel 2003
As per the example created below I'm using a formula to return a number
value (in D1) that can be anywhere between 0.00 & 6.00 (including 0.00 &
6.00)
I want my formula (in E1) to compare the value (D1) to min & max values in
a
table (A4:A9), so whichever min & max my value equals or falls between, it
will return the value in a 3rd cell adjacent the appropriate min & max
cells(C4:C9) :

A B C D E
1 4.75 Pink
2
3 Min Max
4 0.00 0.99 Blue
5 1.00 1.99 Green
6 2.00 2.99 Yellow
7 3.00 3.99 Red
8 4.00 4.99 Pink
9 5.00 6.00 Orange


My calculated value is in D1
The formula (in E1) needs to compare D1 with the table in A4:B9
then return the corresponding value (colour) from C4:C9 and place it in E1

Hope my explanation is clear enough, thanks for looking & hope you can
help.
Cheers.

Sorry Howard but this is just giving me a #REF error. My real life full
formula (in N218) using your help is:

=IF(OR(I218="",J218="",K218=""),"",IF(L218=M218,"X ",VLOOKUP(L218,J425:J436,2,1))

A bit more complex I know(!), I just tried to simplify for clarity.
My calculated value is in L218, calculated from numbers in cells I218:K218.
If my calculated value (L218) is the same as another value in K218, I want
the formula (in N218) to return "X".
If the first two conditions are false, I have a table of Min and Max values
in two columns (I425:J436).
Whatever Min & Max my calculated value falls between, I want the value in a
cell adjacent to the table (K425:K436) returned to N218.

Cheers.

"L. Howard Kittle" wrote:

Try this.

With your MAX values in D1 to D6 and the colors in E1 to E6 and the MAX
value to look up is in A1....

=VLOOKUP(A1,D1:E6,2,1)

HTH
Regards,
Howard

"Struggling in Sheffield"
wrote in message ...
Hi
Excel 2003
As per the example created below I'm using a formula to return a number
value (in D1) that can be anywhere between 0.00 & 6.00 (including 0.00 &
6.00)
I want my formula (in E1) to compare the value (D1) to min & max values in
a
table (A4:A9), so whichever min & max my value equals or falls between, it
will return the value in a 3rd cell adjacent the appropriate min & max
cells(C4:C9) :

A B C D E
1 4.75 Pink
2
3 Min Max
4 0.00 0.99 Blue
5 1.00 1.99 Green
6 2.00 2.99 Yellow
7 3.00 3.99 Red
8 4.00 4.99 Pink
9 5.00 6.00 Orange


My calculated value is in D1
The formula (in E1) needs to compare D1 with the table in A4:B9
then return the corresponding value (colour) from C4:C9 and place it in E1

Hope my explanation is clear enough, thanks for looking & hope you can
help.
Cheers.

Hi again Howard,
Just been playing around with it and changed my formula to:

=IF(OR(I218="",J218="",K218=""),"",IF(L218=M218,"X ",VLOOKUP(L218,$I$425:$J$436,2,1)))

This takes in my Min & Max table values and it now seems to work.

Going back to my original query and applying your help, the formula (I
think) in E1 is:

=VLOOKUP(D1,$A$4:$B$9,2,1)

Many thanks for your input, got me looking in the right direction.
Cheers.

"Struggling in Sheffield" wrote:

Sorry Howard but this is just giving me a #REF error. My real life full
formula (in N218) using your help is:

=IF(OR(I218="",J218="",K218=""),"",IF(L218=M218,"X ",VLOOKUP(L218,J425:J436,2,1))

A bit more complex I know(!), I just tried to simplify for clarity.
My calculated value is in L218, calculated from numbers in cells I218:K218.
If my calculated value (L218) is the same as another value in K218, I want
the formula (in N218) to return "X".
If the first two conditions are false, I have a table of Min and Max values
in two columns (I425:J436).
Whatever Min & Max my calculated value falls between, I want the value in a
cell adjacent to the table (K425:K436) returned to N218.

Cheers.

"L. Howard Kittle" wrote:

Try this.

With your MAX values in D1 to D6 and the colors in E1 to E6 and the MAX
value to look up is in A1....

=VLOOKUP(A1,D1:E6,2,1)

HTH
Regards,
Howard

"Struggling in Sheffield"
wrote in message ...
Hi
Excel 2003
As per the example created below I'm using a formula to return a number
value (in D1) that can be anywhere between 0.00 & 6.00 (including 0.00 &
6.00)
I want my formula (in E1) to compare the value (D1) to min & max values in
a
table (A4:A9), so whichever min & max my value equals or falls between, it
will return the value in a 3rd cell adjacent the appropriate min & max
cells(C4:C9) :

A B C D E
1 4.75 Pink
2
3 Min Max
4 0.00 0.99 Blue
5 1.00 1.99 Green
6 2.00 2.99 Yellow
7 3.00 3.99 Red
8 4.00 4.99 Pink
9 5.00 6.00 Orange


My calculated value is in D1
The formula (in E1) needs to compare D1 with the table in A4:B9
then return the corresponding value (colour) from C4:C9 and place it in E1

Hope my explanation is clear enough, thanks for looking & hope you can
help.
Cheers.

Howard,
Playing around with it some more I'm afraid it doesn't work. I only had
whole numbers in my calculated value cell (N218). Changing the numbers in my
lookup table (Min values I425:I436 & Max values J425:J436) to different
values including some fractional values has resulted in the formula not
returning the correct value from the adj cell (K425:K436). The formula
doesn't put my calculated value between the appropriate Min & Max.
Back to square one I'm afraid, my original question still stands.
Cheers.

"Struggling in Sheffield" wrote:

Hi again Howard,
Just been playing around with it and changed my formula to:

=IF(OR(I218="",J218="",K218=""),"",IF(L218=M218,"X ",VLOOKUP(L218,$I$425:$J$436,2,1)))

This takes in my Min & Max table values and it now seems to work.

Going back to my original query and applying your help, the formula (I
think) in E1 is:

=VLOOKUP(D1,$A$4:$B$9,2,1)

Many thanks for your input, got me looking in the right direction.
Cheers.

"Struggling in Sheffield" wrote:

Sorry Howard but this is just giving me a #REF error. My real life full
formula (in N218) using your help is:

=IF(OR(I218="",J218="",K218=""),"",IF(L218=M218,"X ",VLOOKUP(L218,J425:J436,2,1))

A bit more complex I know(!), I just tried to simplify for clarity.
My calculated value is in L218, calculated from numbers in cells I218:K218.
If my calculated value (L218) is the same as another value in K218, I want
the formula (in N218) to return "X".
If the first two conditions are false, I have a table of Min and Max values
in two columns (I425:J436).
Whatever Min & Max my calculated value falls between, I want the value in a
cell adjacent to the table (K425:K436) returned to N218.

Cheers.

"L. Howard Kittle" wrote:

Try this.

With your MAX values in D1 to D6 and the colors in E1 to E6 and the MAX
value to look up is in A1....

=VLOOKUP(A1,D1:E6,2,1)

HTH
Regards,
Howard

"Struggling in Sheffield"
wrote in message ...
Hi
Excel 2003
As per the example created below I'm using a formula to return a number
value (in D1) that can be anywhere between 0.00 & 6.00 (including 0.00 &
6.00)
I want my formula (in E1) to compare the value (D1) to min & max values in
a
table (A4:A9), so whichever min & max my value equals or falls between, it
will return the value in a 3rd cell adjacent the appropriate min & max
cells(C4:C9) :

A B C D E
1 4.75 Pink
2
3 Min Max
4 0.00 0.99 Blue
5 1.00 1.99 Green
6 2.00 2.99 Yellow
7 3.00 3.99 Red
8 4.00 4.99 Pink
9 5.00 6.00 Orange


My calculated value is in D1
The formula (in E1) needs to compare D1 with the table in A4:B9
then return the corresponding value (colour) from C4:C9 and place it in E1

Hope my explanation is clear enough, thanks for looking & hope you can
help.
Cheers.

I'm a bit lost on what's going on.

If you want, send me an example workbook and detail what and where you want
to happen. Send to:

lhkittle at comcast dot net (loose the spaces and convert the "at" and
"dot")

May not be able to fix it but will try.

Regards,
Howard

"Struggling in Sheffield"
wrote in message ...
Hi
Excel 2003
As per the example created below I'm using a formula to return a number
value (in D1) that can be anywhere between 0.00 & 6.00 (including 0.00 &
6.00)
I want my formula (in E1) to compare the value (D1) to min & max values in
a
table (A4:A9), so whichever min & max my value equals or falls between, it
will return the value in a 3rd cell adjacent the appropriate min & max
cells(C4:C9) :

A B C D E
1 4.75 Pink
2
3 Min Max
4 0.00 0.99 Blue
5 1.00 1.99 Green
6 2.00 2.99 Yellow
7 3.00 3.99 Red
8 4.00 4.99 Pink
9 5.00 6.00 Orange


My calculated value is in D1
The formula (in E1) needs to compare D1 with the table in A4:B9
then return the corresponding value (colour) from C4:C9 and place it in E1

Hope my explanation is clear enough, thanks for looking & hope you can
help.
Cheers.

Try this index/match variation which ties in tightly with the min-max bands
illustrated in your original post

First, re-input the "Max" values, viz make your reference table like this:

Min Max
0 1 Blue
1 2 Green
2 3 Yellow
3 4 Red
4 5 Pink
5 6 Orange

Then in E1, normal ENTER to confirm:
=INDEX(C4:C9,MATCH(1,INDEX((D1=A4:A9)*(D1<B4:B9), ),0))
(note the "<" comparator used for the upper bound)

Success? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Struggling in Sheffield" wrote:
.. Back to square one I'm afraid, my original question still stands.

Perfect Max, as usual I was using the right terms but need more experience to
get them in the right order!
Heartfelt thanks,
Steve.

"Max" wrote:

Try this index/match variation which ties in tightly with the min-max bands
illustrated in your original post

First, re-input the "Max" values, viz make your reference table like this:

Min Max
0 1 Blue
1 2 Green
2 3 Yellow
3 4 Red
4 5 Pink
5 6 Orange

Then in E1, normal ENTER to confirm:
=INDEX(C4:C9,MATCH(1,INDEX((D1=A4:A9)*(D1<B4:B9), ),0))
(note the "<" comparator used for the upper bound)

Success? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Struggling in Sheffield" wrote:
.. Back to square one I'm afraid, my original question still stands.

Welcome, Steve
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Struggling in Sheffield"
wrote in message ...
Perfect Max, as usual I was using the right terms but need more experience
to
get them in the right order!
Heartfelt thanks,
Steve.

Hi Howard,
If you're a bit lost I've been wandering the desert for 40 days and 40 nights!
Think I may have got lost somewhere when you used different cell references
to the ones I put in my example. I managed to get the thing to work 'sort of'
but there was obviously something I was missing. Tried lots of variations as
well but couldn't stumble on the right combination
Anyway no problem as I've tried Max's fix and it works whatever the table
values I use so question solved, but thank you very much for taking the time
to help.
Sorry I'm obviously not quite bright enough to use the help sometimes!
Thanks again,
Steve.

"L. Howard Kittle" wrote:

I'm a bit lost on what's going on.

If you want, send me an example workbook and detail what and where you want
to happen. Send to:

lhkittle at comcast dot net (loose the spaces and convert the "at" and
"dot")

May not be able to fix it but will try.

Regards,
Howard

"Struggling in Sheffield"
wrote in message ...
Hi
Excel 2003
As per the example created below I'm using a formula to return a number
value (in D1) that can be anywhere between 0.00 & 6.00 (including 0.00 &
6.00)
I want my formula (in E1) to compare the value (D1) to min & max values in
a
table (A4:A9), so whichever min & max my value equals or falls between, it
will return the value in a 3rd cell adjacent the appropriate min & max
cells(C4:C9) :

A B C D E
1 4.75 Pink
2
3 Min Max
4 0.00 0.99 Blue
5 1.00 1.99 Green
6 2.00 2.99 Yellow
7 3.00 3.99 Red
8 4.00 4.99 Pink
9 5.00 6.00 Orange


My calculated value is in D1
The formula (in E1) needs to compare D1 with the table in A4:B9
then return the corresponding value (colour) from C4:C9 and place it in E1

Hope my explanation is clear enough, thanks for looking & hope you can
help.
Cheers.