Free Microsoft Excel 2013 Quick Reference

Vlookup Showing #N/A!

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?

Post your answer or comment

comments powered by Disqus

Does anyone know the formula for not showing #N/A if a vlookup doesn't return a number



Hi, all.
I got a problem with usin g vlookup. when it cannot find the data, it always show #N/A, I want to let it show nothing or 0 if it cannot find the data. my vlookup code is like this:
Cells(7, "GO").Select
        StoreVLoopUpFormula = "=  
        ActiveCell.FormulaR1C1 = StoreVLoopUpFormula
        selection.NumberFormat = "0.00"
        selection.AutoFill Destination:=range(Cells(7, "GO"), Cells(GrandTotalRowCount, "GO")),
it is look up data from two worksheets named 200mm and 300mm. i am noe sure whether my code is right or not , but it still can work. Please help me with this.
Greatly appreciated any help.!

I have a prepopulated order form which uses VLOOKUPS. Until the user
completes a line, VLOOKUP produces a not available result #N/A. I would like
to include an order total field which gives the user a constantly updated
order total, however the #N/A is preventing me from doing so. As long as
there is a #N/A in the colum, the sum field only shows #N/A

Is there a way to get the sum to work?

Col "A" Col "B"
8/18/06 Vlookup formula (result is $5)
8/21/06 Vlookup formula (cell not updated yet so result shows
Sum() (result shows #N/A)

Col "B" is linked to another worksheet. 8/21/06 row will get the result of
"#N/A" because the vlookup formula won't get updated until 8/21/06. So the
sum total also gives me the result of "#N/A". How can I get the sum total
result of ($5) on Col "B" row 3..



Col "A" Col "B"
8/18/06 Vlookup formula (result is $5)
8/21/06 Vlookup formula (cell not updated yet so result shows
Sum() (result shows #N/A)

Col "B" is linked to another worksheet. 8/21/06 row will get the result of
"#N/A" because the vlookup formula won't get updated until 8/21/06. So the
sum total also gives me the result of "#N/A". How can I get the sum total
result of ($5) on Col "B" row 3..



I have the a code, which runs fine if vlookup returns #N/A but if it returns a value, i get a Type mismatch error,

MyVar = Application.VLookup(Range("F9"), Range("V7:W87"), 2, False) 
Sheets("Input").Range("G9").Value = MyVar 
If Sheets("Input").Range("G9") = CVErr(xlErrNA) Then 
    MsgBox "Enter the KMAT and KMAT Description in the Table", vbCritical, "Missing Data" 
    Exit Sub 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
how do i modify the if condition as not to get an error when Vlookup returns a value.

Thanks in advance.

Hi all

On the attached spreadsheet A14 and B20 appear the same but VLOOKUP returns N/A. The CELL "FORMAT" function returns "G" for both cells, but the "EXACT" function returns FALSE.

If somebody could explain what is causing them to be different I woud be extremely grateful.

Many thanks

I would normally use vlookup like this -


I have a worksheet which is a standard 26 rows long and sometimes all 26 rows will be used sometimes only 5 will be used.

I want to run the vlookup down column but I get a #N/A if the information in B15 doesn't appear in the range BO4:BP2785.

Is there a way around this without using if and a second vlookup?

So that If the first vlookup returns #N/A it returns 0, if it doesn't it carrys out the second vlookup.

An example would be appreciated.

Hi, is there a way to add to the following formulas "IF CELL P11=0, THEN 'N/A'"?

These are the formulas where I need this to happen:

1) =IF((F15+I15+L15+P15+S15),(Q19)/(F15+I15+L15+P15+S15),0)

2) =IF(P11,(B16)/P11,0)

3) SUM(B17,F17,I17,L17,P17,S17)

All the formulas above are tied one way or another to cell P11. So just to recap, if P11 = 0, then 1, 2, and 3 should show N/A. Currently, 1, 2, and 3 show 0 when P11 is 0. One problem I see at this point is that item #3 is really a sum of a bunch of items #2. Is that going to cause a problem in item #3 if items #2 show text instead of numbers? Please help, I just barely get by in Excel.

Thank you,
Sam Manzella

I'm an absolute newcomer to Excel 2007 VBA (Started on my first
project 24 hours ago).

