When Vlookup doesn't find a value I want zero to be entered

[Caleb]

Is there any way to replace #N/A with
zero(s)? I tried using find/replace under edit (doesn't work)..... The #N/A are present because Vlookup didn't find matching numbers.... Basically, what I want to do is whenever Vlookup doesn't find a value I want zero to be entered into that cell.....
Any help would be greatly appreciated...
Thanks Caleb Shanafelt...

 

[DonkeyOte]

change your lookup...

if(iserror(vlookup(blah,blah,blah,blah),0,vlookup(blah,blah,blah,blah))

 

[Aladin Akyurek]

On 2002-09-12 11:00, Caleb wrote:
Is there any way to replace #N/A with
zero(s)? I tried using find/replace under edit (doesn't work)..... The #N/A are present because Vlookup didn't find matching numbers.... Basically, what I want to do is whenever Vlookup doesn't find a value I want zero to be entered into that cell.....
Any help would be greatly appreciated...
Thanks Caleb Shanafelt...

If you need exact match, the following will give you 0 instead of #N/A...

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

where LTable is the name that you assign to your lookup table. The 0 as the last argument in MATCH and VLOOKUP means FALSE to Excel

If you install morefunc.xll, which is an free add-in, you can use...

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

Both ways you have an efficient formula qua speed.

 

[Caleb]

Hey thanks Lasw10..... it worked like a charm... and thanks for being so prompt....
I really appreciate it....
Caleb Shanafelt