# how to elimate #N/A?

#### cjbyf

##### New Member
Hello,

How can I alter this formula so I do not get a #N/A in a cell if no value is returned? I am trying to average the column and it throws it off.

Here's my current formula:

=VLOOKUP("boston",'1.26.05'!\$A\$1:\$B\$50,2,FALSE)

I would appreciate any help I could get.

Thanks,
Chris

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Without seeing your data this should work....

=if(isna(VLOOKUP("boston",'1.26.05'!\$A\$1:\$B\$50,2,FALSE),"",VLOOKUP("boston",'1.26.05'!\$A\$1:\$B\$50,2,FALSE))

Thanks Ken,

I plugged your suggestion in, however the dialogue box pops up telling me there's an error in the formula and the proceeds to highlight.

Here is the formula broken up:

=if(isna(VLOOKUP("boston",'1.26.05'!\$A\$1:\$B\$50,2,FALSE)

,"", <---- excel highlights this part of the formula

VLOOKUP("boston",'1.26.05'!\$A\$1:\$B\$50,2,FALSE))

Now I know the "" is the key to this but not sure why the error.

Any thoughts why that is?

I appreciate it very much.

Chris

Ken is missing a bracket after "False". Trial this. Dave
Code:
``IF(ISNA(VLOOKUP("boston",'[1.26.05]1.26'!\$A\$1:\$B\$50,2,FALSE)),"",VLOOKUP("boston",'[1.26.05]1.26'!\$A\$1:\$B\$50,2,FALSE))``

I don't know that doesn't seem to be working either... what am I doing wrong?

Assuming you currently have a working formula, Syntax is as follows:-

=IF(ISNA(Your_Formula),"",Your_Formula)

Just plug your formula in this

Following on from Ken:
=IF(ISNA(VLOOKUP("boston",'[1.26.05]1.26'!\$A\$1:\$B\$50,2,FALSE) ),"",VLOOKUP("boston",'[1.26.05]1.26'!\$A\$1:\$B\$50,2,FALSE))

Somehow [] got into the past few formulas. Try:-

=IF(ISNA(VLOOKUP("boston",'1.26.05'!\$A\$1:\$B\$50,2,FALSE)),"",VLOOKUP("boston",'1.26.05'!\$A\$1:\$B\$50,2,FALSE))

Replies
4
Views
738
Replies
7
Views
147
Replies
1
Views
351
Replies
3
Views
207
Replies
2
Views
419

Threads
1,203,644
Messages
6,056,525
Members
444,872
Latest member
agutt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back