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

[Adam at GTOFL]

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

 

[Niek Otten]

IF(ISNA(YourFormula),0,YourFormula)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Ola]

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

 

[Myrna Larson]

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(...)

 

[Ola]

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