Excel function

[Gordana Godzo]

Hi.

I have four coloumns containing number of accounting accounts (coloumns A
and C) with values (coloumns B and D), for example:

Coloumn A Coloumn B Coloumn C
Coloumn D Column E (result)
40100 600 40100
700 100
40101 500 40101
700 200
40103 900 40102
500 500 (I get 0)
40103
1000 100

I need a formula in Coloumn E that will substract the values in coloumn B
from the values in coloumn D, depending on the number of of the account.

I use a VLOOKUP formula, but I have a problem when in coloumn C there is a
account number that is not in coloumn A (Acc. number 40102 with value of
500). The result should be 500, but I get 0.

I hope it is clear to understand
Please help
Thanks
--
Gordana Godzo
Head of Controlling & Budgeting
TITAN Group - Cementarnica "USJE" AD Skopje
Prvomajska bb, 1000 Skopje
Republic of Macedonia
tel: +389 2 2786 138
fax: +389 2 2782 535
e-mail: (e-mail address removed)

 

[Bob Phillips]

What formula are you using, what is in the VLOOKUP table, and why do you
VLOOKUP?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Gordana Godzo]

=IF(ISNA(VLOOKUP($BE6,$A$5:$B$310,2)-VLOOKUP($BE6,$C$5:$D$310,2),0,(VLOOKUP($BE6,$A$5:$B$310,2)-VLOOKUP($BE6,$C$5:$D$310,2)))
where in BE is the number of account

Any other formula will be appreciated
Thanks

 

[Toppers]

Try:

=IF(ISNA(VLOOKUP($BE6,$A$5:$B$310,2,0)),VLOOKUP($BE6,$C$5:$D$310,2,0),IF(ISNA(VLOOKUP($BE6,$C$5:$D$310,2,0)),VLOOKUP($BE6,$A$5:$B$310,2,0),VLOOKUP($BE6,$A$5:$B$310,2,0)-VLOOKUP($BE6,$C$5:$D$310,2,0)))

HTH

 

[Gordana Godzo]

That works fine, thanks a lot

Try:

=IF(ISNA(VLOOKUP($BE6,$A$5:$B$310,2,0)),VLOOKUP($BE6,$C$5:$D$310,2,0),IF(ISNA(VLOOKUP($BE6,$C$5:$D$310,2,0)),VLOOKUP($BE6,$A$5:$B$310,2,0),VLOOKUP($BE6,$A$5:$B$310,2,0)-VLOOKUP($BE6,$C$5:$D$310,2,0)))

HTH