Free Microsoft Excel 2013 Quick Reference

Stop #N/A Showing

I've got my eyes crossed and can't seem to find the error with this formula.

If there is an error in the lookup, such as a blank cell, I want to return a blank in the cell receiving the lookup result. Currently the formula displays a 0 if the target cell is empty.


If someone could help ID the error, I will be indebted.


I tried the suggestion in this thread, but still got the same results:

I have a spreadsheet gathering information on vehicles. One of the pieces of information is a 'Date Off Strength'.

For the vehicles still on strength I have the symbol '#N/A' showing,, this is linked to a sheet using a VLOOKUP formula, is there a way to stop this symbol showing


Can someone help me with this please?

A4 Has this calculation in it

=IF('[LOUISE_B.xls]Job Sheet'!A4>0,'[LOUISE_B.xls]Job Sheet'!A4,"")

B4 Has this calculation in it

=IF(A4>0,VLOOKUP(A4,'[LOUISE_B.xls]Job Sheet'!$A$4:$B$153,2,FALSE),"")

How can I stop the #N/A from coming up if the result of A4 is "" (a blank cell)

Does the vlookup in B4 see the calcualtion In A4 as being >0 simply because it is there?

If so how can I get around it?

Thanks In Advance


Im trying to stop result '#N/A' from the below formula


If have tried using ISNA but unless im putting it in the wrong place it doesnt appear to be working.

any ideas? I can provide a sample if needed.



I have the following formula that sometimes returns an #N/A value. Is there a way I can stop that from happening?

Thank you in advance

Good Morning,

I have a list that I have validated as a list with a blank on top to enable data override.
I have a formula that uses the content of this cell, c7 say, to perform so vlookup function and return a result. However, when the cell c7 is blank( meaning the blank cell in the list is selected), the result is #N/A.
I am trying to have that result be a blank.

The formula is as follows, assuming c7 is the data containing cell:

=IF(OR(LEFT($C7,1)="W",VLOOKUP($C7,Table2B_1,7)>9,ISBLANK(C7))," ",VLOOKUP($C7,Table2B_1,2))

Can anyone tell me what I am doing wrong?

I think I just don't have enough experience with IF, ISNA and VLOOKUP functions to figure this one out. What I'm trying to do is bypass the first IF formula that returns a true result when the vlookup in the IF statement returns an N/A.

I have two columns and if the target isn't in the first column it'll be in the 2nd column. The problem is that it looks for the target in the first column only and never gets to the 2nd column due to the fact that the IF formula returns TRUE. Perhaps looking at an example would make it easier to understand what I'm talking about.

Here is the formula:


The problem is that A4 has to equal F1, F2 or F3 when looking for the target in either column A or column B. I've also attached a sample spreadsheet.

Any help on getting this to work would be greatly appreciated!



I am working on a formula that, eventually, I would like to be inserted into cells using VBA's FormulaArray Range function. Unfortunately, in its present state, the formula is too long and clumsy to meet the 255 character limit on that function. While I know that I can easily shorten the current formula with simple adjustments, such as using named ranges, I still am forced to ask whether the design of the formula itself could not be improved.

The heart of the formula is an Index/Match combination which will search if any line on the 'Archive' sheet meets the three criteria on the line specified in the 'Data Log' sheet. If so, it returns the value from a particular column of the matching row on the archive sheet. If not, it returns N/A.

=INDEX(Archive!$E$1:$E$1999, MATCH(1, IF(Archive!$B$1:$B$1999='Data Log'!$A2, IF(Archive!$A$1:$A$1999='Data Log'!$C2, IF(Archive!$D$1:$D$1999=1,1))),0))

In an effort to circumvent this unsightly N/A result I have attempted to use the ISNA function paired with an IF statement. The idea is that if the result is N/A then "Pending" will display instead. Otherwise the result of the indexing formula will display. This approach is outlined below:

=IF(ISNA(index function), "pending", index function)

The problem, of course, is that the indexing formula is rather long, and, with this approach, I am required to put its entire contents in the formula twice. I would greatly appreciate any help in streamlining this formula. Remember, the final goal is simply to search the 'Archive' sheet for a single line meeting three criteria and return a value from a fourth column, or if not return "pending".

Thanks in advance....


I have many serial files in a folder like spect1, spect2....spect35 and 2 vital columns D,E

