How to find the first highest value with another condition

**sjlabrie** · 02-22-2011, 05:30 PM

Hi,

I want to report the value in column F corresponding to the first value higher than 10 in column I and corresponding to A1 in column A. To extract the first value, I was able to use this formula :

=INDEX(G:G,MATCH(INDEX($I3:I206000,SUMPRODUCT(MATCH(1,--(I3:$I206000>10),0)),1),I:I,0),1)

But, then I want to do the same for the value A2 from column A, for A3... and so on.

I have created Sheet2 as a reference.

Thanks in advance for your help.

Simon

**DonkeyOte** · 02-23-2011, 04:08 AM

Your sample file does not quite out to your formula above.

Using your sample - if we assume you want to return Time where 483-533 first exceeds 10 for each SamplePos listed in Sheet2 - and you want to do this efficiently (relatively speaking):

Please Login or Register  to view this content.

Then:

Please Login or Register  to view this content.

Performing the same without a helper column will be inefficient - more so the larger the source data set / number of SamplePos being searched for

If you can sort your data you can improve efficiency of approach further.

**sjlabrie** · 02-23-2011, 09:45 AM

Originally Posted by DonkeyOte

Your sample file does not quite out to your formula above.

Using your sample - if we assume you want to return Time where 483-533 first exceeds 10 for each SamplePos listed in Sheet2 - and you want to do this efficiently (relatively speaking):

Please Login or Register  to view this content.

Then:

Please Login or Register  to view this content.

Performing the same without a helper column will be inefficient - more so the larger the source data set / number of SamplePos being searched for

If you can sort your data you can improve efficiency of approach further.

Thank you DonkeyOte... It works perfectly.