Hi,
I would like to lookup a value (entered in cell D1) in the table
below. For example if it were between 0 and 2 it would return R1, if
it were between 3 and 5 it would return R2, etc.
(starting in column A1)
0 2 R1
3 5 R2
6 8 R3
Any help would be gratefully received
LROCCO
118eb7eb339f@s19g2000prg.googlegroups.com:
> Hi,
>
> I would like to lookup a value (entered in cell D1) in the table
> below. For example if it were between 0 and 2 it would return R1, if
> it were between 3 and 5 it would return R2, etc.
>
> (starting in column A1)
> 0 2 R1
> 3 5 R2
> 6 8 R3
>
> Any help would be gratefully received
---------
I would remove column 2, and be left with Columns A1 and A3 -> now A2.
I would use a query
SELECT TOP 1 A2 FROM MyTable WHERE D1 >= A1 ORDER BY A1
---------
If the last column, R1 were not persitent data (if it were used only to
identify the range in which D1 falls) I would also remove the last column
(A3 in your schema, A2 in my suggestion) and use
SELECT COUNT(*) FROM Table1 WHERE A1 <= D1
---------
It's quite likely that a unique non-null index on A1 would speed things
up.
---------
If all the ranges were (0,2), (3,5), (6,8) .... (n, n+2) I'd remove the
third column as well (that is, delete the table) and use D1 \ 3 + 1.
---------
If I liked to use zero-based collections and arrays (and I do), I'd use
D1 \ 3.
( air code, script, sql, whatever over morning coffee).
--
lyle fairfield
"LROCCO"
news:f103cc04-515a-4354-b9cd-118eb7eb339f@s19g2000prg.google groups.com...
> Hi,
>
> I would like to lookup a value (entered in cell D1) in the table
> below. For example if it were between 0 and 2 it would return R1, if
> it were between 3 and 5 it would return R2, etc.
>
> (starting in column A1)
> 0 2 R1
> 3 5 R2
> 6 8 R3
>
> Any help would be gratefully received
Use an IIF statement in a query (untested):
Iif([MyField] between 0 and 2, "R1", Iif([MyField] between 3 and 5, "R2",
"R3")))
Don't store calculated fields in tables!
Keith.
www.keithwilby.com