Excel Function of the week - ISNA

  • Buffer
  • Sharebar
  • Buffer
jethro's picture

The ISNA function is an Information Function. It is is used to return information about the status of a cell, or specifically another functions results. The most common use I have for this function is to validate the VLOOKUP function. If the VLOOKUP function is looking for a value that can not be found in the lookup range, then it will return #N/A as the result. Thus it is good to wrap that function in an IF Statement using the ISNA function to replace the #N/A results with something else – e.g. a text string such as “error” or maybe a zero.

=IF(ISNA(VLOOKUP(A1,$B:$B,1,FALSE))=TRUE,"error",VLOOKUP(A1,$B:$B,1,FALSE))

clip_image001

Remember the ISNA function returns a TRUE or FALSE. These equate to a 1 or 0 so can be used in formulas such as array formulas as well.

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Zoran's picture

Wow, you do learn something

Wow, you do learn something new everyday. ISNA is new to me so thanks for pointing it out. I can already see where it can be used in a few of my spreadsheets, thanks so much for this!

Drake's picture

Ha ha pretty cool function

Ha ha pretty cool function man, thanks for sharing will try this.

kerosen's picture

Thanks for sharing this,

Thanks for sharing this, sometime I forget the excel basics, thanks for the reminder...PariuriRezultate FotbalCote Pariuri Online

ishaan99's picture

This post is a marvelous one

This post is a marvelous one as it acquainted me about the use of ISNA function in excel with a pictorial representation of the functions application with example.It acted as a self -tutor for me,therefore I praise you a lot for sharing your knowledge about excel.

eva888's picture

Great footer resources, you

Great footer resources, you gave me some inspiration for a project I am currently completing.

Jackey Worden's picture

Being a Quality Specialists,

Being a Quality Specialists, sure we work on different Excel workbook with Macros, Vlookup, different formulas. The ISNA fundtion is really helpful in VLookUp and I can really understand your explanation on this one. It adds up to my understanding. Nice job!

missjanicedemesa's picture

at first I didn't know about

at first I didn't know about this excel function because I honestly don't know yet about this. I only knew the basic that is why i need to learn excel 2010.

lee's picture

Its a good function,working

Its a good function,working fine thanx......