Free Microsoft Excel 2013 Quick Reference

How to remove #n/a error in excel vlookup b/c value is not found?

Morning all,

I have this formula; =VLOOKUP($A2,Name!$A:$C,2,FALSE) to join to spreadsheets, but in some cells I'm getting an #N/A error. Is there a way to remove or just return a blank cell?

Many thanks


Post your answer or comment

comments powered by Disqus
Does anyone know how to detect #N/A? if it finds, then return 0 value.
Does anyone have any suggestions/
Thank you in advance

I am trying to auto number a column in excel so I do not have to do it. I
tried to Format the cell but i don't see an option to do so.

hi dear

i have this formula in one macro-

    Dim LBZ As Long
        LBZ = Sheets("temp").Cells(Rows.Count, "R").End(xlUp).Row
        Range("C2:C" & LBZ).Formula = "=INDEX(JCR!$A:$S,MATCH(temp!$R2,JCR!$F:$F,FALSE),1)"
let us assume column R has 20,000 rows of data. this above formula may not fill up all 20000 rows with a value. there would be some blanks or #N/A error in between because the indexmatch formula does not find a match in data. i want to do another formula for this blanks in the same column upto the last row of column R. how can i do it without overwriting the already existeing formula in column C ?

plz help dears and thanks you very lot for kind helps.


I am new to this site today and am here because I am getting a strange n/a# error when index matching between two sheets in the same work book.

Sheet 1 is like a master worksheet and pulls info from all the other sheets, so i have used the index match formula in all the columns without fail... until now .

Sheet 2, column B - List of the Site ID numbers
Sheet 2, column G - List of expenditure from each site over last month

Sheet 1, column E - List of same Site ID numbers
Sheet 1, column AF - where I'd like to matched info to appear!

I simply want to pull the expense value from column G of sheet 2, match the two Site ID and put in in column AF of sheet 1.

I have used =INDEX('sheet2'!G:G,match('sheet1'!E:E,'sheet2'!B:B,))

This returns the n/a# error, although I cannot see any difference between this and the other index match formulae in other columns which worked just fine .

I have tried $G:$G, $E:$E etc and specific ranges (E1:E985, G1:G985 etc) instead of just the column headers, but nothing is working. So I bow down before the experts and pray for your assistance!

Anyone knows how to remove Alt+Enter characters in a worksheet?

Thank you.

How do I hide a worksheet in Excel and use a password to un-hide the worksheet?

how to automate opening a password protected excel file? e.g. a .xls that
has a password set in the security tab.

How do I change a column in Excel from upper case to lower case?

How to remove Applied Conditional Formatting in a Copied Sheet(s) retaining Format?

Dear Forum,

I am creating several CopY sheets from a Parent WorkSheet and these Copied SHeets are without any Formulas but only have values and exactly the same formatting as the Parent WorKsheet...

I have managed to get the Copied Sheet with Values and the Formats but how do I remove the conditional formatting so that there are not formulas in the conditional formatting box as well as retain the formatting which was happening due to the Condtional Formatting..

I know it sounds contradictory however never know what all is possible?

If its possible then what code I need to add during the Copying Operation and also exactly where do I add it to not disturb the Conditional FOrmatting of the Parent WorkSheet.

Warm Regards

How to use the LOOKUP function in Excel

... searches for in the first vector. Lookup_value can be a number, text, a logical value ... The array form of LOOKUP is similar to the HLOOKUP and VLOOKUP functions. The difference is ...

I am using the following array formula to copy certain text from another cell:=INDEX(E1:E100,MATCH(1,IF(A1:A 100=Selections!J4,IF(ISNUMBER(MATCH(E1:E100,{"F ","U"},0)) ,1)),0))I am getting the #N/ A error in the cell when the source cell contains something other than "F" or"U". I have tried to wrap the ISERR command around the formula but this does not seem to work. Is there another way of getting rid of the #N/A and just leaving a blank cell?Thanks,James

How to use dates and times in Excel

Describes how to use the built-in date and time functions in Excel to perform complex date ... If you want to use a custom format instead, you must enclose the hours parameter of ...

Does anyone know how to remove (via formula or VBA code) unknown Character returns?
I've tried =trim =clean =substitute, but nothing works. I'm just trying to remove the spaces, but I don't know what type of character return (or other code) is in the cell that I'm trying to clean.

Please help.


I appear to be getting an N/A error in Excel, l cannot work out why this is.

This is formula:


Probably the definitive source of Email help available.

"Mrs Doubtfire" wrote:

> I want to have a hyperlink in excel that when clicked will open up MS
> outlook, with the email address in the address bar, and the file with the
> hyperlink in the attachments box.
> Many thanks

I need to be able to automatically create a chart in Excel. The chart is
basically a 4 quadrant scatter diagram:
x | x
| x
x |
| x

The datapoints are determined by something like:
Data | X | Y
Data1 25% 15002
Data2 63% 32404
Data3 51% 98011
.. . .
.. . .
.. . .
Data(N) % #

I've been plotting all this out by hand using the scatter chart but as I get
more and more datapoints the more time it takes which is why I'm looking for
a way to automate it. The solution created needs to be flexible enough to
work with a small dataset all the way up to a large dataset.


--It looks like the formatting is being changed when I submit this question
so the quadrant diagram isn't really showing up correctly.

you can also answer directly at which would be better

How do I create a formula in Excel that will countif or sum if before or
after a specific date? Whenever I put the actual date in the formula it
works, but when I reference to the cell the date is in it returns 0.

Hi ,
how to get the row index in excel sheet when changes done in a any cell/any number of cells of a specific row using macros.

How to get the row index in excel sheet when changes done in a any cell/any number of cells of a specific row using macros.
Its urgent plz help me

How To Use Date And Times In Excel For Mac Microsoft Knowledgebase

... 06 represents the date and time 7/8/1992 1:26:24 A.M. To help you perform complex date and time calculations, Microsoft Excel includes many built-in date and time functions.


i'm currently working on a database, where I want to eliminate #N/A and 0, in order to calculate averages, medians, kurtosis and skewness.

I understand how to eliminate #N/A into " ", but so far I'm stuck at combining the formula where I can also eliminate 0 and change it also into " ".

=IF(ISNA(VLOOKUP($$$;FALSE));"";VLOOKUP($$$;FALSE)) This helps me to eliminate #N/A, but not the 0.

Please help.

Greetings and thanx from holland.

How can I hide a column in Excel that others I send the sheet or workbook to
cannot unhide, but can still add/modify other information in a sheet or

I am trying to figure out how to automatically colour a cell in excel if my "IF" statement turns out to be negative. For example I have a list of prices in excel, and if they are $50 higher or $50 lower than the targeted price I need that to be flagged somehow in excel (preferably by filling it in with a different colour). Currently I am going through the prices manually and this just takes up too much time!! Help me!

I am using JAVA to retrieve data to an excel sheet.
The data is generated in the excel sheet but when I open that excel sheet a
message comes saying
"queries are used to import external data into an excel sheet but harmful
queries can be used to access confidential information or write info back to
the database."
"This workbook contains queries to external data that refresh sutomatically".
Can anyone tell me how to get rid of this message?
how to disable automatic query refresh in excel?

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