| 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 |
| 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 | |||
Hybrid Mode
