| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Index/Match (maybe?)
Excel2003 ...
Range B4:B100 ... Contains random non-repeating "Text" numbers sorted in "Asc" order. Range D4:D100 ... Contains empty cells & random Dates (mm/dd/yy) Need formula in Cell D3 to return Col B value found against "last" date (not latest date) ie: Col B ... Col D 10 .... 04/22/08 20 .... 06/25/08 35 .... 45 .... 50 .... 05/01/08 ... (need 50 to be returned) 62 .... Etc Thanks ... Kha |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match (maybe?)
Try this:
=LOOKUP(1E100,D4:D100,B4:B100) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Excel2003 ... Range B4:B100 ... Contains random non-repeating "Text" numbers sorted in "Asc" order. Range D4:D100 ... Contains empty cells & random Dates (mm/dd/yy) Need formula in Cell D3 to return Col B value found against "last" date (not latest date) ie: Col B ... Col D 10 .... 04/22/08 20 .... 06/25/08 35 .... 45 .... 50 .... 05/01/08 ... (need 50 to be returned) 62 .... Etc Thanks ... Kha |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match (maybe?)
T.
The Excel solutions you provide here are extremely helpul ... I stalled on the -1E100 - piece of your formula, but then remembered that "dates" are really numbers. Thank you for supporting these boards ... Your many solutions are greatly appreciated ... Kha "T. Valko" wrote: Try this: =LOOKUP(1E100,D4:D100,B4:B100) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Excel2003 ... Range B4:B100 ... Contains random non-repeating "Text" numbers sorted in "Asc" order. Range D4:D100 ... Contains empty cells & random Dates (mm/dd/yy) Need formula in Cell D3 to return Col B value found against "last" date (not latest date) ie: Col B ... Col D 10 .... 04/22/08 20 .... 06/25/08 35 .... 45 .... 50 .... 05/01/08 ... (need 50 to be returned) 62 .... Etc Thanks ... Kha |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match (maybe?)
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Ken" wrote in message ... T. The Excel solutions you provide here are extremely helpul ... I stalled on the -1E100 - piece of your formula, but then remembered that "dates" are really numbers. Thank you for supporting these boards ... Your many solutions are greatly appreciated ... Kha "T. Valko" wrote: Try this: =LOOKUP(1E100,D4:D100,B4:B100) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Excel2003 ... Range B4:B100 ... Contains random non-repeating "Text" numbers sorted in "Asc" order. Range D4:D100 ... Contains empty cells & random Dates (mm/dd/yy) Need formula in Cell D3 to return Col B value found against "last" date (not latest date) ie: Col B ... Col D 10 .... 04/22/08 20 .... 06/25/08 35 .... 45 .... 50 .... 05/01/08 ... (need 50 to be returned) 62 .... Etc Thanks ... Kha |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match (maybe?)
T ... Formula does exactly as requested & seems simple enough ... However, I
spent some time in the HelpScreen for LOOKUP & I am still at a loss for how the "IE100" part of this formula works??? =Lookup(1E100,D4:D100,B4:B100) I am seeing ... Lookup 1E100 (a large #) in Col D, Return value from same row Col B. Thing is ... the large # can not be found, so I thought Lookup would return next highest value ... This made sense until "dates" were out of order & your formula still worked flawlessly. Above said ... could you provide a little guidance on how this formula is working. Thanks ... Kha "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Ken" wrote in message ... T. The Excel solutions you provide here are extremely helpul ... I stalled on the -1E100 - piece of your formula, but then remembered that "dates" are really numbers. Thank you for supporting these boards ... Your many solutions are greatly appreciated ... Kha "T. Valko" wrote: Try this: =LOOKUP(1E100,D4:D100,B4:B100) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Excel2003 ... Range B4:B100 ... Contains random non-repeating "Text" numbers sorted in "Asc" order. Range D4:D100 ... Contains empty cells & random Dates (mm/dd/yy) Need formula in Cell D3 to return Col B value found against "last" date (not latest date) ie: Col B ... Col D 10 .... 04/22/08 20 .... 06/25/08 35 .... 45 .... 50 .... 05/01/08 ... (need 50 to be returned) 62 .... Etc Thanks ... Kha |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match (maybe?)
This can be a little confusing and might require reading it a few times!
If the lookup_value is greater than any value in the lookup_vector the formula will "match" the *last* value in the lookup_vector that is *less than* the lookup_value. 1E100 (or 1E+100) is scientific notation for a very large number, 1 followed by 100 zeros. This is a shorthand method of expressing 10000000(imagine a string of 100 zeros). Since the lookup_vector contains dates it is guaranteed that the lookup_value will be greater than any value in the lookup_vector so it matches the *last* value in the lookup_vector. The formula then returns the value from the result_vector that corresponds to the *last* value in the lookup_vector that is *less than* the lookup_value. exp101 -- Biff Microsoft Excel MVP "Ken" wrote in message ... T ... Formula does exactly as requested & seems simple enough ... However, I spent some time in the HelpScreen for LOOKUP & I am still at a loss for how the "IE100" part of this formula works??? =Lookup(1E100,D4:D100,B4:B100) I am seeing ... Lookup 1E100 (a large #) in Col D, Return value from same row Col B. Thing is ... the large # can not be found, so I thought Lookup would return next highest value ... This made sense until "dates" were out of order & your formula still worked flawlessly. Above said ... could you provide a little guidance on how this formula is working. Thanks ... Kha "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Ken" wrote in message ... T. The Excel solutions you provide here are extremely helpul ... I stalled on the -1E100 - piece of your formula, but then remembered that "dates" are really numbers. Thank you for supporting these boards ... Your many solutions are greatly appreciated ... Kha "T. Valko" wrote: Try this: =LOOKUP(1E100,D4:D100,B4:B100) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Excel2003 ... Range B4:B100 ... Contains random non-repeating "Text" numbers sorted in "Asc" order. Range D4:D100 ... Contains empty cells & random Dates (mm/dd/yy) Need formula in Cell D3 to return Col B value found against "last" date (not latest date) ie: Col B ... Col D 10 .... 04/22/08 20 .... 06/25/08 35 .... 45 .... 50 .... 05/01/08 ... (need 50 to be returned) 62 .... Etc Thanks ... Kha |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match (maybe?)
T ... When in LOOKUP Help I was hung up on the following statement:
"If LOOKUP can't find the lookup_value, it matches the LARGEST value in lookup_vector that is less than or equal to lookup_value." LARGEST & LAST Value would have been the same IF my Date Col sorted in Ascending order ... BUT since it isn't then Lookup is still returning the LAST value. (Which of course ... Is exactly what I wanted this Formula to do) Do I have this resonably correct now or am I still missing something? Thanks ... Kha "T. Valko" wrote: This can be a little confusing and might require reading it a few times! If the lookup_value is greater than any value in the lookup_vector the formula will "match" the *last* value in the lookup_vector that is *less than* the lookup_value. 1E100 (or 1E+100) is scientific notation for a very large number, 1 followed by 100 zeros. This is a shorthand method of expressing 10000000(imagine a string of 100 zeros). Since the lookup_vector contains dates it is guaranteed that the lookup_value will be greater than any value in the lookup_vector so it matches the *last* value in the lookup_vector. The formula then returns the value from the result_vector that corresponds to the *last* value in the lookup_vector that is *less than* the lookup_value. exp101 -- Biff Microsoft Excel MVP "Ken" wrote in message ... T ... Formula does exactly as requested & seems simple enough ... However, I spent some time in the HelpScreen for LOOKUP & I am still at a loss for how the "IE100" part of this formula works??? =Lookup(1E100,D4:D100,B4:B100) I am seeing ... Lookup 1E100 (a large #) in Col D, Return value from same row Col B. Thing is ... the large # can not be found, so I thought Lookup would return next highest value ... This made sense until "dates" were out of order & your formula still worked flawlessly. Above said ... could you provide a little guidance on how this formula is working. Thanks ... Kha "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Ken" wrote in message ... T. The Excel solutions you provide here are extremely helpul ... I stalled on the -1E100 - piece of your formula, but then remembered that "dates" are really numbers. Thank you for supporting these boards ... Your many solutions are greatly appreciated ... Kha "T. Valko" wrote: Try this: =LOOKUP(1E100,D4:D100,B4:B100) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Excel2003 ... Range B4:B100 ... Contains random non-repeating "Text" numbers sorted in "Asc" order. Range D4:D100 ... Contains empty cells & random Dates (mm/dd/yy) Need formula in Cell D3 to return Col B value found against "last" date (not latest date) ie: Col B ... Col D 10 .... 04/22/08 20 .... 06/25/08 35 .... 45 .... 50 .... 05/01/08 ... (need 50 to be returned) 62 .... Etc Thanks ... Kha |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match (maybe?)
LARGEST & LAST Value would have been the same
IF my Date Col sorted in Ascending order That's the "key" to understanding how this works. By default the LOOKUP function *expects* the lookup_vector to be sorted in ascending order. The help on LOOKUP is written based on that assumption. However, if the data isn't sorted that way then the function doesn't work the way it should. Some smart person discovered this "last value greater than the lookup_value" and we've been exploiting this behavior ever since! -- Biff Microsoft Excel MVP "Ken" wrote in message ... T ... When in LOOKUP Help I was hung up on the following statement: "If LOOKUP can't find the lookup_value, it matches the LARGEST value in lookup_vector that is less than or equal to lookup_value." LARGEST & LAST Value would have been the same IF my Date Col sorted in Ascending order ... BUT since it isn't then Lookup is still returning the LAST value. (Which of course ... Is exactly what I wanted this Formula to do) Do I have this resonably correct now or am I still missing something? Thanks ... Kha "T. Valko" wrote: This can be a little confusing and might require reading it a few times! If the lookup_value is greater than any value in the lookup_vector the formula will "match" the *last* value in the lookup_vector that is *less than* the lookup_value. 1E100 (or 1E+100) is scientific notation for a very large number, 1 followed by 100 zeros. This is a shorthand method of expressing 10000000(imagine a string of 100 zeros). Since the lookup_vector contains dates it is guaranteed that the lookup_value will be greater than any value in the lookup_vector so it matches the *last* value in the lookup_vector. The formula then returns the value from the result_vector that corresponds to the *last* value in the lookup_vector that is *less than* the lookup_value. exp101 -- Biff Microsoft Excel MVP "Ken" wrote in message ... T ... Formula does exactly as requested & seems simple enough ... However, I spent some time in the HelpScreen for LOOKUP & I am still at a loss for how the "IE100" part of this formula works??? =Lookup(1E100,D4:D100,B4:B100) I am seeing ... Lookup 1E100 (a large #) in Col D, Return value from same row Col B. Thing is ... the large # can not be found, so I thought Lookup would return next highest value ... This made sense until "dates" were out of order & your formula still worked flawlessly. Above said ... could you provide a little guidance on how this formula is working. Thanks ... Kha "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Ken" wrote in message ... T. The Excel solutions you provide here are extremely helpul ... I stalled on the -1E100 - piece of your formula, but then remembered that "dates" are really numbers. Thank you for supporting these boards ... Your many solutions are greatly appreciated ... Kha "T. Valko" wrote: Try this: =LOOKUP(1E100,D4:D100,B4:B100) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Excel2003 ... Range B4:B100 ... Contains random non-repeating "Text" numbers sorted in "Asc" order. Range D4:D100 ... Contains empty cells & random Dates (mm/dd/yy) Need formula in Cell D3 to return Col B value found against "last" date (not latest date) ie: Col B ... Col D 10 .... 04/22/08 20 .... 06/25/08 35 .... 45 .... 50 .... 05/01/08 ... (need 50 to be returned) 62 .... Etc Thanks ... Kha |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match (maybe?)
T ... I have 3 things to Thank you for he
1: For sticking with me & walking me thru how this formula works ... 2: For Exploiting Excels various behaviors ... 3: For supporting this board ... Thank you ... Kha "T. Valko" wrote: LARGEST & LAST Value would have been the same IF my Date Col sorted in Ascending order That's the "key" to understanding how this works. By default the LOOKUP function *expects* the lookup_vector to be sorted in ascending order. The help on LOOKUP is written based on that assumption. However, if the data isn't sorted that way then the function doesn't work the way it should. Some smart person discovered this "last value greater than the lookup_value" and we've been exploiting this behavior ever since! -- Biff Microsoft Excel MVP "Ken" wrote in message ... T ... When in LOOKUP Help I was hung up on the following statement: "If LOOKUP can't find the lookup_value, it matches the LARGEST value in lookup_vector that is less than or equal to lookup_value." LARGEST & LAST Value would have been the same IF my Date Col sorted in Ascending order ... BUT since it isn't then Lookup is still returning the LAST value. (Which of course ... Is exactly what I wanted this Formula to do) Do I have this resonably correct now or am I still missing something? Thanks ... Kha "T. Valko" wrote: This can be a little confusing and might require reading it a few times! If the lookup_value is greater than any value in the lookup_vector the formula will "match" the *last* value in the lookup_vector that is *less than* the lookup_value. 1E100 (or 1E+100) is scientific notation for a very large number, 1 followed by 100 zeros. This is a shorthand method of expressing 10000000(imagine a string of 100 zeros). Since the lookup_vector contains dates it is guaranteed that the lookup_value will be greater than any value in the lookup_vector so it matches the *last* value in the lookup_vector. The formula then returns the value from the result_vector that corresponds to the *last* value in the lookup_vector that is *less than* the lookup_value. exp101 -- Biff Microsoft Excel MVP "Ken" wrote in message ... T ... Formula does exactly as requested & seems simple enough ... However, I spent some time in the HelpScreen for LOOKUP & I am still at a loss for how the "IE100" part of this formula works??? =Lookup(1E100,D4:D100,B4:B100) I am seeing ... Lookup 1E100 (a large #) in Col D, Return value from same row Col B. Thing is ... the large # can not be found, so I thought Lookup would return next highest value ... This made sense until "dates" were out of order & your formula still worked flawlessly. Above said ... could you provide a little guidance on how this formula is working. Thanks ... Kha "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Ken" wrote in message ... T. The Excel solutions you provide here are extremely helpul ... I stalled on the -1E100 - piece of your formula, but then remembered that "dates" are really numbers. Thank you for supporting these boards ... Your many solutions are greatly appreciated ... Kha "T. Valko" wrote: Try this: =LOOKUP(1E100,D4:D100,B4:B100) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Excel2003 ... Range B4:B100 ... Contains random non-repeating "Text" numbers sorted in "Asc" order. Range D4:D100 ... Contains empty cells & random Dates (mm/dd/yy) Need formula in Cell D3 to return Col B value found against "last" date (not latest date) ie: Col B ... Col D 10 .... 04/22/08 20 .... 06/25/08 35 .... 45 .... 50 .... 05/01/08 ... (need 50 to be returned) 62 .... Etc Thanks ... Kha |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Index/Match (maybe?)
You're quite welcome!
-- Biff Microsoft Excel MVP "Ken" wrote in message ... T ... I have 3 things to Thank you for he 1: For sticking with me & walking me thru how this formula works ... 2: For Exploiting Excels various behaviors ... 3: For supporting this board ... Thank you ... Kha "T. Valko" wrote: LARGEST & LAST Value would have been the same IF my Date Col sorted in Ascending order That's the "key" to understanding how this works. By default the LOOKUP function *expects* the lookup_vector to be sorted in ascending order. The help on LOOKUP is written based on that assumption. However, if the data isn't sorted that way then the function doesn't work the way it should. Some smart person discovered this "last value greater than the lookup_value" and we've been exploiting this behavior ever since! -- Biff Microsoft Excel MVP "Ken" wrote in message ... T ... When in LOOKUP Help I was hung up on the following statement: "If LOOKUP can't find the lookup_value, it matches the LARGEST value in lookup_vector that is less than or equal to lookup_value." LARGEST & LAST Value would have been the same IF my Date Col sorted in Ascending order ... BUT since it isn't then Lookup is still returning the LAST value. (Which of course ... Is exactly what I wanted this Formula to do) Do I have this resonably correct now or am I still missing something? Thanks ... Kha "T. Valko" wrote: This can be a little confusing and might require reading it a few times! If the lookup_value is greater than any value in the lookup_vector the formula will "match" the *last* value in the lookup_vector that is *less than* the lookup_value. 1E100 (or 1E+100) is scientific notation for a very large number, 1 followed by 100 zeros. This is a shorthand method of expressing 10000000(imagine a string of 100 zeros). Since the lookup_vector contains dates it is guaranteed that the lookup_value will be greater than any value in the lookup_vector so it matches the *last* value in the lookup_vector. The formula then returns the value from the result_vector that corresponds to the *last* value in the lookup_vector that is *less than* the lookup_value. exp101 -- Biff Microsoft Excel MVP "Ken" wrote in message ... T ... Formula does exactly as requested & seems simple enough ... However, I spent some time in the HelpScreen for LOOKUP & I am still at a loss for how the "IE100" part of this formula works??? =Lookup(1E100,D4:D100,B4:B100) I am seeing ... Lookup 1E100 (a large #) in Col D, Return value from same row Col B. Thing is ... the large # can not be found, so I thought Lookup would return next highest value ... This made sense until "dates" were out of order & your formula still worked flawlessly. Above said ... could you provide a little guidance on how this formula is working. Thanks ... Kha "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Ken" wrote in message ... T. The Excel solutions you provide here are extremely helpul ... I stalled on the -1E100 - piece of your formula, but then remembered that "dates" are really numbers. Thank you for supporting these boards ... Your many solutions are greatly appreciated ... Kha "T. Valko" wrote: Try this: =LOOKUP(1E100,D4:D100,B4:B100) -- Biff Microsoft Excel MVP "Ken" wrote in message ... Excel2003 ... Range B4:B100 ... Contains random non-repeating "Text" numbers sorted in "Asc" order. Range D4:D100 ... Contains empty cells & random Dates (mm/dd/yy) Need formula in Cell D3 to return Col B value found against "last" date (not latest date) ie: Col B ... Col D 10 .... 04/22/08 20 .... 06/25/08 35 .... 45 .... 50 .... 05/01/08 ... (need 50 to be returned) 62 .... Etc Thanks ... Kha |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Find Exact Match using INDEX, MATCH | Excel Worksheet Functions | |||
| index(match) Wind Uplift Calculations (match four conditions) | Excel Worksheet Functions | |||
| index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
| How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
| index,match,match on un-sorted data | Excel Worksheet Functions | |||
Linear Mode
