Get the first value that is not 0 in a column-CodePudding

   |    A   |    B   |
---|--------|--------|---------
1  |    0   |   500  |
2  |    0   |        |
3  |    0   |        |
4  |  500   |        |
5  |  400   |        |
6  |    0   |        |
7  |  700   |        |
8  |  300   |        |
9  |        |        |

In Cell B1 I want to display the first value that is <> 0 in the Column A.
(in this example it would be 500)

I tried to go with something like this but could not make it work:

B1 =LOOKUP(2,1/(A1:A8<>0),A1:A8)

How do I need to modify the formula to get the needed result?

CodePudding user response：

Can try FILTER().

=@FILTER(A:A,A:A>0)

CodePudding user response：

Index and Match will find the next non 0 in a column

=INDEX(A:A,MATCH(TRUE,INDEX(A:A<>0,),0))