VlookUp that does not return #N/A

[Guest]

I use this formula:

=VLOOKUP(I3;marketwatch;1;FALSE)

Can it be modified to not return #N/A ?

Thank you in advance.

 

[KL]

=IF(ISNA(=VLOOKUP(I3;marketwatch;1;0)),"",=VLOOKUP(I3;marketwatch;1;0))

Regards,
KL

 

[Barb Reinhardt]

Try this

=IF(ISNA(VLOOKUP(I3,marketwatch,1,FALSE))=TRUE,"No
Entry",VLOOKUP(I3,marketwatch,1,FALSE))

If you want a blank displayed, change "No Entry" to "".

 

[KL]

sorry, remove the equal signs inside the formula.

KL

 

[KL]

Hi Barb,

The =TRUE is redundant as the expression IF(x,y,z) evaluates whether x is
TRUE by default and if the z-parameter is ommitted and x-parameter is not
TRUE then FALSE will be returned by default too.

Regards,
KL

 

[Aladin Akyurek]

=ISNUMBER(MATCH(I3;INDEX(marketwatch;0;1);0))+0

or

=1-ISNA(VLOOKUP(I3;marketwatch;1;0))

1 as result means a hit, and 0 a failure.

I use this formula:

=VLOOKUP(I3;marketwatch;1;FALSE)

Can it be modified to not return #N/A ?

Thank you in advance.

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

 

[jeanette.rimmer]

hi, Im having exactly the same problem with my vlookups

Using your formula (without the =signs) gets rid of the #N/a but returns
zero for cells which did have figures in

This is the formula I used

=IF(ISNA(VLOOKUP($B3,HrsMon,2,FALSE)),"",VLOOKUP($B3,HrsMon,2,FALSE))

Can you help?

Cheers

 

[KL]

Hi Jeanette,

I think there may be two explanations to it:

1) Your table has 0's or empty cells in column 2.
2) If you use time values, then, given that time values in Excel are
decimals between 0 and 1, maybe you just see the rounded numbers . Try
applying the Tme or General format to the result.

Regards,
KL

 

[jeanette.rimmer]

Thanks Karl,

I did have 0's in column 2 and hadnt copied my vlookup correctly,

Cheers