#n/a

[crtas1]

Is there anyway to set up a formula to look through a whole spreadsheet and make all #n/a =0?

I was thinking a VLookup with an IF statement but I can't pull it off.

Thanks

 

[Aladin Akyurek]

On 2002-09-10 13:19, crtas1 wrote:
Is there anyway to set up a formula to look through a whole spreadsheet and make all #n/a =0?

I was thinking a VLookup with an IF statement but I can't pull it off.

Thanks

You can modify your VLOOKUP formula.

Two options:

=IF(ISNUMBER(MATCH(lookup-value,INDEX(LTable,0,1),0)),VLOOKUP(lookup-value,LTable,ColIdx,0),0)

where LTable refers to the range that houses your lookup table.

=IF(ISNA(SETV(VLOOKUP(...))),0,GETV())

The 2nd option requires morefunc.xll, an add-in that you can download from:

http://longre.free.fr/english/index.html

Edited in order add GETV().
This message was edited by Aladin Akyurek on 2002-09-11 06:16

 

[crtas1]

Great thanks I will give it a try