LOOKUP, MATCH, INDEX?

[bob]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[RagDyeR]

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!
=====================================================


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

 

[RagDyeR]

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!
=====================================================

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!
=====================================================


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

 

[ShaneDevenshire]

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!

 

[Ragdyer]

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?

 

[Roger Govier]

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.

 

[ShaneDevenshire]

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,G15,{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.

 

[bob]

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

 

[T. Valko]

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

 

[RagDyeR]

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


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,G15,{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.

 

[ShaneDevenshire]

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

 

[ShaneDevenshire]

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

 

[Ragdyer]

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",A110)

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

What terminology would you use to describe this?