Compare value to Lookup table min & max, return contents of 3rd ce

[Struggling in Sheffield]

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.

 

[L. Howard Kittle]

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]

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.

 

[Struggling in Sheffield]

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]

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.

 

[L. Howard Kittle]

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]

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]

Welcome, Steve
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Struggling in Sheffield]

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.