Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Bob is offline
external usenet poster
 
Posts: 972
Default LOOKUP, MATCH, INDEX?

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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,202
Default LOOKUP, MATCH, INDEX?

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:


Column AC has 1001 values in it... column F has 2000 values in it... for any
given row in AC, where is the <=100 condition in F (what row) that control
whether the row in AV that corresponds to the given row gets a "Inc." or
not?

Rick

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default LOOKUP, MATCH, INDEX?

This is a "self-contained" formula, where no outside datalist is necessary,
since all values are included in the formula itself.

Enter this in AV1, and copy down as needed:

=IF(F1<=100,"inc",LOOKUP(AC1,{0,0.1,0.2,0.275,0.35 ,0.425,0.5,0.575,0.65,0.75;1,2,3,4,5,6,7,8,9,10}))

--

HTH,

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


"bob" wrote in message
...
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


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default LOOKUP, MATCH, INDEX?

If you would like to use a separate, outside datalist to shorten the
formula, say you enter the list in BA1 to BB10 as this:

BA BB

1 0.000 1
2 0.100 2
3 0.200 3
4 0.275 4
5 0.350 5
6 0.425 6
7 0.500 7
8 0.575 8
9 0.650 9
10 0.750 10


And then use a formula something like this:

=IF(F1<=100,"inc",LOOKUP(AC1,$BA$1:$BB$10))


--

HTH,

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

"RagDyeR" wrote in message
...
This is a "self-contained" formula, where no outside datalist is necessary,
since all values are included in the formula itself.

Enter this in AV1, and copy down as needed:

=IF(F1<=100,"inc",LOOKUP(AC1,{0,0.1,0.2,0.275,0.35 ,0.425,0.5,0.575,0.65,0.75;1,2,3,4,5,6,7,8,9,10}))

--

HTH,

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


"bob" wrote in message
...
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



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,344
Default LOOKUP, MATCH, INDEX?

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default LOOKUP, MATCH, INDEX?

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


  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default LOOKUP, MATCH, INDEX?

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




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,344
Default LOOKUP, MATCH, INDEX?

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





  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Bob is offline
external usenet poster
 
Posts: 972
Default LOOKUP, MATCH, INDEX?

This almost works. But it yields values that are 1 less than they should be.
Examples: .409 in column AC yields 4 instead of 5; .609 equals 7 instead of
8; and so on.

Thanks,
Bob

"ShaneDevenshire" wrote:

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

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default LOOKUP, MATCH, INDEX?

Which formula almost works?

Tip: a formula either works (returns the correct result) or it doesn't.
There is no "almost works"!!!!! <g

Create a 2 column table like this:

...........A..........B
1.....0.000......1
2.....0.100......2
3.....0.200......3
4.....0.275......4
5.....0.350......5
6.....0.425......6
7.....0.500......7
8.....0.575......8
9.....0.650......9
10...0.750......10

Then:

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

If F1 is *empty* it will return "Inc" since an empty cell evaluates to 0 and
0 <=100.

If any value in AC = 0.750 the result will be 10.

If any cell in AC is *empty* the result will be 1 since an empty cell
evaluates as 0. If you need to account for empty cells let us know.

Biff

"bob" wrote in message
...
This almost works. But it yields values that are 1 less than they should
be.
Examples: .409 in column AC yields 4 instead of 5; .609 equals 7 instead
of
8; and so on.

Thanks,
Bob

"ShaneDevenshire" wrote:

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





  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default LOOKUP, MATCH, INDEX?

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







  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,344
Default LOOKUP, MATCH, INDEX?

I was just clarifying why I said it does not work with multiple column ranges
and I still think my point is correct.

--
Cheers,
Shane Devenshire


"RagDyeR" wrote:

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







  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,344
Default LOOKUP, MATCH, INDEX?

Hi,

=IF(F1<=100,"Inc",VLOOKUP(AC1,BA1:BA10,2))

The formula is fine you just need to set up the lookup table as shown below:

0 1
0.099 2
0.199 3
0.274 4
0.349 5
0.424 6
0.499 7
0.574 8
0.649 9
0.749 10

--
Cheers,
Shane Devenshire


"bob" wrote:

This almost works. But it yields values that are 1 less than they should be.
Examples: .409 in column AC yields 4 instead of 5; .609 equals 7 instead of
8; and so on.

Thanks,
Bob

"ShaneDevenshire" wrote:

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

  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,572
Default LOOKUP, MATCH, INDEX?

Perhaps our disagreement here might be due strictly to semantics!

A datalist occupying A1 to D10 ... I would define as multi-columnar.

Finding a value in Column A, and returning a value from it's corresponding
row in Column D is possible using Lookup().

=Lookup("value",A1:D10)

Wouldn't you describe this as "working" with a multiple column range?

What terminology would you use to describe this?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"ShaneDevenshire" wrote in
message ...
I was just clarifying why I said it does not work with multiple column
ranges
and I still think my point is correct.

--
Cheers,
Shane Devenshire


"RagDyeR" wrote:

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









Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup/match/index Jules Excel Discussion (Misc queries) 1 July 5th 06 05:02 PM
Lookup or Index/Match Scorpvin Excel Discussion (Misc queries) 1 May 16th 06 07:35 PM
index?lookup?match?if? rduffey New Users to Excel 2 March 31st 06 03:32 PM
MATCH, INDEX, LOOKUP - Help! RobPot Excel Worksheet Functions 4 October 18th 05 04:33 PM
index / match /lookup ? help andrewm Excel Worksheet Functions 8 July 21st 05 02:55 PM


All times are GMT +1. The time now is 05:58 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"