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?
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

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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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
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.
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.
NorieVBA ExpertCommented:
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.:)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.