VLookUp "#N/A" message

[Alain]

Good morning,

I would like to know if there are any ways to have
the "#N/A" message replaced by "0" when no records are
found.

Many thanks,

Alain

 

[Andy Wiggins]

You have to test the result with an IF:

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

or

=IF(0=COUNTIF( .........),0,VLOOKUP(........))

--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[Norman Harker]

Hi Alain!

One way:

=IF(ISNA(YourFormula),0,YourFormula)

But there may be better ways depending upon your formula.

 

[RagDyeR]

Try this format:
Data list - B1100
Lookup value - A1

=IF(ISNA(MATCH(A1,B1:B100,0)),0,VLOOKUP(A1,B1100,3,0))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Good morning,

I would like to know if there are any ways to have
the "#N/A" message replaced by "0" when no records are
found.

Many thanks,

Alain

 

[Aladin Akyurek]

If the VLOOKUP() formula requires exact match...

Y2:

=VLOOKUP(LookupValue,LookupTable,ColIdx,FALSE) or
=VLOOKUP(LookupValue,LookupTable,ColIdx,0)

X2:

=IF(ISNA(Y2),0,Y2)

X2 is the ultimate result cell.

Otherwise...

=IF(LookupValue<INDEX(LookupTable,1,1),0,VLOOKUP(LookupValue,LookTable,ColId
x,1))

I wished MS took up the proposal for an additional, optional parameter in
lookup functions so that you could just have:

=VLOOKUP(LookupValue,LookupTable,ColIdx,0,0)

where the last 0 is the return value in case of a lookup failure.