replacing #N/A values with zero values

[Guest]

replacing #N/A values with zero values

I have a cell that is looking up values in another sheet using HLOOKUP....
when it doesn't find a value it returns a #N/A value....
I have tried using an IF statement with an imbedded ISNA or ISERROR or
ERROR.TYPE function to have it return a zero in the event of #N/A and the
actual value if it is able to find what it is looking up....
I need to convert the #N/As to zeros so I can use the sum function by merely
highlighting a range with my mouse...

Thanks.

 

[SteveG]

Ted,

You are correct that you should combine the IF, ISERROR (or ISNA) with
your lookup. This worked for me. Maybe the syntax was off in your
attempts.

=IF(ISERROR(HLOOKUP(6,Sheet1!A12,2,FALSE)),0,HLOOKUP(6,Sheet1!A12,2,FALSE))

Does that help?

Steve

 

[Guest]

It sounds like you're on the right track. An IF() function with an ISERROR()
function should work:

=IF(ISERROR(HLOOKUP(...))=TRUE,0,HLOOKUP(...))

If it still isn't working, try posting your formula, maybe there is a simple
syntax problem.

HTH,
Elkar

 

[Guest]

Here is the formula I used:
=IF(ISERROR(HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,MATCH('Plant
Financing'!E$4,'Calgen
NC'!$D$5:$D$10,0),FALSE))="TRUE",0,(HLOOKUP($A6,'Calgen
NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),FALSE)))

 

[Guest]

Here is the formula I tried using: =IF(ISERROR(HLOOKUP($A6,'Calgen
NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,'Calgen
NC'!$D$5:$D$10,0),FALSE))="TRUE",0,(HLOOKUP($A6,'Calgen
NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),FALSE)))

 

[daddylonglegs]

You don't need quotes around TRUE, in fact you don't need ="TRUE" at
all. Also if you're only experiencing #N/A errors it's better to use
ISNA rather than ISERROR otherwise you risk masking errors that you
might want to know about such as a misspelt function in your formula

Try

=IF(ISNA(HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,MATCH('Plant
Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),0)),0,HLOOKUP($A6,'Calgen
NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,'Calgen
NC'!$D$5:$D$10,0),0))

 

[SteveG]

Ted,

Try it like this,

=IF(ISERROR(HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,MATCH('Plant
Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),FALSE)),0,HLOOKUP($A6,'Calgen
NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,'Calgen
NC'!$D$5:$D$10,0),FALSE))

You did not need the ="TRUE" because the ISERROR provides that in the
formula. The start of your If False formula had a ( which it did not
need.

Steve

 

[Guest]

I would say that you don't need the ="TRUE" part and if you insist on using
it, try getting rid of the quotes around it.

 

[Dave Peterson]

Drop this portion completely:
="TRUE"
or change it to:
=TRUE

(remove the quotes)

 

[Pete_UK]

Remove the quotes from around the TRUE in the middle of the formula. In
fact you don't need that little part, so your formula can become:

=IF(ISERROR(HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,
MATCH('Plant Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),FALSE)),0,
(HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,
MATCH('Plant Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),FALSE)))

Hope this helps.

Pete

 

[Harlan Grove]

Ted wrote...

Here is the formula I used:
=IF(ISERROR(HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,
MATCH('Plant Financing'!E$4,'Calgen NC'!$D$5:$D$10,0),FALSE))="TRUE",0,
(HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,
'Calgen NC'!$D$5:$D$10,0),FALSE)))

....

Others have pointed out the ="TRUE" issue, but I'll note that you could
compress this a bit.

=IF(COUNTIF('Calgen NC'!$D$5:$T$5,$A6)
*COUNTIF('Calgen NC'!$D$5:$D$10,'Plant Financing'!E$4),
HLOOKUP($A6,'Calgen NC'!$D$5:$T$10,MATCH('Plant Financing'!E$4,
'Calgen NC'!$D$5:$D$10,0),0),0)

 

[Guest]

Thanks everyone...
problem solved...you were right about the syntax..
Short answer is this;

=if(iserror(Hllookup(Detail)),0,

in other words I should have used a comma instead of an equal sign...