Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP ( )
Hi,
=LOOKUP(2,1/A1:A100,A1:A100) The following is my interpretation of this formula. If I am wrong, please correct me. I read that there is vector form and array form for LOOKUP. This is vector form, right? Since the purpose is to find the last value in the array, there is no need to sort the array in ascending order. 1/A1:A100 is to reduce all values in the array to less than 1. If we use "2" as the lookup value, we won't find a match. As a result, we will be returned with the position of the last cell in the column that contains a non-blank and non-zero value. Then we use the position to lookup the "result vector" which in this case is the same as the "lookup vector." We don't necessarily have to use "2" as the lookup value; anything greater than 1 is fine e.g. 7, 50, 99 or even 1.5. Thanks for your help. Epinn |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP ( )
Did you mean to use this version?:
=LOOKUP(2,1/(A1:A100<""),A1:A100) In that formula, this section:(A1:A100<"") returns a series of 1's(for non-blanks) and 0's(for blanks)...or error values (for cells that contain errors) Consequently, the fraction 1/(A1:A100<"") returns 1's(for non-blanks) and errors for blanks or errors. Since there will be no 2's for the LOOKUP to find, it will match on the last non-error value (LOOKUP ignores errors in the lookup range) and return the corresponding value. That version has advantages over the formula you posted: =LOOKUP(2,1/A1:A100,A1:A100) Among them are these: If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. OR a last value of zero in the lookup range would be ignored because the resulting fraction would be an error. Does that help? *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Hi, =LOOKUP(2,1/A1:A100,A1:A100) The following is my interpretation of this formula. If I am wrong, please correct me. I read that there is vector form and array form for LOOKUP. This is vector form, right? Since the purpose is to find the last value in the array, there is no need to sort the array in ascending order. 1/A1:A100 is to reduce all values in the array to less than 1. If we use "2" as the lookup value, we won't find a match. As a result, we will be returned with the position of the last cell in the column that contains a non-blank and non-zero value. Then we use the position to lookup the "result vector" which in this case is the same as the "lookup vector." We don't necessarily have to use "2" as the lookup value; anything greater than 1 is fine e.g. 7, 50, 99 or even 1.5. Thanks for your help. Epinn |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP ( )
Ron,
Thank you for your response. I tried out both formulae before I posted. I used "evaluate formula" to watch the steps and I even tested with the last entry being blank or zero with each formula respectively. I thought I was pretty thorough and grasped everything and almost didn't want to post. I am glad I did. You pointed out something that I missed. I forgot 1/0.5 could return 2. More later. I picked up both formulae from another thread but I didn't want to "hijack" that thread, so I started my own. The user wanted to ignore zero, that was why the blank checking formula wasn't used. Would you prefer this formula if "0" is to be ignored? =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) a last value of zero in the lookup range would be ignored because the resulting fraction would be an error ...... "Evaluate formula" shows #DIV/0! and therefore ignored. If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. I included 0.5 in the array and I sorted it as well. However, the following formula did *NOT* find a match and still returned the last value. =LOOKUP(2,1/A1:A100,A1:A100) My interpretation is that for a match of "2" to happen, "2" itself has to be in the array. The result of a calculation being "2" won't cause a match to happen. But I am not comfortable with this and I haven't totally convinced myself. Can you or someone shed some light on this please? Is this a case similar to 1/COUNTIF? Am I confusing myself more here? Afterall, the following formula may be "safe" if we want to ignore zero. =LOOKUP(2,1/A1:A100,A1:A100) Epinn "Ron Coderre" wrote in message ... Did you mean to use this version?: =LOOKUP(2,1/(A1:A100<""),A1:A100) In that formula, this section:(A1:A100<"") returns a series of 1's(for non-blanks) and 0's(for blanks)...or error values (for cells that contain errors) Consequently, the fraction 1/(A1:A100<"") returns 1's(for non-blanks) and errors for blanks or errors. Since there will be no 2's for the LOOKUP to find, it will match on the last non-error value (LOOKUP ignores errors in the lookup range) and return the corresponding value. That version has advantages over the formula you posted: =LOOKUP(2,1/A1:A100,A1:A100) Among them are these: If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. OR a last value of zero in the lookup range would be ignored because the resulting fraction would be an error. Does that help? *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Hi, =LOOKUP(2,1/A1:A100,A1:A100) The following is my interpretation of this formula. If I am wrong, please correct me. I read that there is vector form and array form for LOOKUP. This is vector form, right? Since the purpose is to find the last value in the array, there is no need to sort the array in ascending order. 1/A1:A100 is to reduce all values in the array to less than 1. If we use "2" as the lookup value, we won't find a match. As a result, we will be returned with the position of the last cell in the column that contains a non-blank and non-zero value. Then we use the position to lookup the "result vector" which in this case is the same as the "lookup vector." We don't necessarily have to use "2" as the lookup value; anything greater than 1 is fine e.g. 7, 50, 99 or even 1.5. Thanks for your help. Epinn |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP ( )
This seems to work if you wanted to ignore both 0 and blanks
=LOOKUP(2,1/(A1:A100<0),A1:A100) "Epinn" wrote: Ron, Thank you for your response. I tried out both formulae before I posted. I used "evaluate formula" to watch the steps and I even tested with the last entry being blank or zero with each formula respectively. I thought I was pretty thorough and grasped everything and almost didn't want to post. I am glad I did. You pointed out something that I missed. I forgot 1/0.5 could return 2. More later. I picked up both formulae from another thread but I didn't want to "hijack" that thread, so I started my own. The user wanted to ignore zero, that was why the blank checking formula wasn't used. Would you prefer this formula if "0" is to be ignored? =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) a last value of zero in the lookup range would be ignored because the resulting fraction would be an error ...... "Evaluate formula" shows #DIV/0! and therefore ignored. If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. I included 0.5 in the array and I sorted it as well. However, the following formula did *NOT* find a match and still returned the last value. =LOOKUP(2,1/A1:A100,A1:A100) My interpretation is that for a match of "2" to happen, "2" itself has to be in the array. The result of a calculation being "2" won't cause a match to happen. But I am not comfortable with this and I haven't totally convinced myself. Can you or someone shed some light on this please? Is this a case similar to 1/COUNTIF? Am I confusing myself more here? Afterall, the following formula may be "safe" if we want to ignore zero. =LOOKUP(2,1/A1:A100,A1:A100) Epinn "Ron Coderre" wrote in message ... Did you mean to use this version?: =LOOKUP(2,1/(A1:A100<""),A1:A100) In that formula, this section:(A1:A100<"") returns a series of 1's(for non-blanks) and 0's(for blanks)...or error values (for cells that contain errors) Consequently, the fraction 1/(A1:A100<"") returns 1's(for non-blanks) and errors for blanks or errors. Since there will be no 2's for the LOOKUP to find, it will match on the last non-error value (LOOKUP ignores errors in the lookup range) and return the corresponding value. That version has advantages over the formula you posted: =LOOKUP(2,1/A1:A100,A1:A100) Among them are these: If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. OR a last value of zero in the lookup range would be ignored because the resulting fraction would be an error. Does that help? *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Hi, =LOOKUP(2,1/A1:A100,A1:A100) The following is my interpretation of this formula. If I am wrong, please correct me. I read that there is vector form and array form for LOOKUP. This is vector form, right? Since the purpose is to find the last value in the array, there is no need to sort the array in ascending order. 1/A1:A100 is to reduce all values in the array to less than 1. If we use "2" as the lookup value, we won't find a match. As a result, we will be returned with the position of the last cell in the column that contains a non-blank and non-zero value. Then we use the position to lookup the "result vector" which in this case is the same as the "lookup vector." We don't necessarily have to use "2" as the lookup value; anything greater than 1 is fine e.g. 7, 50, 99 or even 1.5. Thanks for your help. Epinn |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP ( )
Previously I wrote:
Afterall, the following formula may be "safe" if we want to ignore zero. =LOOKUP(2,1/A1:A100,A1:A100) I want to add ".... and text." This formula is only good when the array has *numbers only*. I can't think of a reason why (A) below has to be used instead of (B). If you do, please let me know. (A) =LOOKUP(2,1/(ISNUMBER(A1:A100)+ISTEXT(A1:A100)),A1:A100) (B) =LOOKUP(2,1/(A1:A100<""),A1:A100) Epinn "Epinn" wrote in message ... Ron, Thank you for your response. I tried out both formulae before I posted. I used "evaluate formula" to watch the steps and I even tested with the last entry being blank or zero with each formula respectively. I thought I was pretty thorough and grasped everything and almost didn't want to post. I am glad I did. You pointed out something that I missed. I forgot 1/0.5 could return 2. More later. I picked up both formulae from another thread but I didn't want to "hijack" that thread, so I started my own. The user wanted to ignore zero, that was why the blank checking formula wasn't used. Would you prefer this formula if "0" is to be ignored? =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) a last value of zero in the lookup range would be ignored because the resulting fraction would be an error ...... "Evaluate formula" shows #DIV/0! and therefore ignored. If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. I included 0.5 in the array and I sorted it as well. However, the following formula did *NOT* find a match and still returned the last value. =LOOKUP(2,1/A1:A100,A1:A100) My interpretation is that for a match of "2" to happen, "2" itself has to be in the array. The result of a calculation being "2" won't cause a match to happen. But I am not comfortable with this and I haven't totally convinced myself. Can you or someone shed some light on this please? Is this a case similar to 1/COUNTIF? Am I confusing myself more here? Afterall, the following formula may be "safe" if we want to ignore zero. =LOOKUP(2,1/A1:A100,A1:A100) Epinn "Ron Coderre" wrote in message ... Did you mean to use this version?: =LOOKUP(2,1/(A1:A100<""),A1:A100) In that formula, this section:(A1:A100<"") returns a series of 1's(for non-blanks) and 0's(for blanks)...or error values (for cells that contain errors) Consequently, the fraction 1/(A1:A100<"") returns 1's(for non-blanks) and errors for blanks or errors. Since there will be no 2's for the LOOKUP to find, it will match on the last non-error value (LOOKUP ignores errors in the lookup range) and return the corresponding value. That version has advantages over the formula you posted: =LOOKUP(2,1/A1:A100,A1:A100) Among them are these: If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. OR a last value of zero in the lookup range would be ignored because the resulting fraction would be an error. Does that help? *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Hi, =LOOKUP(2,1/A1:A100,A1:A100) The following is my interpretation of this formula. If I am wrong, please correct me. I read that there is vector form and array form for LOOKUP. This is vector form, right? Since the purpose is to find the last value in the array, there is no need to sort the array in ascending order. 1/A1:A100 is to reduce all values in the array to less than 1. If we use "2" as the lookup value, we won't find a match. As a result, we will be returned with the position of the last cell in the column that contains a non-blank and non-zero value. Then we use the position to lookup the "result vector" which in this case is the same as the "lookup vector." We don't necessarily have to use "2" as the lookup value; anything greater than 1 is fine e.g. 7, 50, 99 or even 1.5. Thanks for your help. Epinn |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP ( )
My interpretation is that for a match of "2" to happen, "2" itself has to
be in the array. The result of a calculation being "2" won't cause a match to happen. But I am not comfortable with this and I haven't totally convinced myself.<< Try using these values A1: 9 A2: 8 A3: 7 A4: 6 A5: 5 A6: 0.5 A7: 3 This formula: =LOOKUP(2,1/A1:A100,A1:A100) returns 0.5 Consequently, I'm not so comfortable with "may be safe". =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) always returns the last non-zero, non-blank, non-error value (assuming you don't want a final error value returned). Does that help? *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Previously I wrote: Afterall, the following formula may be "safe" if we want to ignore zero. =LOOKUP(2,1/A1:A100,A1:A100) I want to add ".... and text." This formula is only good when the array has *numbers only*. I can't think of a reason why (A) below has to be used instead of (B). If you do, please let me know. (A) =LOOKUP(2,1/(ISNUMBER(A1:A100)+ISTEXT(A1:A100)),A1:A100) (B) =LOOKUP(2,1/(A1:A100<""),A1:A100) Epinn "Epinn" wrote in message ... Ron, Thank you for your response. I tried out both formulae before I posted. I used "evaluate formula" to watch the steps and I even tested with the last entry being blank or zero with each formula respectively. I thought I was pretty thorough and grasped everything and almost didn't want to post. I am glad I did. You pointed out something that I missed. I forgot 1/0.5 could return 2. More later. I picked up both formulae from another thread but I didn't want to "hijack" that thread, so I started my own. The user wanted to ignore zero, that was why the blank checking formula wasn't used. Would you prefer this formula if "0" is to be ignored? =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) a last value of zero in the lookup range would be ignored because the resulting fraction would be an error ...... "Evaluate formula" shows #DIV/0! and therefore ignored. If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. I included 0.5 in the array and I sorted it as well. However, the following formula did *NOT* find a match and still returned the last value. =LOOKUP(2,1/A1:A100,A1:A100) My interpretation is that for a match of "2" to happen, "2" itself has to be in the array. The result of a calculation being "2" won't cause a match to happen. But I am not comfortable with this and I haven't totally convinced myself. Can you or someone shed some light on this please? Is this a case similar to 1/COUNTIF? Am I confusing myself more here? Afterall, the following formula may be "safe" if we want to ignore zero. =LOOKUP(2,1/A1:A100,A1:A100) Epinn "Ron Coderre" wrote in message ... Did you mean to use this version?: =LOOKUP(2,1/(A1:A100<""),A1:A100) In that formula, this section:(A1:A100<"") returns a series of 1's(for non-blanks) and 0's(for blanks)...or error values (for cells that contain errors) Consequently, the fraction 1/(A1:A100<"") returns 1's(for non-blanks) and errors for blanks or errors. Since there will be no 2's for the LOOKUP to find, it will match on the last non-error value (LOOKUP ignores errors in the lookup range) and return the corresponding value. That version has advantages over the formula you posted: =LOOKUP(2,1/A1:A100,A1:A100) Among them are these: If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. OR a last value of zero in the lookup range would be ignored because the resulting fraction would be an error. Does that help? *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Hi, =LOOKUP(2,1/A1:A100,A1:A100) The following is my interpretation of this formula. If I am wrong, please correct me. I read that there is vector form and array form for LOOKUP. This is vector form, right? Since the purpose is to find the last value in the array, there is no need to sort the array in ascending order. 1/A1:A100 is to reduce all values in the array to less than 1. If we use "2" as the lookup value, we won't find a match. As a result, we will be returned with the position of the last cell in the column that contains a non-blank and non-zero value. Then we use the position to lookup the "result vector" which in this case is the same as the "lookup vector." We don't necessarily have to use "2" as the lookup value; anything greater than 1 is fine e.g. 7, 50, 99 or even 1.5. Thanks for your help. Epinn |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP ( )
Ron, Guess what, *before* I read your post I tested "0.5" some more. I did pick up 0.5 even though it is *not* the last value. The difference between this time and last time is I sorted the array in ascending order last time but no order this time. Looks like this is temperamental. Anyway, I agree with you that it may not be "safe" to use =LOOKUP(2,1/A1:A100,A1:A100) =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) always returns the last non-zero, non-blank, non-error value (assuming you don't want a final error value returned). << None of the formulae that we have discussed will return error values (e.g. #DIV/0!); so I won't worry about it. JMB is right about checking for zero will take care of ignoring blanks as well. Thanks, JMB. I have done some more testing and have come to the following conclusion. Please correct me if I am wrong. =LOOKUP(2,1/(A1:A10<""),A1:A10) ignore blank but not 0, not text =LOOKUP(2,1/(A1:A10<0),A1:A10) ignore blank and zero but not text =LOOKUP(2,1/(ISNUMBER(A1:A10)),A1:A10) ignore blank and text but not 0 =LOOKUP(2,1/(ISTEXT(A1:A10)),A1:A10) ignore blank, zero, numbers but not text I am quite satisfied now unless someone tells me I am wrong on the above. I can include more conditions like ignoring blank, zero and text, but I want to give my brain a rest. Thank you very much for your guidance, Ron. I am glad that I posted. Epinn "Ron Coderre" wrote in message ... My interpretation is that for a match of "2" to happen, "2" itself has to be in the array. The result of a calculation being "2" won't cause a match to happen. But I am not comfortable with this and I haven't totally convinced myself.<< Try using these values A1: 9 A2: 8 A3: 7 A4: 6 A5: 5 A6: 0.5 A7: 3 This formula: =LOOKUP(2,1/A1:A100,A1:A100) returns 0.5 Consequently, I'm not so comfortable with "may be safe". =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) always returns the last non-zero, non-blank, non-error value (assuming you don't want a final error value returned). Does that help? *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Previously I wrote: Afterall, the following formula may be "safe" if we want to ignore zero. =LOOKUP(2,1/A1:A100,A1:A100) I want to add ".... and text." This formula is only good when the array has *numbers only*. I can't think of a reason why (A) below has to be used instead of (B). If you do, please let me know. (A) =LOOKUP(2,1/(ISNUMBER(A1:A100)+ISTEXT(A1:A100)),A1:A100) (B) =LOOKUP(2,1/(A1:A100<""),A1:A100) Epinn "Epinn" wrote in message ... Ron, Thank you for your response. I tried out both formulae before I posted. I used "evaluate formula" to watch the steps and I even tested with the last entry being blank or zero with each formula respectively. I thought I was pretty thorough and grasped everything and almost didn't want to post. I am glad I did. You pointed out something that I missed. I forgot 1/0.5 could return 2. More later. I picked up both formulae from another thread but I didn't want to "hijack" that thread, so I started my own. The user wanted to ignore zero, that was why the blank checking formula wasn't used. Would you prefer this formula if "0" is to be ignored? =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) a last value of zero in the lookup range would be ignored because the resulting fraction would be an error ...... "Evaluate formula" shows #DIV/0! and therefore ignored. If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. I included 0.5 in the array and I sorted it as well. However, the following formula did *NOT* find a match and still returned the last value. =LOOKUP(2,1/A1:A100,A1:A100) My interpretation is that for a match of "2" to happen, "2" itself has to be in the array. The result of a calculation being "2" won't cause a match to happen. But I am not comfortable with this and I haven't totally convinced myself. Can you or someone shed some light on this please? Is this a case similar to 1/COUNTIF? Am I confusing myself more here? Afterall, the following formula may be "safe" if we want to ignore zero. =LOOKUP(2,1/A1:A100,A1:A100) Epinn "Ron Coderre" wrote in message ... Did you mean to use this version?: =LOOKUP(2,1/(A1:A100<""),A1:A100) In that formula, this section:(A1:A100<"") returns a series of 1's(for non-blanks) and 0's(for blanks)...or error values (for cells that contain errors) Consequently, the fraction 1/(A1:A100<"") returns 1's(for non-blanks) and errors for blanks or errors. Since there will be no 2's for the LOOKUP to find, it will match on the last non-error value (LOOKUP ignores errors in the lookup range) and return the corresponding value. That version has advantages over the formula you posted: =LOOKUP(2,1/A1:A100,A1:A100) Among them are these: If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. OR a last value of zero in the lookup range would be ignored because the resulting fraction would be an error. Does that help? *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Hi, =LOOKUP(2,1/A1:A100,A1:A100) The following is my interpretation of this formula. If I am wrong, please correct me. I read that there is vector form and array form for LOOKUP. This is vector form, right? Since the purpose is to find the last value in the array, there is no need to sort the array in ascending order. 1/A1:A100 is to reduce all values in the array to less than 1. If we use "2" as the lookup value, we won't find a match. As a result, we will be returned with the position of the last cell in the column that contains a non-blank and non-zero value. Then we use the position to lookup the "result vector" which in this case is the same as the "lookup vector." We don't necessarily have to use "2" as the lookup value; anything greater than 1 is fine e.g. 7, 50, 99 or even 1.5. Thanks for your help. Epinn |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP ( )
Don't forget the old standbys:
The value of the last numeric cell in Col_A: =LOOKUP(10^99,A:A) The value of the last text cell in Col_A =LOOKUP(REPT("z",255),A:A) *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Ron, Guess what, *before* I read your post I tested "0.5" some more. I did pick up 0.5 even though it is *not* the last value. The difference between this time and last time is I sorted the array in ascending order last time but no order this time. Looks like this is temperamental. Anyway, I agree with you that it may not be "safe" to use =LOOKUP(2,1/A1:A100,A1:A100) =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) always returns the last non-zero, non-blank, non-error value (assuming you don't want a final error value returned). << None of the formulae that we have discussed will return error values (e.g. #DIV/0!); so I won't worry about it. JMB is right about checking for zero will take care of ignoring blanks as well. Thanks, JMB. I have done some more testing and have come to the following conclusion. Please correct me if I am wrong. =LOOKUP(2,1/(A1:A10<""),A1:A10) ignore blank but not 0, not text =LOOKUP(2,1/(A1:A10<0),A1:A10) ignore blank and zero but not text =LOOKUP(2,1/(ISNUMBER(A1:A10)),A1:A10) ignore blank and text but not 0 =LOOKUP(2,1/(ISTEXT(A1:A10)),A1:A10) ignore blank, zero, numbers but not text I am quite satisfied now unless someone tells me I am wrong on the above. I can include more conditions like ignoring blank, zero and text, but I want to give my brain a rest. Thank you very much for your guidance, Ron. I am glad that I posted. Epinn "Ron Coderre" wrote in message ... My interpretation is that for a match of "2" to happen, "2" itself has to be in the array. The result of a calculation being "2" won't cause a match to happen. But I am not comfortable with this and I haven't totally convinced myself.<< Try using these values A1: 9 A2: 8 A3: 7 A4: 6 A5: 5 A6: 0.5 A7: 3 This formula: =LOOKUP(2,1/A1:A100,A1:A100) returns 0.5 Consequently, I'm not so comfortable with "may be safe". =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) always returns the last non-zero, non-blank, non-error value (assuming you don't want a final error value returned). Does that help? *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Previously I wrote: Afterall, the following formula may be "safe" if we want to ignore zero. =LOOKUP(2,1/A1:A100,A1:A100) I want to add ".... and text." This formula is only good when the array has *numbers only*. I can't think of a reason why (A) below has to be used instead of (B). If you do, please let me know. (A) =LOOKUP(2,1/(ISNUMBER(A1:A100)+ISTEXT(A1:A100)),A1:A100) (B) =LOOKUP(2,1/(A1:A100<""),A1:A100) Epinn "Epinn" wrote in message ... Ron, Thank you for your response. I tried out both formulae before I posted. I used "evaluate formula" to watch the steps and I even tested with the last entry being blank or zero with each formula respectively. I thought I was pretty thorough and grasped everything and almost didn't want to post. I am glad I did. You pointed out something that I missed. I forgot 1/0.5 could return 2. More later. I picked up both formulae from another thread but I didn't want to "hijack" that thread, so I started my own. The user wanted to ignore zero, that was why the blank checking formula wasn't used. Would you prefer this formula if "0" is to be ignored? =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) a last value of zero in the lookup range would be ignored because the resulting fraction would be an error ...... "Evaluate formula" shows #DIV/0! and therefore ignored. If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. I included 0.5 in the array and I sorted it as well. However, the following formula did *NOT* find a match and still returned the last value. =LOOKUP(2,1/A1:A100,A1:A100) My interpretation is that for a match of "2" to happen, "2" itself has to be in the array. The result of a calculation being "2" won't cause a match to happen. But I am not comfortable with this and I haven't totally convinced myself. Can you or someone shed some light on this please? Is this a case similar to 1/COUNTIF? Am I confusing myself more here? Afterall, the following formula may be "safe" if we want to ignore zero. =LOOKUP(2,1/A1:A100,A1:A100) Epinn "Ron Coderre" wrote in message ... Did you mean to use this version?: =LOOKUP(2,1/(A1:A100<""),A1:A100) In that formula, this section:(A1:A100<"") returns a series of 1's(for non-blanks) and 0's(for blanks)...or error values (for cells that contain errors) Consequently, the fraction 1/(A1:A100<"") returns 1's(for non-blanks) and errors for blanks or errors. Since there will be no 2's for the LOOKUP to find, it will match on the last non-error value (LOOKUP ignores errors in the lookup range) and return the corresponding value. That version has advantages over the formula you posted: =LOOKUP(2,1/A1:A100,A1:A100) Among them are these: If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. OR a last value of zero in the lookup range would be ignored because the resulting fraction would be an error. Does that help? *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Hi, =LOOKUP(2,1/A1:A100,A1:A100) The following is my interpretation of this formula. If I am wrong, please correct me. I read that there is vector form and array form for LOOKUP. This is vector form, right? Since the purpose is to find the last value in the array, there is no need to sort the array in ascending order. 1/A1:A100 is to reduce all values in the array to less than 1. If we use "2" as the lookup value, we won't find a match. As a result, we will be returned with the position of the last cell in the column that contains a non-blank and non-zero value. Then we use the position to lookup the "result vector" which in this case is the same as the "lookup vector." We don't necessarily have to use "2" as the lookup value; anything greater than 1 is fine e.g. 7, 50, 99 or even 1.5. Thanks for your help. Epinn |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP ( )
Ron,
Thank you for the bonus which I always like. Previously, I spotted =LOOKUP(REPT("z",255),A:A) in a 2004 thread and I have almost forgotten about it because I am so focused on this thread. Thank you for reminding me. I also found the following :- =LOOKUP(9.99999999999999E+307,C:C) I don't understand it but I understand yours. So, I'll use =LOOKUP(10^99,A:A). I want to throw in two more formulae to determine the position ...... =MATCH(REPT("z",255),A:A) =MATCH(10^99,A:A) With the bonus formulae, I have discovered that I can't use A:A with the four formulae in my previous post because they return an error. But then if I specify A100 as the last cell of the array and even if it is blank, it will still be okay. I don't understand but I may want to let it go unless someone wants to shed some light on this. Thanks again. Epinn "Ron Coderre" wrote in message ... Don't forget the old standbys: The value of the last numeric cell in Col_A: =LOOKUP(10^99,A:A) The value of the last text cell in Col_A =LOOKUP(REPT("z",255),A:A) *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Ron, Guess what, *before* I read your post I tested "0.5" some more. I did pick up 0.5 even though it is *not* the last value. The difference between this time and last time is I sorted the array in ascending order last time but no order this time. Looks like this is temperamental. Anyway, I agree with you that it may not be "safe" to use =LOOKUP(2,1/A1:A100,A1:A100) =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) always returns the last non-zero, non-blank, non-error value (assuming you don't want a final error value returned). << None of the formulae that we have discussed will return error values (e.g. #DIV/0!); so I won't worry about it. JMB is right about checking for zero will take care of ignoring blanks as well. Thanks, JMB. I have done some more testing and have come to the following conclusion. Please correct me if I am wrong. =LOOKUP(2,1/(A1:A10<""),A1:A10) ignore blank but not 0, not text =LOOKUP(2,1/(A1:A10<0),A1:A10) ignore blank and zero but not text =LOOKUP(2,1/(ISNUMBER(A1:A10)),A1:A10) ignore blank and text but not 0 =LOOKUP(2,1/(ISTEXT(A1:A10)),A1:A10) ignore blank, zero, numbers but not text I am quite satisfied now unless someone tells me I am wrong on the above. I can include more conditions like ignoring blank, zero and text, but I want to give my brain a rest. Thank you very much for your guidance, Ron. I am glad that I posted. Epinn "Ron Coderre" wrote in message ... My interpretation is that for a match of "2" to happen, "2" itself has to be in the array. The result of a calculation being "2" won't cause a match to happen. But I am not comfortable with this and I haven't totally convinced myself.<< Try using these values A1: 9 A2: 8 A3: 7 A4: 6 A5: 5 A6: 0.5 A7: 3 This formula: =LOOKUP(2,1/A1:A100,A1:A100) returns 0.5 Consequently, I'm not so comfortable with "may be safe". =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) always returns the last non-zero, non-blank, non-error value (assuming you don't want a final error value returned). Does that help? *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Previously I wrote: Afterall, the following formula may be "safe" if we want to ignore zero. =LOOKUP(2,1/A1:A100,A1:A100) I want to add ".... and text." This formula is only good when the array has *numbers only*. I can't think of a reason why (A) below has to be used instead of (B). If you do, please let me know. (A) =LOOKUP(2,1/(ISNUMBER(A1:A100)+ISTEXT(A1:A100)),A1:A100) (B) =LOOKUP(2,1/(A1:A100<""),A1:A100) Epinn "Epinn" wrote in message ... Ron, Thank you for your response. I tried out both formulae before I posted. I used "evaluate formula" to watch the steps and I even tested with the last entry being blank or zero with each formula respectively. I thought I was pretty thorough and grasped everything and almost didn't want to post. I am glad I did. You pointed out something that I missed. I forgot 1/0.5 could return 2. More later. I picked up both formulae from another thread but I didn't want to "hijack" that thread, so I started my own. The user wanted to ignore zero, that was why the blank checking formula wasn't used. Would you prefer this formula if "0" is to be ignored? =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) a last value of zero in the lookup range would be ignored because the resulting fraction would be an error ...... "Evaluate formula" shows #DIV/0! and therefore ignored. If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. I included 0.5 in the array and I sorted it as well. However, the following formula did *NOT* find a match and still returned the last value. =LOOKUP(2,1/A1:A100,A1:A100) My interpretation is that for a match of "2" to happen, "2" itself has to be in the array. The result of a calculation being "2" won't cause a match to happen. But I am not comfortable with this and I haven't totally convinced myself. Can you or someone shed some light on this please? Is this a case similar to 1/COUNTIF? Am I confusing myself more here? Afterall, the following formula may be "safe" if we want to ignore zero. =LOOKUP(2,1/A1:A100,A1:A100) Epinn "Ron Coderre" wrote in message ... Did you mean to use this version?: =LOOKUP(2,1/(A1:A100<""),A1:A100) In that formula, this section:(A1:A100<"") returns a series of 1's(for non-blanks) and 0's(for blanks)...or error values (for cells that contain errors) Consequently, the fraction 1/(A1:A100<"") returns 1's(for non-blanks) and errors for blanks or errors. Since there will be no 2's for the LOOKUP to find, it will match on the last non-error value (LOOKUP ignores errors in the lookup range) and return the corresponding value. That version has advantages over the formula you posted: =LOOKUP(2,1/A1:A100,A1:A100) Among them are these: If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. OR a last value of zero in the lookup range would be ignored because the resulting fraction would be an error. Does that help? *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Hi, =LOOKUP(2,1/A1:A100,A1:A100) The following is my interpretation of this formula. If I am wrong, please correct me. I read that there is vector form and array form for LOOKUP. This is vector form, right? Since the purpose is to find the last value in the array, there is no need to sort the array in ascending order. 1/A1:A100 is to reduce all values in the array to less than 1. If we use "2" as the lookup value, we won't find a match. As a result, we will be returned with the position of the last cell in the column that contains a non-blank and non-zero value. Then we use the position to lookup the "result vector" which in this case is the same as the "lookup vector." We don't necessarily have to use "2" as the lookup value; anything greater than 1 is fine e.g. 7, 50, 99 or even 1.5. Thanks for your help. Epinn |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP ( )
Epinn,
9.99999999999999E+307 is the largest number that Excel can represent. However, you can use a smaller number as long as it is greater than the largest number you expect in the data set, so 10^99 will cover most eventualities. If I want to do something like this for dates, I know that as long as the number is greater than about 40000, then this will also work (so, 99999 will do). Hope this sheds a bit more light. Pete Epinn wrote: Ron, Thank you for the bonus which I always like. Previously, I spotted =LOOKUP(REPT("z",255),A:A) in a 2004 thread and I have almost forgotten about it because I am so focused on this thread. Thank you for reminding me. I also found the following :- =LOOKUP(9.99999999999999E+307,C:C) I don't understand it but I understand yours. So, I'll use =LOOKUP(10^99,A:A). I want to throw in two more formulae to determine the position ...... =MATCH(REPT("z",255),A:A) =MATCH(10^99,A:A) With the bonus formulae, I have discovered that I can't use A:A with the four formulae in my previous post because they return an error. But then if I specify A100 as the last cell of the array and even if it is blank, it will still be okay. I don't understand but I may want to let it go unless someone wants to shed some light on this. Thanks again. Epinn "Ron Coderre" wrote in message ... Don't forget the old standbys: The value of the last numeric cell in Col_A: =LOOKUP(10^99,A:A) The value of the last text cell in Col_A =LOOKUP(REPT("z",255),A:A) *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Ron, Guess what, *before* I read your post I tested "0.5" some more. I did pick up 0.5 even though it is *not* the last value. The difference between this time and last time is I sorted the array in ascending order last time but no order this time. Looks like this is temperamental. Anyway, I agree with you that it may not be "safe" to use =LOOKUP(2,1/A1:A100,A1:A100) =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) always returns the last non-zero, non-blank, non-error value (assuming you don't want a final error value returned). << None of the formulae that we have discussed will return error values (e.g. #DIV/0!); so I won't worry about it. JMB is right about checking for zero will take care of ignoring blanks as well. Thanks, JMB. I have done some more testing and have come to the following conclusion. Please correct me if I am wrong. =LOOKUP(2,1/(A1:A10<""),A1:A10) ignore blank but not 0, not text =LOOKUP(2,1/(A1:A10<0),A1:A10) ignore blank and zero but not text =LOOKUP(2,1/(ISNUMBER(A1:A10)),A1:A10) ignore blank and text but not 0 =LOOKUP(2,1/(ISTEXT(A1:A10)),A1:A10) ignore blank, zero, numbers but not text I am quite satisfied now unless someone tells me I am wrong on the above. I can include more conditions like ignoring blank, zero and text, but I want to give my brain a rest. Thank you very much for your guidance, Ron. I am glad that I posted. Epinn "Ron Coderre" wrote in message ... My interpretation is that for a match of "2" to happen, "2" itself has to be in the array. The result of a calculation being "2" won't cause a match to happen. But I am not comfortable with this and I haven't totally convinced myself.<< Try using these values A1: 9 A2: 8 A3: 7 A4: 6 A5: 5 A6: 0.5 A7: 3 This formula: =LOOKUP(2,1/A1:A100,A1:A100) returns 0.5 Consequently, I'm not so comfortable with "may be safe". =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) always returns the last non-zero, non-blank, non-error value (assuming you don't want a final error value returned). Does that help? *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Previously I wrote: Afterall, the following formula may be "safe" if we want to ignore zero. =LOOKUP(2,1/A1:A100,A1:A100) I want to add ".... and text." This formula is only good when the array has *numbers only*. I can't think of a reason why (A) below has to be used instead of (B). If you do, please let me know. (A) =LOOKUP(2,1/(ISNUMBER(A1:A100)+ISTEXT(A1:A100)),A1:A100) (B) =LOOKUP(2,1/(A1:A100<""),A1:A100) Epinn "Epinn" wrote in message ... Ron, Thank you for your response. I tried out both formulae before I posted. I used "evaluate formula" to watch the steps and I even tested with the last entry being blank or zero with each formula respectively. I thought I was pretty thorough and grasped everything and almost didn't want to post. I am glad I did. You pointed out something that I missed. I forgot 1/0.5 could return 2. More later. I picked up both formulae from another thread but I didn't want to "hijack" that thread, so I started my own. The user wanted to ignore zero, that was why the blank checking formula wasn't used. Would you prefer this formula if "0" is to be ignored? =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) a last value of zero in the lookup range would be ignored because the resulting fraction would be an error ...... "Evaluate formula" shows #DIV/0! and therefore ignored. If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. I included 0.5 in the array and I sorted it as well. However, the following formula did *NOT* find a match and still returned the last value. =LOOKUP(2,1/A1:A100,A1:A100) My interpretation is that for a match of "2" to happen, "2" itself has to be in the array. The result of a calculation being "2" won't cause a match to happen. But I am not comfortable with this and I haven't totally convinced myself. Can you or someone shed some light on this please? Is this a case similar to 1/COUNTIF? Am I confusing myself more here? Afterall, the following formula may be "safe" if we want to ignore zero. =LOOKUP(2,1/A1:A100,A1:A100) Epinn "Ron Coderre" wrote in message ... Did you mean to use this version?: =LOOKUP(2,1/(A1:A100<""),A1:A100) In that formula, this section:(A1:A100<"") returns a series of 1's(for non-blanks) and 0's(for blanks)...or error values (for cells that contain errors) Consequently, the fraction 1/(A1:A100<"") returns 1's(for non-blanks) and errors for blanks or errors. Since there will be no 2's for the LOOKUP to find, it will match on the last non-error value (LOOKUP ignores errors in the lookup range) and return the corresponding value. That version has advantages over the formula you posted: =LOOKUP(2,1/A1:A100,A1:A100) Among them are these: If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. OR a last value of zero in the lookup range would be ignored because the resulting fraction would be an error. Does that help? *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Hi, =LOOKUP(2,1/A1:A100,A1:A100) The following is my interpretation of this formula. If I am wrong, please correct me. I read that there is vector form and array form for LOOKUP. This is vector form, right? Since the purpose is to find the last value in the array, there is no need to sort the array in ascending order. 1/A1:A100 is to reduce all values in the array to less than 1. If we use "2" as the lookup value, we won't find a match. As a result, we will be returned with the position of the last cell in the column that contains a non-blank and non-zero value. Then we use the position to lookup the "result vector" which in this case is the same as the "lookup vector." We don't necessarily have to use "2" as the lookup value; anything greater than 1 is fine e.g. 7, 50, 99 or even 1.5. Thanks for your help. Epinn |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
LOOKUP ( )
All right, I have another bonus - 99999 for dates.
Thanks, Pete. Epinn "Pete_UK" wrote in message oups.com... Epinn, 9.99999999999999E+307 is the largest number that Excel can represent. However, you can use a smaller number as long as it is greater than the largest number you expect in the data set, so 10^99 will cover most eventualities. If I want to do something like this for dates, I know that as long as the number is greater than about 40000, then this will also work (so, 99999 will do). Hope this sheds a bit more light. Pete Epinn wrote: Ron, Thank you for the bonus which I always like. Previously, I spotted =LOOKUP(REPT("z",255),A:A) in a 2004 thread and I have almost forgotten about it because I am so focused on this thread. Thank you for reminding me. I also found the following :- =LOOKUP(9.99999999999999E+307,C:C) I don't understand it but I understand yours. So, I'll use =LOOKUP(10^99,A:A). I want to throw in two more formulae to determine the position ...... =MATCH(REPT("z",255),A:A) =MATCH(10^99,A:A) With the bonus formulae, I have discovered that I can't use A:A with the four formulae in my previous post because they return an error. But then if I specify A100 as the last cell of the array and even if it is blank, it will still be okay. I don't understand but I may want to let it go unless someone wants to shed some light on this. Thanks again. Epinn "Ron Coderre" wrote in message ... Don't forget the old standbys: The value of the last numeric cell in Col_A: =LOOKUP(10^99,A:A) The value of the last text cell in Col_A =LOOKUP(REPT("z",255),A:A) *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Ron, Guess what, *before* I read your post I tested "0.5" some more. I did pick up 0.5 even though it is *not* the last value. The difference between this time and last time is I sorted the array in ascending order last time but no order this time. Looks like this is temperamental. Anyway, I agree with you that it may not be "safe" to use =LOOKUP(2,1/A1:A100,A1:A100) =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) always returns the last non-zero, non-blank, non-error value (assuming you don't want a final error value returned). << None of the formulae that we have discussed will return error values (e.g. #DIV/0!); so I won't worry about it. JMB is right about checking for zero will take care of ignoring blanks as well. Thanks, JMB. I have done some more testing and have come to the following conclusion. Please correct me if I am wrong. =LOOKUP(2,1/(A1:A10<""),A1:A10) ignore blank but not 0, not text =LOOKUP(2,1/(A1:A10<0),A1:A10) ignore blank and zero but not text =LOOKUP(2,1/(ISNUMBER(A1:A10)),A1:A10) ignore blank and text but not 0 =LOOKUP(2,1/(ISTEXT(A1:A10)),A1:A10) ignore blank, zero, numbers but not text I am quite satisfied now unless someone tells me I am wrong on the above. I can include more conditions like ignoring blank, zero and text, but I want to give my brain a rest. Thank you very much for your guidance, Ron. I am glad that I posted. Epinn "Ron Coderre" wrote in message ... My interpretation is that for a match of "2" to happen, "2" itself has to be in the array. The result of a calculation being "2" won't cause a match to happen. But I am not comfortable with this and I haven't totally convinced myself.<< Try using these values A1: 9 A2: 8 A3: 7 A4: 6 A5: 5 A6: 0.5 A7: 3 This formula: =LOOKUP(2,1/A1:A100,A1:A100) returns 0.5 Consequently, I'm not so comfortable with "may be safe". =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) always returns the last non-zero, non-blank, non-error value (assuming you don't want a final error value returned). Does that help? *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Previously I wrote: Afterall, the following formula may be "safe" if we want to ignore zero. =LOOKUP(2,1/A1:A100,A1:A100) I want to add ".... and text." This formula is only good when the array has *numbers only*. I can't think of a reason why (A) below has to be used instead of (B). If you do, please let me know. (A) =LOOKUP(2,1/(ISNUMBER(A1:A100)+ISTEXT(A1:A100)),A1:A100) (B) =LOOKUP(2,1/(A1:A100<""),A1:A100) Epinn "Epinn" wrote in message ... Ron, Thank you for your response. I tried out both formulae before I posted. I used "evaluate formula" to watch the steps and I even tested with the last entry being blank or zero with each formula respectively. I thought I was pretty thorough and grasped everything and almost didn't want to post. I am glad I did. You pointed out something that I missed. I forgot 1/0.5 could return 2. More later. I picked up both formulae from another thread but I didn't want to "hijack" that thread, so I started my own. The user wanted to ignore zero, that was why the blank checking formula wasn't used. Would you prefer this formula if "0" is to be ignored? =LOOKUP(2,1/((A1:A100<"")*(A1:A100<0)),A1:A100) a last value of zero in the lookup range would be ignored because the resulting fraction would be an error ...... "Evaluate formula" shows #DIV/0! and therefore ignored. If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. I included 0.5 in the array and I sorted it as well. However, the following formula did *NOT* find a match and still returned the last value. =LOOKUP(2,1/A1:A100,A1:A100) My interpretation is that for a match of "2" to happen, "2" itself has to be in the array. The result of a calculation being "2" won't cause a match to happen. But I am not comfortable with this and I haven't totally convinced myself. Can you or someone shed some light on this please? Is this a case similar to 1/COUNTIF? Am I confusing myself more here? Afterall, the following formula may be "safe" if we want to ignore zero. =LOOKUP(2,1/A1:A100,A1:A100) Epinn "Ron Coderre" wrote in message ... Did you mean to use this version?: =LOOKUP(2,1/(A1:A100<""),A1:A100) In that formula, this section:(A1:A100<"") returns a series of 1's(for non-blanks) and 0's(for blanks)...or error values (for cells that contain errors) Consequently, the fraction 1/(A1:A100<"") returns 1's(for non-blanks) and errors for blanks or errors. Since there will be no 2's for the LOOKUP to find, it will match on the last non-error value (LOOKUP ignores errors in the lookup range) and return the corresponding value. That version has advantages over the formula you posted: =LOOKUP(2,1/A1:A100,A1:A100) Among them are these: If the lookup range is in the right order and happens to contain 0.5 in the right place, 1/0.5 would calculate to 2, resulting in a match. OR a last value of zero in the lookup range would be ignored because the resulting fraction would be an error. Does that help? *********** Regards, Ron XL2002, WinXP "Epinn" wrote: Hi, =LOOKUP(2,1/A1:A100,A1:A100) The following is my interpretation of this formula. If I am wrong, please correct me. I read that there is vector form and array form for LOOKUP. This is vector form, right? Since the purpose is to find the last value in the array, there is no need to sort the array in ascending order. 1/A1:A100 is to reduce all values in the array to less than 1. If we use "2" as the lookup value, we won't find a match. As a result, we will be returned with the position of the last cell in the column that contains a non-blank and non-zero value. Then we use the position to lookup the "result vector" which in this case is the same as the "lookup vector." We don't necessarily have to use "2" as the lookup value; anything greater than 1 is fine e.g. 7, 50, 99 or even 1.5. Thanks for your help. Epinn |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup function gives wrong values occasionally | Excel Discussion (Misc queries) | |||
Another way to lookup data | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |