NA

[Darts via OfficeKB.com]

I am copying down my formula and in areas that i don't have any data the
result is N/A
here is my sample formula
VLOOKUP(D11, 'Pay Rate'!$D$2:$E$8, 2)

thanks
for your help

 

[Pete_UK]

To get rid of the #N/A message, you can change your formula thus:

=if(isna(VLOOKUP(D11, 'Pay Rate'!$D$2:$E$8, 2)),"",VLOOKUP(D11, 'Pay
Rate'!$D$2:$E$8, 2))

and copy this down. This will give you a blank cell instead of #N/A,
controlled by the "" in the middle - you could put a message like "not
present" in there if you wish.

Hope this helps.

Pete

 

[JE McGimpsey]

Did you have a question?

If you're just trying to not see the #N/As, one way:


=IF(ISNA(MATCH(D11,'Pay Rate'!$D$2:$D$8)),"",VLOOKUP(D11, 'Pay
Rate'!$D$2:$E$8, 2))

 

[T. Valko]

I'm guessing that you don't want #N/A?

This will leave those cells blank:

=IF(ISNA(VLOOKUP(D11, 'Pay Rate'!$D$2:$E$8, 2)),"",VLOOKUP(D11, 'Pay
Rate'!$D$2:$E$8, 2))

Biff

 

[Darts via OfficeKB.com]

You jare the best!!

I'm guessing that you don't want #N/A?

This will leave those cells blank:

=IF(ISNA(VLOOKUP(D11, 'Pay Rate'!$D$2:$E$8, 2)),"",VLOOKUP(D11, 'Pay
Rate'!$D$2:$E$8, 2))

Biff

I am copying down my formula and in areas that i don't have any data the
result is N/A

[quoted text clipped - 3 lines]

thanks
for your help

 

[Darts via OfficeKB.com]

Pete worked great!! how would it work if I wanted to do the same thing and
the fromula is:
=m24*n24

To get rid of the #N/A message, you can change your formula thus:

=if(isna(VLOOKUP(D11, 'Pay Rate'!$D$2:$E$8, 2)),"",VLOOKUP(D11, 'Pay
Rate'!$D$2:$E$8, 2))

and copy this down. This will give you a blank cell instead of #N/A,
controlled by the "" in the middle - you could put a message like "not
present" in there if you wish.

Hope this helps.

Pete

I am copying down my formula and in areas that i don't have any data the
result is N/A

[quoted text clipped - 3 lines]

thanks
for your help

 

[Chip Pearson]

Just for the record, in Excel 2007 you can use the new IFERROR function.
E.g.,

=IFERROR(your_formula,result_if_error)

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

You jare the best!!

I'm guessing that you don't want #N/A?

This will leave those cells blank:

=IF(ISNA(VLOOKUP(D11, 'Pay Rate'!$D$2:$E$8, 2)),"",VLOOKUP(D11, 'Pay
Rate'!$D$2:$E$8, 2))

Biff

I am copying down my formula and in areas that i don't have any data the
result is N/A

[quoted text clipped - 3 lines]

thanks
for your help

 

[Pete_UK]

Do you mean that M24 or N24 contain the vlookup formula, and as a
result of multiplying these together you get another error message
(#VALUE) ? If so, then you can either change the original formula I
gave you so that it returns zero instead of "" in the middle (so that
m24*n24 will also show zero), or you could change your second formula
to:

=IF(ISERROR(m24*n24),0,m24*n24)

which will also give you a zero instead of an error.

Hope this helps.

Pete

Pete worked great!! how would it work if I wanted to do the same thing and
the fromula is:
=m24*n24

To get rid of the #N/A message, you can change your formula thus:

=if(isna(VLOOKUP(D11, 'Pay Rate'!$D$2:$E$8, 2)),"",VLOOKUP(D11, 'Pay
Rate'!$D$2:$E$8, 2))

and copy this down. This will give you a blank cell instead of #N/A,
controlled by the "" in the middle - you could put a message like "not
present" in there if you wish.

Hope this helps.

Pete

I am copying down my formula and in areas that i don't have any data the
result is N/A

[quoted text clipped - 3 lines]

thanks
for your help