Vlookup Error
 

Hi

I have alot of Vlookup functions in a worksheet. Some return values, others
do not which results in #N/A in the cell.

Is there a way to avoid #N/A being returned. All I want is either a positive
value or an empty cell.

Thanks for any suggestions

Hi Mick,

One way

=IF(ISNA(lookup_formula),"",lookup_formula)

this traps #N/A errors specifically, a more generic solution is

=IF(ISERROR(lookup_formula),"",lookup_formula)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"mick.smith1964" wrote in message
...
Hi

I have alot of Vlookup functions in a worksheet. Some return values,
others
do not which results in #N/A in the cell.

Is there a way to avoid #N/A being returned. All I want is either a
positive
value or an empty cell.

Thanks for any suggestions

Try using the isna() function, @if(Isna(vlookup.....),"",(vlookup.....))

"mick.smith1964" wrote:

Hi

I have alot of Vlookup functions in a worksheet. Some return values, others
do not which results in #N/A in the cell.

Is there a way to avoid #N/A being returned. All I want is either a positive
value or an empty cell.

Thanks for any suggestions

See for a set of alternative approaches:

http://www.mrexcel.com/board2/viewtopic.php?t=62102

BTW, also investigate whether you can sort the lookup table(s) you use
for that would allow you to do faster lookups (with added bonus no #N/A's).

mick.smith1964 wrote:
Hi

I have alot of Vlookup functions in a worksheet. Some return values, others
do not which results in #N/A in the cell.

Is there a way to avoid #N/A being returned. All I want is either a positive
value or an empty cell.

Thanks for any suggestions

Works great. Thanks very much for the suggestion.

"BCGROUPNY" wrote in message
...
Try using the isna() function, @if(Isna(vlookup.....),"",(vlookup.....))

"mick.smith1964" wrote:

Hi

I have alot of Vlookup functions in a worksheet. Some return values,
others
do not which results in #N/A in the cell.

Is there a way to avoid #N/A being returned. All I want is either a
positive
value or an empty cell.

Thanks for any suggestions

Hi,

Try this: =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

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

Regards,
Ola