In these columns sometimes a #N/A or 0 or null appears in E and I dont want it to appear.

What can I do to compare the D,E of all files and insert the value (which is text) in E when it finds #N/A or 0 or null. For example in D in 3 files the number 49868686 shows. In one of these 3 files in E the #N/A shows in the other 2 files the material name ACC-FM30A. Well I d like this name to be shown instead of #N/A because the macro compares the D and finds 49868686 in all 3 cases. This is of course impossible for me so help is appreciated.

However I append these 2 columnss of many serial files with the code that a dear member provided
I have no idea how the previous problem I mentioned could be inserted in this macro!!!!So all tasks are done at the same time!!!

Kind regards

Sub AppendData()
Dim SummaryFile As String, SummarySheet As String, DataFile As String, DataSheet As String
Dim SearchDir As String
Dim NextRow As Long, LastDataRow As Long
Dim i As Integer

SummaryFile = ActiveWorkbook.Name
SummarySheet = ActiveSheet.Name
SearchDir = "S:Trade MarketingSELL THROUGH PROJECTÐñüôõðç ìïñöÞSPECTRAProcessed"

For i = 1 To 52
DataFile = "spect" & i & ".xls"
NextRow = Workbooks(SummaryFile).Sheets(SummarySheet).Range("D65535").End(xlUp).Row + 1
Workbooks.Open Filename:=SearchDir & "" & DataFile
DataSheet = Workbooks(DataFile).ActiveSheet.Name
LastDataRow = Workbooks(DataFile).Sheets(DataSheet).Range("D65535").End(xlUp).Row
Workbooks(DataFile).Sheets(DataSheet).Range("D5:E" & LastDataRow).Copy _
Destination:=Workbooks(SummaryFile).Worksheets(SummarySheet).Range("D" & NextRow)
Workbooks(DataFile).Close SaveChanges:=False
Next i
End Sub

Is there away to add to formula to when no value is there that it leave the cell blank and not give me #N/A


I'm using the following formula to find out the smallest number in a set of numbers, and it is currently excluding 0 and numbers < 0 so it gives me an actual price number. However, some of the rows includes "#N/A" because not all fields contain a price, but it doesn't work when "#N/A" shows.



How can I create a formula to make N/A show up as zero, as at present whenever I have an N/A in something that is summed it mucks up the whole sum.



Could any kind soul out there tell me how I would add ISERROR to the
following to stop #N/A?

'=INDEX(Sheet1!$A$7:$A$1152,MATCH($C79,Sheet1!$C$7 :$C$1152,0))


Use the following formula to draw data from a table back to a spreadsheet,
however the formula applies for 20 cells down a page, how do i stop #n/a
appearing for those cells not yet inputed

=INDEX('Cattle prices'!$B$4:'Cattle prices'!$AW$18,MATCH(B76,'Cattle
prices'!$A$4:'Cattle prices'!$A$18,0),MATCH('Cattle Mvts'!E76,'Cattle
prices'!$B$3:'Cattle prices'!$AW$3,0))

thanks ant

I am working on learning vlookups..
I have enclosed a snapshot of what i am trying to get rid of..
The vlookups work fine, but when I auto-filled to have the rest fill in (like a list of products and their costs), where there is no value yet, you get that #N/A symbol. And, because of that symbol, I cannot get a running tally for the total cost..that also comes up #N/A.

Is there a way to not have this(#N/A) show, when there is no value yet in that row to fill in the lookups, and also have a total work with that column that now does have that showing??

I hope that makes sense, but I think the snapshot shows it best.

Picture 6.jpg

I want to be able to total these figures (from a HLookup), but clearly where there is no amount (because that person was not working that day) I get #N/A which means my total also reads #N/A

Staff Day 1 Day 2 Sum
Emma C £39.93 £27.73 £67.66
Elaine S £27.73 #N/A #N/A

I need the #N/A's to read 0 so I can still total all the days.

Can it be done?

I having trouble with my worksheet. I would like to sum up monthly sales totals for individual sales people on a main worksheet. On a worksheet I have monthly sales tabs from Jan - Apr but will continually add to it. I am using index and match to sum up the totals for each sales employee based on a code name, but run into an error if N/A if a sales employee does not have any sales for a particular month. On my main worksheet I have 5 different product categories to total.

My formula is :
=INDEX(Apr!A:O,MATCH('all '!A:A,Apr!B:B,0),14)+ INDEX(Mar!A:O,MATCH('all '!A:A,Mar!B:B,0),14) + INDEX(Feb!A:O,MATCH('all '!A:A,Feb!B:B,0),14) + INDEX(Jan!A:O,MATCH('all '!A:A,Jan!B:B,0),14)

My spreadsheet is attached. What can I do to resolve the N/A showing up for blank data? Any help is greatly appreciated!

Hi all

I have a formula, due to unmatch data from another table, some cell has "#N/A" show up, how could I replace #N/A with [nothing ""] in the cell?

I try to use the if function, such as
However, if the A1 is #N/A ... is still show up as #N/A .

Can anyone help me .... thanks for yr help!

hi all i need help on the following

In cells BS8 I have the following =100*SUBSTITUTE(BR16,"metres","") to get centremetres from cell BR8 and in cell BT8 I have the following

=IF(BS8,ROUND('Under 6 Boys'!D11*(BS8-'Under 6 Boys'!E11)^'Under 6 Boys'!F11,0),"") I get a #Value error

I have also tried
=ROUND('Under 6 Boys'!D11*(BS8-'Under 6 Boys'!E11)^'Under 6 Boys'!F11,0)
In BT8 but I get same error

I have used it in another sheetsheet and it works I don’t understand why is is happening

Plus is there a way to get rid of #N/A as I am using the following in another cell on same spreadsheet
=VLOOKUP(F8,'centre No'!C:D,2,0)
it works but I don’t want the #N/A showing

A spreadsheet I am working on return #n/a into a cell as it only dins a blank
in the validation menu we are using.

Is there any way to get excel to ignore #n/a & show a blank?

I have used NA() to suppress no data in a chart, but I must have missed something.

I've have created one chart which will cycle through different data on change of B1 adn the data comes from range S3:X14.

All appears to work fine except for #N/A shows up as data labels for a series other than the chart I am displaying.

Three questions.

1) How to suppress the #N/A

2) On the PAT chart I would like the axis to be displayed in the format [h]:mm:ss but when I change it on the PAT chart it also changes the axis for the other two charts.

3) Is there a more efficient way to write this

=IF($B$1="MMHRS",AVERAGE($S$3:$S$14),IF($B$1="PAT",AVERAGE($T$3:$T$14),IF($B$1="FREQ",AVERAGE($U$3:$ U$14),"")))


I am plotting a price time series as a line chart. There are some periods without prices (gaps) with #N/A(). I would like to see the gaps (non-continuos line) on the chart for missing data. When I am using the named range as a source for the chart gaps are no longer visible as Excel draws a line between last point before and first point after the gap.
What can I do to show the gaps in line when using a named range as a source for the graph?

I am using the following syntax for the named ranges
Defined name:
Source of values for chart:

Attached are the charts: one with data from a "normal" range with gaps visble and one from a named range with gaps not visible.With gaps from a normal range.jpgNamed range with unwanted lines in gaps.jpg

Thanks in advance for your help!

I've been scouring the net for a function code to allow me to find the max value in an array
that includes #N/A. I need the #N/A to display, to stop the unused values showing in a chart, but
require an add on for the vlookup max function, so i can calculate the max value, as im only getting #N/A
as the max value answer.

Hi all, I have a vlookup that I have almost working but I'm a little stumped my the last bit.
the formula I have is this =VLOOKUP(J4,AW:AY, 3, 0) and the only issue I have is that if there is nothing in J4, then I want nothing to appear in my vlookup cell, but it is currently showing #N/A. Any ideas?

I have this formula:

{=SUM(IF($B4=QE,IF(C$2>StartDate,IF(C$2 < StopDate, CMMIhours,0),0),0))
where QE, StartDate, StopDate, and CMMIhours are all dynamic name ranges. It is supposed to sum CMMIhours if C2 is between the start and stop date, and if QE in that row is the same as B4. However, for some reason, the cell simply shows #N/A. When I click "Show Calculation Steps" under the error message, the N/A's seem to appear in the CMMIhours range. However, that is simply a column of numbers, and when I checked the whole range with "ISNA(CMMIhours)" the result was false for all of them. This has me completely baffled, maybe someone can help?