Check if value has been exceeded

**ppataki** · 06-24-2011, 07:19 AM

Dear All,

I have a problem:
I have an array that contains data (in a single row)
I have a number in another field
Now I would like to know which number in the array is bigger than the number specified in the field

I tried MATCH function but it does not work
like: =MATCH(M3;B5:H5;-1)
M3=50
B5:H5 contains two numbers: 10 and 55
the result of the formula is error #N/A while it should give 4 as 55 is in the fourth cell in the array

Could you please advise?

Many thanks

**teylyn** · 06-24-2011, 07:45 AM

Hello,

M5:H5 are seven cells. You only specified 2 values, 10 and 55. You did not specify in which cells these two values actually reside. Therefore it is not obvious that the "correct" result of the Match() function should be a 4 (which would be cell E5. What's in F5, G5 and H5?)

I have about a dozen questions, which could be easily answered if you posted a workbook with your data layout and the context.

Please do so, because it will be so much easier to analyse the issue and come up with a suggestion.

cheers,

**martindwilson** · 06-24-2011, 07:46 AM

B5:H5 must be sorted descending for that to work

**ppataki** · 06-24-2011, 07:53 AM

Please find the workbook attached

Many thanks!!

ps. values cannot be sorted, the original order is needed

**martindwilson** · 06-24-2011, 07:56 AM

well it wont work then!

**teylyn** · 06-24-2011, 08:09 AM

try this array formula

=MIN(IF(B5:H5>M3,COLUMN(B5:H5)-1,""))

you need to confirm it with CTRL-SHIFT-ENTER

It will tell you the position of the smallest number that is greater than the value in M3.

So, if E5=55 and F5=66, the result will be 4, because E5 is the smallest number that is greater than the 50 in M3.

cheers,