Combining HLOOKUP and IF statements

[ehardway123]

Hey all,

I am stuck on this formula. So I created key to lookup and match a type a for a month to get a value. Some the keys do not have a value (meaning key is non existent) for a month so it returns as #N/A in the cell. This is the lookup formula I have.
=HLOOKUP(I4,'BL!A:M1095,MATCH(CALC!B9,A2:A1095,0),FALSE)

how can I get the return values that are #N/A to be zero instead? I know an IF statement can but I haven't been able to figure it out. Please help!


Erica

 

[Smitty]

The simplest way is with IFERROR:

=IFERROR(HLOOKUP(I4,'BL!A:M1095,MATCH(CALC!B9,A2:A1095,0),FALSE),0)

HTH,

 

[RonB1111]

Wrap it within an IFERROR statement, like this:

=[COLOR=#ff0000]IFERROR([/COLOR]HLOOKUP(I4,'BL!A:M1095,MATCH(CALC!B9,A2:A1095,0),FALSE)[COLOR=#ff0000],0)[/COLOR]

edit - I see Smitty beat me to it!

 

[ehardway123]

THANKS!!! I didn't even think about that one. its working now