How to Use IF ISNA Function with VLOOKUP in Excel

Get FREE Advanced Excel Exercises with Solutions!

Today I will be showing how you can use the VLOOKUP in combination with the IF and ISNA functions of Excel.

One of the most important and widely used functions of Excel is VLOOKUP. But while using VLOOKUP, we may sometimes face errors when the lookup value doesn’t match any value in the lookup array.

The ISNA functions of Excel come in handy in these situations. ISNA in combination with IF provides us with the opportunity to search for another value if the first value doesn’t match. This is quite useful for large sets of data.


IF ISNA Function with VLOOKUP (Quick View)

Quick View of IF ISNA Function with VLOOKUP


IF ISNA Function with VLOOKUP in Excel: 3 Examples

Let’s look at some examples of using the IF and ISNA functions with VLOOKUP.

1. Using IF ISNA Function with VLOOKUP in Same Table

Here we have a data set with the BOOK Types, Names, and Authors of some books in a bookshop called Martin Bookstore.

Sample Dataset of IF ISNA Function

Steps:

  • Now first we will look for a book type of poetry. If a book type of poetry isn’t available, then we will look for a novel. A combination of the IF, ISNA, and VLOOKUP is the perfect match here.
  • The formula will be-
=IF(ISNA(VLOOKUP("Poetry",B5:D17,2,FALSE)),VLOOKUP("Novel",B5:D17,2,FALSE))

Formula of IF ISNA Function

  • See, we have got a Novel, Oliver Twist, as there was no book of Poetry.

Output of IF ISNA Function

Explanation of the Formula

  • VLOOKUP(“Poetry”,B5:D17,2,FALSE) returns #N/A error, as there was no book type called “Poetry” in the first column of table B5:D17.

Formula Explanation of VLOOKUP Function

  • ISNA(VLOOKUP(“Poetry”,B5:D17,2,FALSE)) becomes ISNA(#N/A) and it returns TRUE.

Formula Explanation of ISNA

  • IF(ISNA(VLOOKUP(“Poetry”,B5:D17,2,FALSE)),VLOOKUP(“Novel”,B5:D17,2,FALSE)) now becomes IF(TRUE,VLOOKUP(“Novel”,B5:D17,2,FALSE)) which returns VLOOKUP(“Novel”,B5:D17,2,FALSE).
  • VLOOKUP(“Novel”,B5:D17,2,FALSE) searches for a “Novel” in the first column of table B5:D17 (Book Type). After finding one, it returns the Book Name from column 2, Oliver Twist.

Formula Explanation with VLOOKUP Function

  • Therefore, IF(ISNA(VLOOKUP(“Poetry”,B5:D17,2,FALSE)),VLOOKUP(“Novel”,B5:D17,2,FALSE)) returns “Oliver Twist”.

2. Using IF ISNA Function with VLOOKUP in a Different Table but the Same Worksheet

Here we have another data set with the book records of two book stores, Martin Bookstore and Holder Bookstore.

Sample Dataset of IF ISNA Function with VLOOKUP in a Different Table

This time we will search for a poetry book in the first book shop. If we do not find it there, we will search in the second bookstore.

  • The formula will be-
=IF(ISNA(VLOOKUP("Poetry",B4:D20,2,FALSE)),VLOOKUP("Poetry",G4:I20,2,FALSE))

Formula and Output in a Different Table

See, when it does not find a novel in the first bookstore, it searches for one in the second bookstore (G4:I20) and finds one called “Ode to the Nightingale”, by John Keats.

For a detailed explanation of the formula, see example 1.

Read More: How to Use VLOOKUP Formula in Excel with Multiple Sheets


3. Using IF ISNA Function with VLOOKUP in a Different Worksheet

Finally, we have another data set with the book records of two book stores, but this time in two different worksheets.

Sample Dataset in a Different Worksheet

 

Sample Datatable of IF ISNA Function with VLOOKUP in a Different Worksheet

 

Steps:

  • First, we will search for a poetry book in the Martin Bookstore. If we do not find it there, we will search in the Holder Bookstore.
  • We enter this formula in the worksheet called “Martin Bookstore”-
=IF(ISNA(VLOOKUP("Poetry",B6:D18,2,FALSE)),VLOOKUP("Poetry",'Holder Bookstore'!B6:D18,2,FALSE))

Formula of IF ISNA Function with VLOOKUP

  • It searches for a Poetry book in the Martin Bookstore. When doesn’t find it there, searches for one in the Holder Bookstore (‘Holder Bookstore’!B6:D18), and finds one there Ode to the Nightingale by John Keats.

Output of IF ISNA Function in a Different Worksheet

Marked Result from a different worksheet

For a detailed explanation of the formula, see example 1.

Read More: 10 Best Practices with VLOOKUP in Excel


Alternative Options of IF ISNA

From Excel 2013, an alternative option of the IF ISNA function is available. This is called the IFNA function.

The Syntax of the IFNA function is-

=IFNA(value,value_if_na)

The IFNA formula to first search for a poetry book, and then search for a novel if any poetry is not available will be:

=IFNA(VLOOKUP("Poetry",B4:D20,2,FALSE),VLOOKUP("Novel",B4:D20,2,FALSE))

Formula of IFNA Function

  • Press ENTER to get the final result.

Output of IFNA Function

Read More: Return the Highest Value Using VLOOKUP Function in Excel


Download Practice Workbook


Conclusion

Thus you can use the IF ISNA function with VLOOKUP to search for a value in a table and do another thing if you don’t find the value there. Do you have any questions? Feel free to ask us.


Further Readings

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rifat Hassan
Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo