INDEX, MATCH, IF function does not work

[Michael]

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:A>C2;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/tutorial...f5-1d419bb8539a/build-a-c-sql-server-dat.aspx

 

[smartin]

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:A>C2;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/tutorial...f5-1d419bb8539a/build-a-c-sql-server-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.

 

[Michael]

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 whil
you tap Enter.

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Useful Vista Tweaks
http://www.eggheadcafe.com/tutorial...81-b515-c80c5d6d00ec/useful-vista-tweaks.aspx

 

[smartin]

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))