Alexey said:

Hi Can anyone advise if there is an easier and less complicated way to

achieve the following :

[reformatted]

=SUM(

$C127

+(SUM(

IF(

ISNA(VLOOKUP($A124,MISDATA!$A$3:$B$5000,2,0)),

0,

VLOOKUP($A124,MISDATA!$A$3:$B$5000,2,0)

)-(IF(

ISNA(VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0)),

0,

VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0)

))

))

)

Eliminate the unnecessary SUM calls. SUM(a+b) can ALWAYS be replaced

by a+b.

Eliminate the unnecessary parentheses.

If there could be multiple instances of the A124 value in MISDATA!

A3:A5000 or MISDATA!D3

5000, your formula would use only the first

(topmost) one found, and there's no simpler approach than what you're

using. In which case you could reduce your formula to

=$C127

+IF(ISNA(VLOOKUP($A124,MISDATA!$A$3:$B$5000,2,0)),0,

VLOOKUP($A124,MISDATA!$A$3:$B$5000,2,0))

-IF(ISNA(VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0)),0,

VLOOKUP($A124,MISDATA!$D$3:$E$5000,2,0))

OTOH, if there'd only be at most one instance of the A124 value in

both MISDATA!A3:A5000 or MISDATA!D3

5000, you could use SUMIF. For

example,

=$C127

+SUMIF(MISDATA!$A$3:$A$5000,$A124,MISDATA!$B$3:$B$5000)

-SUMIF(MISDATA!$D$3:$D$5000,$A124,MISDATA!$E$3:$E$5000)