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.