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

 

[Aladin Akyurek]

In F2 control+shift+enter, not just enter, and copy down:

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

 

[admiral100]

Hi,

Ctrl+Shift+Enter NOT just Enter

This formula deals with cases of more then one empty cell that not sequentially

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

	A	B	C	D	E	F
1	1	2	3	4	5	5
2	1	2		4		2
3	1	2			5	2
4	1	2		4	5	2
5		2	3			0
6		2	3	4	5	0
7	1	2		4		2
8	1		3		5	1
9		2	3	4		0

<colgroup><col width="70" span="7" style="width:52pt"> </colgroup><tbody>
</tbody>

 

[tbollo]

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

 

[Aladin Akyurek]

See also post #2 ...

 

[tbollo]

@Aladin Akurek that appears to work unless there is just a 1 in the row...

 

[Aladin Akyurek]

@Aladin Akurek that appears to work unless there is just a 1 in the row...

I thought that 1 condition is what you wanted. What else is the condition?

 

[tbollo]

I am getting the below results:

		3	4		0
1	2	3	4	5	5
		3			0
1		3			3
				5	0
1	2				2
					0
1					#N/A
					0
1		3		5	5
1					#N/A
1					#N/A
					0
1					#N/A

<colgroup><col><col><col span="3"><col></colgroup><tbody>
</tbody>

 

[tbollo]

If there is just a '1' in the row it is return '#N/A' instead of '1'

Thanks
Tim

 

[Aladin Akyurek]

If there is just a '1' in the row it is return '#N/A' instead of '1'

Thanks
Tim

We can include the first cell in our formula. Again control+shift+enter and copy down:


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