Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX, MATCH, IF function does not work
Hi
I have a table with two columns, much like stock quotes. Column A holds the time in ascending order. Column B holds the values, which are pretty much at random, just like stock quotes. I want to find the first appearance of a certain value AFTER a certain time. The INDEX MATCH function does just that but I cannot get the time criteria in there. Here is what I do: =INDEX(A:A;MATCH(C1;IF(A:AC2;B:G);0)) A = time column (ascending order) B = value column (random order) C1 = value to look for C2 = time after which the values first appearance I want to know (not before that time) The whole thing kept me busy for a couple of days now, without any solution besides adding extra columns. That is not an option because I have a couple of hundred tables which waists to much of my time and data power. I would appreciate every thought on that. Thanks in advance. Michael EggHeadCafe - Software Developer Portal of Choice Build a C# SQL Server Database Export Utility http://www.eggheadcafe.com/tutorials...erver-dat.aspx |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX, MATCH, IF function does not work
Michael wrote:
Hi I have a table with two columns, much like stock quotes. Column A holds the time in ascending order. Column B holds the values, which are pretty much at random, just like stock quotes. I want to find the first appearance of a certain value AFTER a certain time. The INDEX MATCH function does just that but I cannot get the time criteria in there. Here is what I do: =INDEX(A:A;MATCH(C1;IF(A:AC2;B:G);0)) A = time column (ascending order) B = value column (random order) C1 = value to look for C2 = time after which the values first appearance I want to know (not before that time) The whole thing kept me busy for a couple of days now, without any solution besides adding extra columns. That is not an option because I have a couple of hundred tables which waists to much of my time and data power. I would appreciate every thought on that. Thanks in advance. Michael EggHeadCafe - Software Developer Portal of Choice Build a C# SQL Server Database Export Utility http://www.eggheadcafe.com/tutorials...erver-dat.aspx Try this out; must be array-entered*: =INDEX($B$2:$B$9999,MATCH(1,--($C$1<=$A$2:$A$9999),0)) *Array-entry: commit the formula by holding down Ctrl and Shift while you tap Enter. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thanks
Hi smartin
Thanks for the reply. I don?t really get your formula, as I am looking for the time when the value appears for the first time. MATCH should not start comparing with the first time value, but e.g. 1 hour later. Am I supposed to copy your formula down due to the $-sign? Best regards Michael smartin wrote: Michael wrote:Try this out; must be 05-nov-09 Michael wrote: Try this out; must be array-entered*: =INDEX($B$2:$B$9999,MATCH(1,--($C$1<=$A$2:$A$9999),0)) *Array-entry: commit the formula by holding down Ctrl and Shift while you tap Enter. Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Useful Vista Tweaks http://www.eggheadcafe.com/tutorials...ta-tweaks.aspx |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thanks
My bad: I read the requirement backwards.
Revised formula (no array-entry needed): Return the next time in column A where the value in C1 matches a value in column B: =INDEX($A$2:$A$9999,1+MATCH(C1,B2:B9999,0)) Michael wrote: Hi smartin Thanks for the reply. I don?t really get your formula, as I am looking for the time when the value appears for the first time. MATCH should not start comparing with the first time value, but e.g. 1 hour later. Am I supposed to copy your formula down due to the $-sign? Best regards Michael smartin wrote: Michael wrote:Try this out; must be 05-nov-09 Michael wrote: Try this out; must be array-entered*: =INDEX($B$2:$B$9999,MATCH(1,--($C$1<=$A$2:$A$9999),0)) *Array-entry: commit the formula by holding down Ctrl and Shift while you tap Enter. Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Useful Vista Tweaks http://www.eggheadcafe.com/tutorials...ta-tweaks.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
INDEX w. MATCH function | Excel Worksheet Functions | |||
Need help with function using INDEX and MATCH. | Excel Worksheet Functions | |||
Match and Index function | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions |