Hi Ben

If you are attempting to locate a lookup that is not in your array's lookup
column you will get an answer of #N/A.

There is a function within Excel - ISNA(value) - that can address this issue
and yes you could address it with an IF statement. The ISNA function is
"TRUE" if the value is #N/A or "ISNA."

So really your goal as I understand it is when the lookup is not in my array
- that is, whenever I get the #N/A answer - I want to show the term "MISSING"

Your IF statement (which is made up of a logical test and then a result if
that test is true and a result if that test is false - IF(logical test, value
if true, value if false) - would like this:

Your logical test would be ISNA(value) - where the value is your lookup
statement.
Your value if ISNA is true would be "MISSING"
Your value if ISNA is false would be your lookup statement

=IF(ISNA(VLOOKUP(A14,[Book2.xls]T01!$A$8:$AB$731,28,FALSE),"MISSING",VLOOKUP(A14,[Book2.xls]T01!$A$8:$AB$731,28,FALSE))

Try that, it should work.

BJ

"Ben" wrote:

Put simply, Yes.

However, i was just wondering if there was some way to change the output of
vlookup when it is unable to find any data.

For example, i just want to change the "0" when no data is found to say
"Missing".

I checked on the MS office site regarding If statments and looked at other
posts regarding dynamic statments and found that i am having problems.

I would assume that this statment would work as the MS site explains...
http://office.microsoft.com/en-us/ex...091181033.aspx

I beleive i correctly perform an IF statment...

If(A10="0","Missing","")

However, when i throw on the end (and it is the linking bit)...

=If(A10="0","Missing","")*VLOOKUP(A14,[Book2.xls]T01!$A$8:$AB$731,28,FALSE)

So, what am i doing wrong and why please?

Regards,

Ben

"BJ" wrote:

So Ben have all your questions been answered or do you still need some help?