My comment was strictly directed at your statement that Lookup() was *not*
an option for lookup tables of more then 2 columns!

I mentioned *nothing* about "exact matches", or ease of referencing "other"
columns in the array, or which function was "better" then the other.

Roger picked-up exactly on the intent of my comment.

That was the sole agenda of my post.

We don't want OPs to get incorrect information from us ... do we?<bg

We all make our share of mistakes.
It's just appropriate behavior to try and keep the archives as accurate as
possible.

--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------


"ShaneDevenshire" wrote in
message ...
Hi Rick and Roger,

To clarify apparent misunderstandings:

1. I am not sure how you set LOOKUP to do Exact Matches? Is it as easy as
with VLOOKUP?
2. It is easy to set the third argument in VLOOKUP so that you can refer to
any column in a lookup table, I'm sure this can be done with LOOKUP but is
it
really as easy as modifying the 3rd argument to reference a cell and then
entering a single number in that cell? It seems to me that VLOOKUP looks at
any column(s) in the lookup table and as I understand it LOOKUP always looks
at the last column? I'm not sure how you make this easily dynamic?
3. If you looked at 5000 spreadsheets what percent would use VLOOKUP verses
LOOKUP, would you say about 50/50? I believe that more users use and are
comfortable with VLOOKUP.
4. The following is a piece of cake with VLOOKUP but I'm not sure how to do
it simply with LOOKUP:

=SUM(VLOOKUP(D1,G1:P5,{2,3,5,7},TRUE)) (array entered)


I do like LOOKUP, it finds the last text or numerical entry in a column or
row filled or not. It can lookup in a vector that is non-adjacent and
non-parallel, which is very nice. LOOKUP allows you to look to the left of
the lookup vector and VLOOKUP does not, so one needs to use MATCH and OFFSET
or INDEX or INDIRECT. Not as nice a LOOKUP for this purpose.

--
Cheers,
Shane Devenshire


"Roger Govier" wrote:

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