I'm trying to parse some data I have downloaded. I use VLOOKUP to
search for specific strings. It works fine if the string is present,
but if the string is not present then VLOOKUP returns #N/A which seems
to cause the execution of my routine to be halted. I have to tried to
wrap IsNA() around VLOOKUP, but my routine is still halted. I have
also tried to add "On Error Resume Next" but that seems to cause the
following check (IsNA) to fail (i.e. #N/A is not detected). I assume
the #N/A value gets wiped out by the "On Error Resume Next Statement".
How do I get Execl to continue executing when #N/A is returned, but at
the same branch out if #N/A was returned?

Here is what I want to do: I want to search my downloaded data for a
specific string. If the string is present then I want to grap the
associated data (and store them somewhere). If the string is not
present then I want to go and search for the next string and the whole
thing repeats itself until I get thru all my patterns.

I have a worksheet with 8 cells that link to 8 cells on a different
worksheet. 5 of these always pick up the value of the linked cell. The other
three show "N/A". When i open the source file, the problem cells update with
the values OK. But then when closed the N/A appears back.

I have tried to identify what is different with the three "rogue" cells on
the source sheets. Could it have something to do with them being merged or
locked, protected? I feel it is something to do with one of these but there
are other cells (e.g. merged) that are updating OK.

Any suggestions gratefully received! - thanks in advance.

Hi All,

I attached my files here.. Can anyone help me on the error N/A. Why it will return NA on my unit price?? I had checked everything.. why stil appear error

Kindly help...

Thanks V much

I am using the following formula:


Basically that will ignore any #N/A's in my row and give me a sum.

But if the sum is $0.00, how can I show #N/A instead of $0.00 for the sum?

This is so I can chart the grand total without the chart showing $0.00 for these dates.

Thanks for any help.


Hi all
i'm trying to sum with multipole criterial by sumproduct. but showing #N/A error. I'M attched the file for better understand my problem.

I often get this problem. A long list contains partnumbers (some numerical,
some text) and I need to get price from a pricelist. Even though it is an
exact match I get #N/A in return. Only way to get it working is to edit the
source cell (F2+Enter).
I have tried TRIM() and VALUE() but since the list contains both types I get
#VALUE in return for all text cells.

Is there a way to "transform" source column so that it will work 100%?

Hi everyone,

Im new to formulas in excel.
And this is driving me crazy..
I had been search high and low in all forums for 5hrs!!
I tried to input by SUMPRODUCT, but i still cant solve my problem!


Here's the problem...

I am using this formula in Product Balance Update.xls,Tsuchiura worksheet that update data from A-Tsuchiura.xls, INVOICE worksheet.

When both workbooks are is opened, all linking are fine.

But upon A-Tsuchiura.xls is closed, my linking in Product Balance Update.xls,Tsuchiura worksheet will shows #N/A.

I had tried many ways for sumproduct
1) =SUMPRODUCT(--('SomfactoryDistributionShipment[A-Tsuchiura.xls]INVOICE'!$B$14:$B$97=A3),--('SomfactoryDistributionShipment[A-Tsuchiura.xls]INVOICE'!$G$14:$G$97))

2) =SUMPRODUCT(('SomfactoryDistributionShipment[A-Tsuchiura.xls]INVOICE'!$B$14:$B$97=A3)*('SomfactoryDistributionShipment[A-Tsuchiura.xls]INVOICE'!$G$14:$G$97))

I had read through this forum that sumproduct should work in closed workbook but I still cant get it why this error ocurr..

Please refer to attached to have a clearer picture of error

please help me solve this issue please??

I am performing a vlookup and looking for data in another worksheet. If the
worksheet doesn't contain a match the vlookup brings back a "#N/A" like it is
suppose to. However if it doesn't find a match I want it to show what is in
cell "I4" instead of "#N/A".


