How to recognize a blank cell as zero

[pedro-egoli]

I have the following formula to find the last balance in a column

=INDEX(D5:D500,MATCH(9.99999999999999E+307,D5:D500))

How do I add to formula to recognize blank cell as a zero?

 

[Toadstool]

Hi Pedro-Egoli,

I can't see what is wrong with your formula. Can you explain what is failing and provide a scenario?

 

[pedro-egoli]

The result of formula as it stands is that when a cell becomes blank in column D the result shows as the last non zero balance.
This then displays an incorrect balance in another cell dependent on result.

 

[Toadstool]

I still don't see the issue as here I have the last value as 11,308

...and if I make some cells empty I still get the same result:

 

[shaowu459]

I don't see any unexpected results.

Book1.xlsx
	D	E	F
4
5
6	111		0
7
8	333
9
10
11
12
13	1
14
15	4
16
17
18	0
Sheet6

Cell Formulas
Range	Formula
F6	F6	=LOOKUP(9^99,D5:D500)

 

[pedro-egoli]

I still don't see the issue as here I have the last value as 11,308

View attachment 14614

...and if I make some cells empty I still get the same result:

View attachment 14615

This is part of my sheet showing blanks

NEW sheet commenced 10717.xls
	A	B	C	D	E
34	23/05/2020			-$105.39	$3,412.49
35	24/05/2020	-$47.30		-$105.39	$3,365.19
36	24/05/2020	-$105.39			$3,259.80
37	25/05/2020				$3,259.80
May 2020

Cell Formulas
Range	Formula
E34:E37	E34	=IF(A34="","",E33+(B34+C34))

In cell with formula it displays -$105.39 rather than a blank or "0"
The solution provided by Shaow459 works so thank you both for your interest and help