LOOKUP, MATCH, INDEX?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bob - 02 Jun 2007 17:27 GMT

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 to this Message

Rick Rothstein (MVP - VB) - 02 Jun 2007 18:01 GMT

> 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.
[quoted text clipped - 4 lines]
>
> 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

Reply to this Message

RagDyeR - 02 Jun 2007 18:19 GMT

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}))

Signature

HTH,

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

Reply to this Message

RagDyeR - 02 Jun 2007 18:30 GMT

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))

Signature

HTH,

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

Signature

HTH,

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

Reply to this Message

ShaneDevenshire - 02 Jun 2007 23:08 GMT

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.274,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!

Signature

Cheers,
Shane Devenshire

> 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.
[quoted text clipped - 18 lines]
> thanks,
> Bob

Reply to this Message

Ragdyer - 03 Jun 2007 06:34 GMT

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?

Signature

Regards,

RD

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

> Hi,
>
[quoted text clipped - 8 lines]
>
> This formula can also be done as a stand alone formula:

=IF(F1<=100,"Inc",VLOOKUP(AC1,{0.099,1;0.199,2;0.274,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
[quoted text clipped - 24 lines]
> > thanks,
> > Bob

Reply to this Message

Roger Govier - 03 Jun 2007 08:13 GMT

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.

Signature

Regards

Roger Govier

>I don't quite agree with your statement that Lookup() cannot work on
> datalists of more then 2 columns.
[quoted text clipped - 59 lines]
>> > thanks,
>> > Bob

Reply to this Message

ShaneDevenshire - 03 Jun 2007 19:04 GMT

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.

Signature

Cheers,
Shane Devenshire

> Hi Rick
>
[quoted text clipped - 74 lines]
> >> > thanks,
> >> > Bob

Reply to this Message

RagDyeR - 04 Jun 2007 17:25 GMT

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.

Signature

Regards,

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

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.

Signature

Cheers,
Shane Devenshire

"Roger Govier" wrote:

> Hi Rick
>
[quoted text clipped - 73 lines]
> >> > thanks,
> >> > Bob

Reply to this Message

ShaneDevenshire - 05 Jun 2007 22:39 GMT

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

Signature

Cheers,
Shane Devenshire

> My comment was strictly directed at your statement that Lookup() was *not*
> an option for lookup tables of more then 2 columns!
[quoted text clipped - 116 lines]
> > >> > thanks,
> > >> > Bob

Reply to this Message

Ragdyer - 06 Jun 2007 04:26 GMT

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?

Signature

>I was just clarifying why I said it does not work with multiple column
>ranges
[quoted text clipped - 135 lines]
>> > >> > thanks,
>> > >> > Bob

Reply to this Message

bob - 03 Jun 2007 22:41 GMT

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

> Hi,
>
[quoted text clipped - 39 lines]
> > thanks,
> > Bob

Reply to this Message

T. Valko - 03 Jun 2007 23:13 GMT

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

> This almost works. But it yields values that are 1 less than they should
> be.
[quoted text clipped - 53 lines]
>> > thanks,
>> > Bob

Reply to this Message

ShaneDevenshire - 05 Jun 2007 23:06 GMT

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

Signature

Cheers,
Shane Devenshire

> 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
[quoted text clipped - 46 lines]
> > > thanks,
> > > Bob

Reply to this Message