Hello, my vlookup is returning n/a values which is ok. I still want the
column to add the totals of the vlookups but at the moment the total cell is
showing n/a because some of the vlookups are returning n/a values. Can I
still get a total sum of the column even though some of the vlookups are
showing n/a

Just a head's up on ISERROR

It will mask all errors, not just the #N/A and may hide something you don't
want hidden.

Preferable to use the ISNA function.


Gord Dibben Excel MVP

On Tue, 5 Jul 2005 11:41:21 -0500, swatsp0p
<> wrote:

>If we use ISERROR to trap error messages, we can prevent them from
>showing up, as such:
>The first part of the formula looks to see if the result is an error
>and if so, returns the empy string "". If no error is found, the
>result of the formula is returned.

I'm using excell 2003 and on one tab I have a list of worktypes with volumes received and I'm using vlookup to transfer the volumes over to departermental worksheet. What I wondered is there a way of instead of showing N/A if a worktype on departermental list does not appear on the main tab it can show 0 so it can still auto sum at the bottom of the tab and save me the troblem of amending them to 0 as when this is finished being built there will be 5 tabs with about 140 worktypes and showing 10 weeks worth of data. I have attached a small exampe of what I mean. This would also be usefull on another sheet I have for staff and the hours worked on that day.

Thanks for any help

Here is the situation:

I have two worksheets: one that shows a list of all customers (along with other data), and one that shows a list of all customers that we have been in contact with this year. What I am trying to do is exclude the customers that we have not been in contact with this year from showing on the first worksheet. On both worksheets each customer has a corresponding number (this number is the same on each worksheet per customer)

So far I have tried using the VLOOKUP function in order to make it so that if a number in column A in Worksheet A matches with a number in column A in Worksheet B, it puts that number into another column (we'll call this column "Lookup") on Worksheet A. Here is a sample what the function looks like: =VLOOKUP(B1:B2124,WorksheetB!A1:A852,1,FALSE). What it is doing right now is showing this number correctly in the "Lookup" column for the customers that are on Worksheet B, and for the customers that are not on Worksheet B it is returning N/A.

If I could just get Worksheet A to hide every row that has N/A in the "Lookup" column that would solve my issue. Is there a way to do that? If anyone has any suggestions it would be greatly appreciated!

Hi all, I am using vlookup in a table that I only want to return if the result is exact.

Therefore if no exact match is found then I get #N/A.

However I want to join several cells text strings using these lookup values such as


column A is manually inputted
column B is from a lookup value and sometimes returns #N/A

When using either A&B or concatenate for column C, only the #N/A is displayed if there is one in column B.

What I would like to see is, if there is a vlookup value to display that as in the first row.
If no exact match is found I would like it to show anything as long as column A value is there, i.e only 'you' or even 'you#N/A'.

I hope this is clear enough.

Thanks for any help given.

I have used Vlookup to look for the information, but it shows #N/A for a cell that has no value. I want to remove it. Please help.


I am trying to write a macro for vlookup. However, some of the values will return #N/A (i.e. the key does not show up on the source).

I am trying to write something if vlookup return #N/A, display as N/A else display the vlookup value. However, i keep getting error. Can anyone help? Thanks!

Heres my code:

Sub VlookupAmount()

sheetName = InputBox("Input Sheet Name")

Dim i As Integer
Dim rngIn As Range

Set rngIn = Worksheets(sheetName).Range("A:C")

'Count number of rows in column A
rowCount = Application.WorksheetFunction.CountA(Range("A:A"))

' For loop - from row 2 to last row
For i = 2 To rowCount

' Vlookup Function
If Cells(i, 6) = "ABC" Then

Cells(i, 2).Value = 0


If WorksheetFunction.IsNa(WorrksheetFunction.VLookup(Cells(i, 1), rngIn, 2, False)) Then

Cells(i, 2) = "N/A"


Cells(i, 2).Value = WorksheetFunction.VLookup(Cells(i, 1), rngIn, 2, False)

End If

End If

Next i

End Sub

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