I use this formula:

=VLOOKUP(I3;marke****ch;1;FALSE)

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

Thank you in advance.

=IF(ISNA(=VLOOKUP(I3;marke****ch;1;0)),"",=VLOOKUP (I3;marke****ch;1;0))

Regards,
KL


"carl" wrote in message
...
I use this formula:

=VLOOKUP(I3;marke****ch;1;FALSE)

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

Thank you in advance.

Try this

=IF(ISNA(VLOOKUP(I3,marke****ch,1,FALSE))=TRUE,"No
Entry",VLOOKUP(I3,marke****ch,1,FALSE))

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


"carl" wrote in message
...
I use this formula:

=VLOOKUP(I3;marke****ch;1;FALSE)

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

Thank you in advance.

sorry, remove the equal signs inside the formula.

KL


"KL" wrote in message
...
=IF(ISNA(=VLOOKUP(I3;marke****ch;1;0)),"",=VLOOKUP (I3;marke****ch;1;0))

Regards,
KL


"carl" wrote in message
...
I use this formula:

=VLOOKUP(I3;marke****ch;1;FALSE)

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

Thank you in advance.

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


"Barb Reinhardt" wrote in message
...
Try this

=IF(ISNA(VLOOKUP(I3,marke****ch,1,FALSE))=TRUE,"No
Entry",VLOOKUP(I3,marke****ch,1,FALSE))

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


"carl" wrote in message
...
I use this formula:

=VLOOKUP(I3;marke****ch;1;FALSE)

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

Thank you in advance.

=ISNUMBER(MATCH(I3;INDEX(marke****ch;0;1);0))+0

or

=1-ISNA(VLOOKUP(I3;marke****ch;1;0))

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

carl wrote:
I use this formula:

=VLOOKUP(I3;marke****ch;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.

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" wrote in message
...
sorry, remove the equal signs inside the formula.

KL


"KL" wrote in message
...
=IF(ISNA(=VLOOKUP(I3;marke****ch;1;0)),"",=VLOOKUP (I3;marke****ch;1;0))

Regards,
KL


"carl" wrote in message
...
I use this formula:

=VLOOKUP(I3;marke****ch;1;FALSE)

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

Thank you in advance.

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" wrote in message
...
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" wrote in message
...
sorry, remove the equal signs inside the formula.

KL


"KL" wrote in message
...
=IF(ISNA(=VLOOKUP(I3;marke****ch;1;0)),"",=VLOOKUP (I3;marke****ch;1;0))

Regards,
KL


"carl" wrote in message
...
I use this formula:

=VLOOKUP(I3;marke****ch;1;FALSE)

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

Thank you in advance.

Thanks Karl,

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

Cheers
"KL" wrote in message
...
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" wrote in message
...
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" wrote in message
...
sorry, remove the equal signs inside the formula.

KL


"KL" wrote in message
...
=IF(ISNA(=VLOOKUP(I3;marke****ch;1;0)),"",=VLOOKUP (I3;marke****ch;1;0))

Regards,
KL


"carl" wrote in message
...
I use this formula:

=VLOOKUP(I3;marke****ch;1;FALSE)

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

Thank you in advance.