Is there an easier way?

[Alexey]

Hi Can anyone advise if there is an easier and less complicated way to
achieve the following :

=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))))))

Thanks

A

 

[Harlan Grove]

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!D35000, 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!D35000, 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)

 

[Alexey]

Thanks both

A

 

[Dana DeLouis]

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

achieve the following :

Hi. I like to use Range Names for Tables.
If any error is ok, perhaps with Excel 2007...
(not tested!)

=SUM(
$C127,
IFERROR(VLOOKUP($A124,TblAB,2,0),0),
-IFERROR(VLOOKUP($A124,TblDE,2,0),0)
)

--
HTH :>)
Dana DeLouis