Hi Rick

Perhaps Shane is under the same mis-apprehension as myself, that Lookup
can only be used with a 2 column array.
Following your post, I have looked again at Lookup and I can see that
=LOOKUP(F1,A1:E5)
works, but "does exactly what it says on the tin", it returns the value
from the Last column of the array, in my case column E.

I had always assumed that the 2 columns used had to be adjacent, as you
cannot specify an Offset with Lookup, as you can with Vlookup and
Hlookup.

Thank you for drawing my attention to this.

--
Regards

Roger Govier


"Ragdyer" wrote in message
...
I don't quite agree with your statement that Lookup() cannot work on
datalists of more then 2 columns.

Both forms of Lookup(), vector and array, can work on *any* size
datalist.

Would you care to elaborate, in case I misunderstood you?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------
"ShaneDevenshire" wrote in
message ...
Hi,

I like using LOOKUP but in most cases that is not an option because
you
have
more than 2 columns in the lookup table or you want an exact match,
in
which
case I would suggest the standard approach:

=IF(F1<=100,"Inc",VLOOKUP(AC1,BA$1:BB$10,2))

Where AC1 is the value you want to look up and BA1:BB10 is the range
containing the lookup table.

This formula can also be done as a stand alone formula:


=IF(F1<=100,"Inc",VLOOKUP(AC1,{0.099,1;0.199,2;0.2 74,3;0.349,4;0.424,5;0.499
,6;0.574,7;0.649,8;0.749,9;1,10},2))

One final comment on VLOOKUP verses LOOKUP, you will find that most
users
and companys use VLOOKUP and far fewer use LOOKUP, given a choice
then it
is
better to use what other user's are comfortable with. However, there
are
some very niffty uses of LOOKUP which can't be duplicated with
VLOOKUP! So
kodos to RD for using it!
--
Cheers,
Shane Devenshire


"bob" wrote:

In column AC I have values that range from .000 to 1.000.
In column F I have values that range from 1 to 2,000.

In column AV, I want to place a value from 1 to 10, depending on
the
corresponding value in column AC. The only exception is that if
column F
<=100, the correspinding value in AV should be "Inc." (for
Incomplete)

The values in AC should correspond to the values in AV, as follows:

.000 to .099 = 1
.100 to .199 = 2
.200 to .274 = 3
.275 to .349 = 4
.350 to .424 = 5
.425 to .499 = 6
.500 to .574 = 7
.575 to .649 = 8
.650 to .749 = 9
.750 to 1.000 = 10

thanks,
Bob