Find first value that is greater than X, and return value in another column

**rabidchild** · 12-19-2014, 06:17 AM

Hello All

I'd appreciate some help on this problem, please. I feel like I can almost do it but my brain seems to have gone on xmas shutdown and isn't co-operating.

I have three columns of data. Column A contains a 'test' number, and many rows have the same value. Column B contains the 'sample' number, and this increases by one with each row that the 'test' value remains the same. Column C contains a 'value', which either increases or remains the same with each increase in the 'sample' column (i.e. there is never a decrease).

For each 'test' I need to look up the 'sample' number where the 'value' is first equal to or greater than 2.

So, in the attached example, for test 1 the sample value where the corresponding test value is first equal to or greater than 2 is sample 7. For test 2, it is sample 5. For test three, no values are equal to or greater than 2 so no sample is returned.

I hope this makes sense. I'd like to know which functions I can use to make this lookup work.

Thanks in advance!

Ben
Example.xlsx
Example.png

**Glenn Kennedy** · 12-19-2014, 06:51 AM

You need an array formula for this. these are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.