Funtion Match

  • Thread starter Thread starter Hugo
  • Start date Start date
H

Hugo

I need help with function Match.
Classical formula: match($A$2,Z12:Z2000,0)
Let say in cells Z12:Z2000 are numbers from 0 to 10.

If I am looking for one number only (for example 5) this formula works normaly.
But problem is when I am looking for the first matching any value greater then 4.
My solution for this problem is combination of formulas in cells Z1:Z11
In Z1 I am looking for 0, in Z2 I am looking for 1.....
and from this results it is possible to find the MIN in cells Z6:Z11

This way of solution is very difficult for editing.
It would be very nice and easy to write ">4" in the cell A2 but this is impossible.
I hope somebody will understand my basic English and will offer some professional
and elegant solution. (Excel 2000)

Thank's for any help. Hugo
 
I'm probably using a cannon to kill a fly here, but:

=SMALL(((Z12:Z2000>$A$2)*ROW(Z12:Z2000)),SUM(--((Z12:Z2000>$A$2)*ROW(Z12:Z20
00)=0))+1)-ROW(Z12:Z2000)

entered as an array formula with <Ctrl> <Shift> <Enter>.
 
Back
Top