#N/A In V Lookups

[omletto]

Hello,

If you take a look at the attached spreadsheet, the lookups are working fine on the summary sheet, but where it can't find data in the lookup I need it to display '0' instead of '#N/A'.

Any help would be appreciated.

Kind regards,

Om.

[zbor]

You can always use principle:

=IF( ISNA(formula), 0, formula )

[zbor]

Also.. in AA you could write:

=SUMIF($C$7:$Z$7;AA$7; $C8:$Z8) and pull right and down which is easier way for summing in your example

[omletto]

Sorry, bit of a novice here. How would that formulae read?

[zbor]

Here:

=IF(ISNA(MATCH($A8, Jan!$M:$M,0)), 0, VLOOKUP(A8, Jan!M:N, 2, 0))

Of course, you could also use:

=IF(ISNA(VLOOKUP(A8, Jan!M:N, 2, 0)), 0, VLOOKUP(A8, Jan!M:N, 2, 0))

but MATCH is slightly better then VLOOKUP for this kind of test...

[jawad1_ali]

omletto, why dont' u look in help for sumif.

[KeesValkenswaar]

See example for jan-10.
With 0 for SALES and empty string for COS.
In Excel 2007 there is a new, more compact, function for this.

[omletto]

Thanks, I didn't realise you could combine SUMIF with VLookups, its working now.

All help much appreciated.

Kind regards,

Om