Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup/match/index | Excel Discussion (Misc queries) | |||
Lookup or Index/Match | Excel Discussion (Misc queries) | |||
index?lookup?match?if? | New Users to Excel | |||
MATCH, INDEX, LOOKUP - Help! | Excel Worksheet Functions | |||
index / match /lookup ? help | Excel Worksheet Functions |