Search formula in Excel

[KALINDA]

I have a table, a column M with a formula "IF". This formula tells me different values ranging from 4 to 0, so decreasing. That is, I have several lines that follow each with 4, then several other lines with 3 .... until 0.

What I would do:
I get to the first cell that displays a 0, the value of cell in the same row, column E.

For example, if the first 0 appears in cell M9, I get the value in cell E9.

I tried with different formulas "Search" I do not really control, could you tell me what formula to return?

[absolute55]

Here is one method (there are probably others), I hope it matches your request:

A set M2 (I imagine you have a title)

=IF(M1=0;IF(YourFormula);IF(M1=E1;IF(YourFormula);IF(IF(YourFormula)=0;E2;IF(YourFormula))))

Replaces YourFormula by the formula that you have currently in M2 and pulls down.

[KALINDA]

Thank you for your quick response!

I do not think the formula is appropriate. Or maybe it's me who did not understand (which is quite possible too!)

Columns that interest me are the columns M and E. In column M, the values (using a formula IF) range from 2 to 0:
M3 to M18 2
From M19 to M36: 1
From M37: 0

I bring up my cell B7 the value of the cell E when the first 0 in column M.

In my case, it would be to appear in cell B7 the value 37 which corresponds to cell E39.

The interest is to have the user directly to the value 37 under the eyes, without his having to enter formulas or manipulated to do with the column M.

I hope I have been more clear.

[absolute55]

Your explanations was clearer. You can content yourself with counting the number of rows in column M whose value is greater than 0 (1 to get the expected result). Specifically a "sum if" in the matrix and you're done (you can also do this with SUMPRODUCT but I am less familiar).

{=SUM(IF(M$3:M$47>0;1;0))+1}

To recall who told array formula () for having validated the formula with Ctrl + Shift + Enter

[henrythomas]

To quickly show all formulas in your Excel spreadsheet hold down the CONTROL key on the keyboard and press the grave accent key.Show formulas does not change the spreadsheet, only the way it is displayed.
Advantage of this formula:
1) It allows you to quickly read through all formulas to check for errors
2) When you click on a formula, Excel outlines in color the cells referenced in the formula. This helps you to trace the data being used in a formula.