Return the last number before a blank cell

[tbollo]

Hello,

I have a tricky problem.

In the below, if there is not a 1 in the first column I want to return a 0. If there is a 1 in the first column I want to return the last number BEFORE the blank cell:

1	2	3	4	5	5
1	2	3	4		4
1	2	3	4	5	5
1	2		4	5	2
	2	3			0
	2	3	4	5	0
1	2		4	5	2
1	2	3		5	3
	2	3	4		0

<tbody>
</tbody>

The numbers in BOLD are what I want returned at the end of each row.

Any help appreciated!

Thanks

 

[tbollo]

Perfect.

Can you adapt if there is a gap after 1 but other numbers follow? For example, if '1' is present but '2' is missing and then there is a '3' present in the same row. In this case we only want to return '1'.

 

[admiral100]

Thanks for the replies.
@admiral100 - this works apart from the fact that the empty cells have a formula in them... Is it possible to work around this?


Thanks

What does the formula return?

 

[admiral100]

Try this :

Ctrl+Shift+Enter NOT just Enter

=IF(A1="",0,IFERROR(INDEX(A1:E1,MATCH(TRUE,(B1:E1=""),0)),LOOKUP(9^9,A1:E1)))

 

[Aladin Akyurek]

Perfect.

Can you adapt if there is a gap after 1 but other numbers follow? For example, if '1' is present but '2' is missing and then there is a '3' present in the same row. In this case we only want to return '1'.

Do you mean we must obtain 1 when the following is the case?

Book1
	A	B	C	D	E
12	1		2	3
Sheet1

 

[tbollo]

Yes that is correct, if possible.

thanks

 

[Aladin Akyurek]

Yes that is correct, if possible.

thanks

Control+shift+enter and copy down:

=IF(A2=1,IF(B2="",1,IFERROR(LOOKUP(9.99999999999999E+307,B2:INDEX(B2:E2,MATCH(TRUE,ISBLANK(B2:E2),0))),LOOKUP(9.99999999999999E+307,B2:E2))),0)