Removing #N/A in Index formula

Good Day Experts,

When the following formula does not have a value it returns #N/A. How can I get excel not to display the #N/A?  


Let me know

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
You can use the IFERROR function.
=IFERROR(INDEX(list1,MATCH(IFERROR(VALUE(C2),C2),INDEX(list1,,3),0),4), "")

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProfessorJimJamMicrosoft Excel ExpertCommented:
you can do it with IFNA this function is new to Excel 2013, so is not available in earlier versions of Excel.

iferror will bypass all error, if encountered in formula

=IFNA(INDEX(list1,MATCH(IFERROR(VALUE(C2),C2),INDEX(list1,,3),0),4), "")
BeeyenAuthor Commented:
Professorjimjam - thank you for the information
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

ProfessorJimJamMicrosoft Excel ExpertCommented:
you are welcome Beenyen.

was my answer helpful to you?   because, i can see you accepted someone's answer, while thanking me :)
BeeyenAuthor Commented:
It is good to know but I do not have 2013 so I used =IFERROR.  Thanks
ProfessorJimJamMicrosoft Excel ExpertCommented:
ok. thanks for clarification.
BeeyenAuthor Commented:
I should have at least provided you with some points for responding but had already tried the other answer and then respond.  Thanks again.
ProfessorJimJamMicrosoft Excel ExpertCommented:
no problem with the points, what matter is, you have your solution.

however, based on my experience, IFERROR bypasses the ERROR control in Formula, for example if your formula would result a #Value error , sometimes is caused due to the inconsistency of formula range inputs, becuase if the ranges are not equal in numbers of rows or columns then it will trigger VALUE error.

so, in this case when you do not have Excel 2013,   there is a very good use of another function called ERROR.TYPE

to wrap up your formula with ERROR.TYPE , here is the example

=IF(ERROR.TYPE(INDEX(list1,MATCH(IFERROR(VALUE(C2),C2),INDEX(list1,,3),0),4))=7,"","Not N/A error, Check the formula")

see also the attached example file.

in cell L6  i used match with N/A error and it returned blank. and second example in cell L7  where result of formula dividing a value over zero result /DIV error, the formula is asking to check the formula becuase it is not an N/A error.
NorieAnalyst Assistant Commented:
Pre-2013 you could use ISNA.

=IF(ISNA(INDEX(list1,MATCH(IFERROR(VALUE(C2),C2),INDEX(list1,,3),0),4)), "",INDEX(list1,MATCH(IFERROR(VALUE(C2),C2),INDEX(list1,,3),0),4))

Obviously a bit cumbersome.:)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.