Forum home » Delegate support and help forum » Microsoft Excel Training and help » #N/A entries

#N/A entries

ResolvedVersion 2003

Wed 4 Jun 2008, 14:41 Sarah (guest) said...

#N/A entries

Where a vlookup returns #N/A as the answer, I need to be able to display this as zero - so that I can then include this cell in a furthur calculation.
Extracting figures >0 still includes #N/A.
Any ideas how to do this ?

Fri 6 Jun 2008, 14:40 Trainer Amanda said...

RE: #N/A entries

Hi Sarah

Thank you for your question; and welcome to the forum.

You could try the following:
Create another formula in a separate cell that looks at the cell with the vlookup in it, and displays 0 if there is an #N/A error or displays the vlookup result if there is one.

The formula would look like this (say the vlookup formula is in cell B6):

=if(isna(B6),0,vlookup(cell,table array,column,false)

Then you can refer to the cell with the above formula in it in other formulas.

I hope this helps.
Amanda

Training courses

98.7 % recommend us

More live stats...

Training information:

Excel tip:

Create own ribbon tab - Excel 2010

a. In Excel click on the File tab
b. Select Options from left hand side
c. Choose the Customize Ribbon section
d. Click the New Tab button (below the list of tabs on the right hand side of the dialog box)
e. Select the New Tab (Custom) and enter a name, by clicking the Rename button (below the list of tabs on the right hand side of the dialog box)
f. Select the New Group (Custom) and enter a name, by clicking the Rename button (below the list of tabs on the right hand side of the dialog box)
g. Add commands to your tab and group by locating them on the list on the right hand side (remembering that you can change the list using the drop down box at the top of the list of commands) and clicking the Add button between the two panes to add them to your tab and group
h. You can rearrange the commands in your group, the groups on any tab or the tabs, using the up and down arrow buttons beside the list of tabs.
i. Click OK to apply your changes

View all Excel hints and tips

#N/A entries

#N/A entries

RE: #N/A entries

Excel articles

Excel tip:

Create own ribbon tab - Excel 2010