Fin the first value greater than and return another cell value

**va3gjg** · 04-25-2013, 06:19 PM

I want to find the first instance of a value in single row range B9:F9 and return the date in the top row directly above in B1:F1

For instance in range B9:F9 there are a series of numbers constantly increasing from 35-175.
In cell A11 is the threshold value of 100
Above in the range B1:F1 are a series of dates
I want to find the first cell in B9:F9 that is greater than A11 and return the date in the column directly above in B1:F1

I've attached a sample sheet to illustrate.
Find the date.xlsx

I've tried using MATCH just to find the first instance with the -1 value for "greater than" but it keeps returning #N/A

Thank you in advance for your help!
Graham

**loginjmor** · 04-25-2013, 06:39 PM

Hi -

This is just a little bit tricky because of a couple things:

1. You really have to read the help on Match and the optional greater than/less than thing. It's actually the opposite of what you think you read. You want a +1 for the option. Read the help about 5 times and then you will get what they are saying.

2. MATCH returns the value your criteria is greater than or equal to. That means to get the first instance that is greater than your criteria, you have to add +1 to whatever MATCH returns. That means you will have some range errors if your threshold is less than the minimum number in your range or greater than the maximum number in your range.

So, try the following formula:

=INDEX(B1:F1,1,IF(MIN(B9:F9)>B11,1,IF(MAX(B9:F9)<B11,5,MATCH(B11,B9:F9,1)+1)))

Hope this helps.

**loginjmor** · 04-25-2013, 06:41 PM

Just a quick follow up. MATCH actually returns the relative column number in this case, not the value, as I misstated earlier.

**Tony Valko** · 04-25-2013, 09:49 PM

Try this array formula**:

=INDEX(B1:F1,MATCH(TRUE,B9:F9>B11,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date