How do I create a formula to retun a value of 0 (zero) when a VLO.
 

When searching multiple tables with a V or HLOOKUP formula, I want a search
that doe not find anything to result in a zero instead of an error (#N/A)

I have tried a variations of IF formulas but am missing something.

Thanks

IF(ISNA(YourFormula),0,YourFormula)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Adam at GTOFL" <Adam at wrote in message
...
When searching multiple tables with a V or HLOOKUP formula, I want a
search
that doe not find anything to result in a zero instead of an error (#N/A)

I have tried a variations of IF formulas but am missing something.

Thanks

You could also use this formula:
=LOOKUPV(C1,A1:B100,2,0,0) or
=LOOKUPV(C1,A1:B100,2,0,"")


But first
1. Press Alt+F11. Insert Module. Copy and Paste the below:

Function LookupV(Lookup_Value, Table_Array As Range, Col_Index_Num,
Range_value, Optional Error_Msg)
LookupV = Application.VLookup(Lookup_Value, Table_Array, Col_Index_Num,
Range_value)
If IsError(LookupV) And Not IsMissing(Error_Msg) Then LookupV =
Error_Msg
End Function

Make sure the VBA code is only 4 rows!
The LOOKUPV formula is Shorter and is Faster then VLOOKUP

Ola Sandstrom

Do you mean it's faster than the 2 calls that are used in a formula like

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

That could be, but LOOKUPV(...) certainly can't be faster than =VLOOKUP(...)


On Sat, 19 Feb 2005 11:21:02 -0800, Ola wrote:

You could also use this formula:
=LOOKUPV(C1,A1:B100,2,0,0) or
=LOOKUPV(C1,A1:B100,2,0,"")


But first
1. Press Alt+F11. Insert Module. Copy and Paste the below:

Function LookupV(Lookup_Value, Table_Array As Range, Col_Index_Num,
Range_value, Optional Error_Msg)
LookupV = Application.VLookup(Lookup_Value, Table_Array, Col_Index_Num,
Range_value)
If IsError(LookupV) And Not IsMissing(Error_Msg) Then LookupV =
Error_Msg
End Function

Make sure the VBA code is only 4 rows!
The LOOKUPV formula is Shorter and is Faster then VLOOKUP

Ola Sandstrom

You are right Myrna, VBA code is always slower. So VLOOKUP should always be
used when Error handling is not needed.

However in this case, I've done some 20.000 rows test and LOOKUPV has been
faster, since it only does the VLOOKUP procedure once not twice. So the main
reson for this post is to offer an alternative - which I think work. But do
test it.

Ola Sandstrom