VlookUp that does not return #N/A

C

carl

I use this formula:

=VLOOKUP(I3;marketwatch;1;FALSE)

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

Thank you in advance.
 
B

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 "".
 
K

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
 
A

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.
Click to expand...

--

[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.
 
J

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
 
K

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
 
J

jeanette.rimmer

Thanks Karl,

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

Cheers
 
Top