work up to find next number not 0

Data Range

	A	B	C
1
2	63		57
3	0
4	68
5	0
6	95
7	57
8	0
9	0
10	0

Try ths...

=LOOKUP(2,1/(A2:A10<>0),A2:A10)

Let's use this example to demonstrate how this works:

Data Range

	A	B	C
1
2	63		57
3	0
4	68
5	0
6	95
7	57
8	0
9	0
10	0

Return the bottom-most non-zero number in column A.

=LOOKUP(2,1/(A2:A10<>0),A2:A10)

This expression will return an array of either TRUE or FALSE:

(A2:A10<>0)

<> means: not equal to. I think it's easier to understand if you read it as: "is not". A2:A10 is not 0

A2 = 63 <> 0 = TRUE
A3 = 0 <> 0 = FALSE
A4 = 68 <> 0 = TRUE
A5 = 0 <> 0 = FALSE
A6 = 95 <> 0 = TRUE
A7 = 57 <> 0 = TRUE
A8 = 0 <> 0 = FALSE
A9 = 0 <> 0 = FALSE
A10 = 0 <> 0 =FALSE

We then use the divison operation to coerce those logical values to numbers:

A2 = 1 / TRUE = 1
A3 = 1 / FALSE = #DIV/0!
A4 = 1 / TRUE = 1
A5 = 1 / FALSE = #DIV/0!
A6 = 1 / TRUE = 1
A7 = 1 / TRUE = 1
A8 = 1 / FALSE = #DIV/0!
A9 = 1 / FALSE = #DIV/0!
A10 = 1 / FALSE = #DIV/0!

At this point the formula looks like this:

=LOOKUP(2,{1;#DIV/0!;1;#DIV/0!;1;1;#DIV/0!;#DIV/0!;#DIV/0!),A2:A10)

The way that LOOKUP works is if the lookup_value is greater than all the
values in the lookup_vector it will match the *last* value in the
lookup_vector. So, we simply need a lookup_value that is greater than any number in the lookup_vector.

We use a lookup_value of 2 because we know that the result of this expression:

1/(A2:A10<>0)

will not return a value greater than 1. Therefore, the lookup_value 2 is guaranteed to be greater than all the 1s in the lookup_vector.

The *last* numeric value in the lookup_vector is the 4th 1. The #DIV/0! errors are ignored (unless there are ALL errors).

This is how that would look:

Data Range

	A
2	1
3	#DIV/0!
4	1
5	#DIV/0!
6	1
7	1
8	#DIV/0!
9	#DIV/0!
10	#DIV/0!

So, the last (bottom-most) instance of a non-zero number was in A7.

Data Range

	A	B	C
1
2	63		57
3	0
4	68
5	0
6	95
7	57
8	0
9	0
10	0

=LOOKUP(2,1/(A2:A10<>0),A2:A10) = 57

Use IFERROR:

=IFERROR(LOOKUP(2,1/(A2:A10<>0),A2:A10),"")

Actually, the formula will work either way:

=LOOKUP(2,1/(A2:A10<>0),A2:A10)
=LOOKUP(2,1/(A2:A10),A2:A10)

The <>0 version gives you more info as to what the criteria is.

Also, it is assumed that the range does not contain any text entries, numbers only.

Hi,

how can i combine that with XLOOKUP?

For example i want to find the value of "k" that is not zero (57). I had the following formula, but it gives me an error:
=+LOOKUP(2;1/(XLOOKUP("k";A:A;B:B)<>0);XLOOKUP("k";A:A;B:B))


- A B
1 a
2 b 63
3 c 0
4 d 68
5 e 0
6 k 0
7 k 0
8 k 57
9 k 0
10 k 0

Thank you!

Administrative Note:

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread