Free Microsoft Excel 2013 Quick Reference

Replace VLOOKUP #N/A! Value With 0

I have been trying to work this out all morning...

I am using Vlookup,
=VLOOKUP('Google Traffic UK'!$C5017,'Google Traffic UK'!$C$7:$Q$3107,2,FALSE)

Sometimes the value is false & displays N/A.
I would like to replace the N/A with 0 but can't seem to get it working.

Any help appreciated.

Thanks


Post your answer or comment

comments powered by Disqus
replacing #N/A values with zero values

I have a cell that is looking up values in another sheet using HLOOKUP....
when it doesn't find a value it returns a #N/A value....
I have tried using an IF statement with an imbedded ISNA or ISERROR or
ERROR.TYPE function to have it return a zero in the event of #N/A and the
actual value if it is able to find what it is looking up....
I need to convert the #N/As to zeros so I can use the sum function by merely
highlighting a range with my mouse...

Thanks.

Hi,

Within the VBA code I have written I am trying to do a Find "00/01/1900" and Replace "ONGOING" but it seems to be overlooking the request without returning any error messages. I need to change "00/01/1900" so I dont get large negative values in columns C and D.
I have other find and replace requests and they work fine.
Any help on this matter would be greatly appreciated.


	VB:
	
 
Sub MainMacro2() 
     '
     ' MainMacro2 Macro
     ' This one does everything!
     '
     'This looksup Property reference and brings back data in relevant cell
    Range("S3").Select 
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-13],Sheltered!C[-18],1,0)" 
    Range("T3").Select 
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-14],'Stocklist at 010410'!C[-19],1,0)" 
    Range("U3").Select 
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-15],'Minor Works'!C[-20],1,0)" 
    Range("V3").Select 
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16],'Minor Works'!C[-21]:C[-17],5,0)" 
    Range("W3").Select 
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-17],'Minor Works'!C[-22]:C[1],24,0)" 
    Range("X3").Select 
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-18],'Major Works'!C[-23],1,0)" 
    Range("Y3").Select 
    ActiveCell.FormulaR1C1 = "=VLOOKUP('Major Works'!C[-24]:C[-20],5,0)" 
    Range("Y3").Select 
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-19],'Major Works'!C[-24]:C[-20],5,0)" 
    Range("Z3").Select 
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-20],'Major Works'!C[-25]:C[-2],24,0)" 
     
     'Copy and Paste Special Values on Selection
    Range("S3:Z3").Select 
    Selection.AutoFill Destination:=Range("S3:Z150"), Type:=xlFillDefault 
    Range("S3:Z150").Select 
    ActiveWindow.SmallScroll Down:=-126 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Application.CutCopyMode = False 
     
     'Find and Replace all 00/01/1900 dates with ONGOING
    Range("U3:Z150").Select 
    [COLOR=red]Selection.Replace What:="00/01/1900*", Replacement:="ONGOING", LookAt:= _[/COLOR] 
    [COLOR=red]                   xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _[/COLOR] 
    [COLOR=red]                   ReplaceFormat:=False[/COLOR] 
     
     
     'Add's formulas to Columns B,C,D to calculate Void Duration
    Range("B3").Select 
    ActiveCell.FormulaR1C1 = "=R1C6-RC[6]" 
    Range("C3").Select 
    ActiveCell.FormulaR1C1 = _ 
    "=IF(AND(RC[3]=RC[18],RC[20]=""ONGOING""),R1C6-RC[19],RC[20]-RC[19])" 
    Range("D3").Select 
    ActiveCell.FormulaR1C1 = _ 
    "=IF(AND(RC[2]=RC[20],RC[22]=""ONGOING""),R1C6-RC[21],RC[22]-RC[21])" 
     
     
     
     'Sets conditions for Columns B,C,D
    Range("B3").Select 
    Selection.FormatConditions.Delete 
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ 
    "=IF(B3>28,B3)" 
    Selection.FormatConditions(1).Interior.ColorIndex = 3 
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ 
    "=IF(AND(B3=14),B3)" 
    Selection.FormatConditions(2).Interior.ColorIndex = 44 
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ 
    "=IF(AND(B3>=0,B3=0,C314,C3)" 
    Selection.FormatConditions(3).Interior.ColorIndex = 3 
    Range("D3").Select 
    Selection.FormatConditions.Delete 
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ 
    "=IF(AND(D3=21),D3)" 
    Selection.FormatConditions(1).Interior.ColorIndex = 44 
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ 
    "=IF(AND(D3>=0,D328,D3)" 
    Selection.FormatConditions(3).Interior.ColorIndex = 3 
    Range("B3:D3").Select 
    Selection.AutoFill Destination:=Range("B3:D97"), Type:=xlFillDefault 
     
     
     'Copy and Paste Special Values in Columns B,C,D - CHANGE RANGE to match number of rows containing data
    Range("B3:D97").Select 
    Selection.Copy 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=False 
    Application.CutCopyMode = False 
     
     'Replaces all #N/A values with ""
    Columns("B:Z").Select 
    Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, _ 
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
    ReplaceFormat:=False 
    Range("A1").Select 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


While using vllookup am having trouble with the result when value is not recognized or even present. I would like the result in the cell to be blank if the value sought is not found. Am now getting #N/A or just #. As I'm trying to sum the results am failing miserably!! Any help please

WilliamO

Hi,

thanks to Donkeyote I fixed one problem but I forgot to ask about getting rid of #N/A on the same formula.

My formula belows works, but if nothing is there to be found it returns #N/A.

How do I add onto my formula to make it return a value of 0 if it can't locate anything?

=IFERROR(INDEX(ST,MATCH($H665,STT,0),5),INDEX(ST,MATCH($I665,STT,0),5))

Thanks in Advance.

Hello Everyone ,

I have been trying to figure out how to get rid of this evil #n/a value, in my formula,

Here is the formula I am using : =INDEX(Automate_Pricing!C4:G13,MATCH(PLAN.ECO!R10,Automate_Pricing!B4:B13,0),MATCH(PLAN.ECO!V10,Auto mate_Pricing!C3:G3,0))

to be a little bit more specific

R10 is a model
V10 is a code number going from 1 to 5

So let's say I have 3 models :

Model A
Model B
Model C

And also 3 different codes :

Code 1
Code 2
Code 3

This formula is use to determine the price of a model depending on it's code .

Model A - Code 1 = 10$
Model A - Code 2 = 20$

Etc...

This formula is used for a rental project , my problem is that we offer several different models although only part of them are up for rental,
so when you choose a model that is available on our inventory , but not available for rent , I get the error #n/a and then it won't let me SUM up .

Can someone help me !

Thank you very much

I am using the results of a Vlookup in calculations for other cells, the
problem is that if a value of #N/A is returned (when it can't find what I'm
looking up), all calculations attached to that cell give me #N/A's as well.
I want it to return a zero if it would normally return an #N/A. I can
usually achieve this with a SUMIF(A1,">0") in another cell that points to my
vlookup, however because of the math in this specific circumstance, my
calculations need to be able to point directly to the vlookup, and not the
sumif. How can I get Vlookup to report a zero instead of an #N/A. I've
failed with different IF attempts, and it won't let me successfully place the
vlookup function inside of the SUMIF.

Thanks for your help!

I am using the results of a Vlookup in calculations for other cells, the
problem is that if a value of #N/A is returned (when it can't find what I'm
looking up), all calculations attached to that cell give me #N/A's as well.
I want it to return a zero if it would normally return an #N/A. I can
usually achieve this with a SUMIF(A1,">0") in another cell that points to my
vlookup, however because of the math in this specific circumstance, my
calculations need to be able to point directly to the vlookup, and not the
sumif. How can I get Vlookup to report a zero instead of an #N/A. I've
failed with different IF attempts, and it won't let me successfully place the
vlookup function inside of the SUMIF.

Thanks for your help!

I use Vlookups extensively at work. I run into #N/A frequently in the tables I use. Sometimes I need to replace the #N/A with a 0 or a blank or some other value.

I usually then Copy and Paste Special - Values and then select the range. I would like to put together a macro that then runs at this point after I have done the prior steps.

Is there an easy way to have some sort of prompt appear that asks "Which Value would you like to replace the #N/A with?" I would then enter the value and then the macro would find and replace the #N/A's with the entered value I have selected?

(Hope this is clear) Using Excel 2000

I am trying to change all the cell in column H that have the value "#N/A" in them to 0. However, I want the loop to stop when it hits the last line with in it rather than just continue on forever. I have written some code and have been playing with it for a few hours, but cannot get it to stop.

'Replace N/A's with 0
Range("H:H").Copy
Range("H:H").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("H2").Select
    For Each x In Range("R:C")
    R = Selection.Row
    C = Selection.Column
    If Cells(R, 1) <> " " Then
        Cells(R, C).Replace What:="#N/A", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Selection.Offset(1, 0).Select
    End If
Next x
If anyone can help me out I would appreciate it greatly.Thank you in advance.

currently have a spreadsheet that has a standard Vlookup in it- the Vlook up searchs for a text code(ex BV), if BV is found the cell containing the data is displayed (for example if the Vlook up is looking for BV code and it is found the table displays 12.25 or what ever value is in that cell).

Problem I have is when the look up encounters a cell without a value in it I end up with a #n/a error code (example if the vlookup is looking for BV and it is not found the table displays the #n/a error code). when consolidating the data this error prevents sheet from caluclating the totals.

I need to replace the #n/a code with a 0 and not finding a way to do that with a vlookup function in place.

Any suggestions?

Hello,

I am dealing with an Excel workbook which was created 3-4 years ago (on an older version of Excel) and also a new Excel workbook, which is created on Office version 2003.

I have noticed a discrepancy with the return value of the vlookup function which I would need further advice on.

In the older version of excel, wherever a "lookup_value" takes the value of a "-" (minus sign), vlookup returns a value of 0.

However, in this new version of Excel, where I am trying to do the same, by putting "-" into cells, vlookup returns a "#N/A".

Is there anyway to let vlookup to do the same as before (recognizing "-" as being an empty cell and returns a 0 )

Thank you

Han

Hi all,

The below table is a sample. I have about a zillion rows. I want to update the "orig price" column with numbers from the "new price" column. But i dont Excel to pick up the #N/As in the "new price" column. If there's a #N/A then i want Excel to keep the existing value that is in the "orig price" column.

So for product 1 the $1.613 will be replaced by the $1.67 this is fine. But say in product 3 i want to keep the $38.65 price as there is a #N/A in the "new price" column.

Hope this makes sense.

Item Orig. Price New Price
Product 1 1.613 1.67
Product 2 0.4096 0.41
Product 3 38.65 #N/A
Product 4 9.95 #N/A
Product 5 0.7711 0.82

Thanks for any help

Is it possible to add code to the following macro to replace the #N/A left by the formula to just N/A after the formula is pasted values.

Here is the For part of the code that cycles through all the cells that will contain the formula.

Code:
For Each cell In Rng
R = cell.Row
    Range("Line") = Cells(R, 1)
With query
On Error Resume Next
    .Range("A1").QueryTable.Refresh BackgroundQuery:=False
End With
With main
    Cells(R, 3).FormulaR1C1 = "=VLOOKUP(RC2&"""",Query!C2:C3,2,FALSE)"
    Cells(R, 3).Value = Cells(R, 3).Value
End With
Next cell


Hi, I am trying to calculate a linear trend line for the following data:

Jan 06 100
Feb 06 101
Mar 06 104
Apr 06 99
May 06 106
Jun 06 95
Jul 06 99
Aug 06 101
Sep 06 106
Oct 06 104
Nov 06 89
Dec 06 99
Jan 07 67
Feb 07 105
Mar 07 110
Apr 07 123
May 07 99
Jun 07 #N/A
Jul 07 #N/A
Aug 07 #N/A
Sep 07 #N/A
Oct 07 #N/A
Nov 07 #N/A
Dec 07 #N/A

I wish to show 2006 as one data series on my graph, and 2007 (Year to date) overlaid as a second data series on the graph. Because of this, I don't think there is any way to add the trendline for 2006 and 2007 combined directly to the graph.

I tried the trend function, but the problem pops up that it does NOT ignore the #N/A values in the calculation, it gives the #VALUE error instead. If I replace #N/A with zero's, etc. then the trend function will factor the 0 values into the trend.

This is a monthly report with a LOT of graphs like this, so I don't really want to re-do the range for the trend function every month, I'd like it to be done automatically. I think I can figure out how to do this using the IF function, but it'd involve doing a seperate table for every single month, which again would be a major pain.

Anyone have any idea how to get the TREND function to accept (but ignore when calculating) #N/A values, or any decent workaround? Thanks for the help!

Problem with vlookup returning #N/A value in only some of the worksheet
I have an inventory of some 4,000 items
The data I'm looking for is in the target worksheet, but vlookup returns
#N/A for some of the results.
I'm wondering if it has to do with the format of item numbers (which are a
combination of numbers & letters).
Would it make a difference if some was number & some text??

I have a list of numbers in one column, e.g. 3.1, 3.4, 2.3 4.3 etc. Does
anybody know if I can replace numbers within a range with one value i.e. all
numbers between 3.1 and 3.5 to become 3B all numbers between 2.0 and 2.5 to
become 2B and numbers between 4.0 and 4.5 to become 4B, the list would then
become 3B, 3B, 2B, 4B. Is it possible to do this without going to individual
cells, similar to the find and replace function?

Problem with vlookup returning #N/A value in only some of the worksheet
I have an inventory of some 4,000 items
The data I'm looking for is in the target worksheet, but vlookup returns
#N/A for some of the results.
I'm wondering if it has to do with the format of item numbers (which are a
combination of numbers & letters).
Would it make a difference if some was number & some text??

Right guys,

Having some trouble using conditional formatting.
I have a workbook of about 4000 rows of data.
I am using lookups to pull relevant data onto one sheet to display it in an easier to use manor.

Some of the values are coming up as #N/A, but this was expected.

I am basically trying to use conditional formatting, or something of that kind to replace the "#N/A" error with the word "Solid".

Any help would be greatly appreciated.

Cheers,

Nick

Hi,
I have a large worksheet with thousands of rows, columns and formulaes.

A lot of the cells have an #N/A error in them. It would take too long to rewrite the formulae with isna functions in them so I was wondering if there is a quicker way to replace every #N/A with a "*".

I'm sure someone has posted this before as a piece of VBA code but I can't find it anywhere.

Any help would be greatly appreciated.

Regards,
Dixon

Hi

i have linked a few pages through vlookup but sometimes when i open my working s/sheet it brings n/a values instead of actual data. It happened to me once before i think i managed to re-activate the sheet by opening all relevant link sheets, but this is a timely exersice, can some one explain me what i am doing wrong? as now it doesnt seem to be working (re-opening linked sheets i mean)

thank you very much

I have a simple calculation in cell C1 like C1=A1+B1. But B1 is sometimes returning a #N/A value, and than C1 is also displaying a #N/A value.
At C11 my calculation is counting C1 till C10, but this is not working because C1 is displaying the #N/A value. I need a formula in C1 where this cell is display a 0 in place of a #N/A value.

When I do a vlookup and the lookup value is not in the lookup table, it returns a value of N/A. Can I get the formula to return a value of 0 if the value is not in the lookup table?

I have a sheet with 2884 rows.. I am currently using the following macro (with changes for each column) to search columns AN - BG and delete cells with a value of 0 and move the columns that do not = 0 up..

for example:

0
0
0
0
5
0
0
3
0
0
0
1

would be

5
3
1

I can not delete the entire row, because the next column may not have a 0 value in the same row..

Obviously, the process I have below takes FOREVER. Is there a faster way to do this? Again, it is Column AN through Column BG and rows 6-2884 . I am going through it backwards so that I do not miss any 0's.

Dim ANcell As
Range
Dim ANCount As Long
  For ANCount = 2884 To 6 Step -1
  'Start from bottom of range, so we don't skip any rows When we've deleted one
  If Trim(Range("AN" & ANCount).Value) = "0" Then
     Range("AN" & ANCount).Delete Shift:=xlUp
  Else
  End If
  Next ANCount


I am using vlookup to return data for a particular reference number. If the
reference number is not available I would like to replace the #N/A returns
with the wording "No Response". Is this possible?
Thanks


No luck finding an answer? You could always try Google.