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)

**Table of Contents**hide

**Download Practice Workbook**

**Excel ISNA Function: Syntax and Argument**

**Summary**

- Takes a Value as the argument, and returns
**TRUE**if it is a**#N/A**error. Otherwise, returns**FALSE**. - Available from Excel 2003.

**Syntax**

The Syntax of the **ISNA** function is:

`=ISNA(value)`

**Argument**

Argument |
Required or Optional |
Value |

value | Required | The value that the ISNA function checks whether a #N/A error or not. |

**Return Value**

Returns a Boolean value, **TRUE **or **FALSE**. **TRUE** if the value is a **#N/A** error, **FALSE** otherwise.

**IF ISNA Function with VLOOKUP: 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 the Same Table**

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

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",B4:D20,2,FALSE)),VLOOKUP("Novel",B4:D20,2,FALSE))`

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

**Explanation of the Formula**

returns`VLOOKUP("Poetry",B4:D20,2,FALSE)`

**#N/A**error, as there was no book type called**“Poetry”**in the first column of table**B4:D20**.

- .
becomes`ISNA(VLOOKUP("Poetry",B4:D20,2,FALSE))`

and it returns`ISNA(#N/A)`

**TRUE**.

now becomes`IF(ISNA(VLOOKUP("Poetry",B4:D20,2,FALSE)),VLOOKUP("Novel",B4:D20,2,FALSE)`

)which returns`IF(TRUE,VLOOKUP("Novel",B4:D20,2,FALSE))`

`VLOOKUP("Novel",B4:D20,2,FALSE)`

.searches for a`VLOOKUP("Novel",B4:D20,2,FALSE)`

**“Novel”**in the first column of table**B4:D20 (Book Type).**After finding one, it returns the**Book Name**from column 2,**Oliver Twist**.

- Therefore,
returns`IF(ISNA(VLOOKUP("Poetry",B4:D20,2,FALSE)),VLOOKUP("Novel",B4:D20,2,FALSE))`

**“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.

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 book store.

The formula will be:

`=IF(ISNA(VLOOKUP("Poetry",B4:D20,2,FALSE)),VLOOKUP("Poetry",G4:I20,2,FALSE))`

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.

**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.

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",B4:D20,2,FALSE)),VLOOKUP("Poetry",'Holder Bookstore'!B4:D20,2,FALSE))`

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’!B4:D20), **and finds one there.

**Ode to the Nightingale** by John Keats.

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

**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))`

**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.