Reader Level:

IFNA VlookUp in Excel 2013

By Megha Goyal on Aug 30, 2012
In this article we will discuss how to use the IFNA VlookUp in Excel 2013 to replace the data with an error message if an error occurs.

We know that the VLookUp function is used in every version of Excel. But here we are also using the concept of IFNA in VLookUp which means if an error occurs then replace it with a string message. We know that the VLookUp function is used to search for a value on the basis of a unique identifier.

Let's have a look at the following steps.

Step 1

Go to Excel 2013.

Step 2

Now select the blank workbook template from the available templates.


Step 3

Now suppose we have a sheet with the following data:


Step 4

Suppose we have some column values for which we want to lookup the values in the table array like this:


Step 5

Now we will use the VLookUp function as follows to lookup the values:


The first parameter is the lookup value; in other words, the value that needs to be found in the first column of the table and the second parameter is the array table in which the value needs to be located and the third parameter is the column index number which means the column index in the table array from which we want to return the value.

Step 6

The  output will be like this:


Step 7

Now to see the other lookup values just select the first resultant cell and drag the cursor onto the hotspot, as in:


Step 8

The output will be like this:


Step 9

Here the cells displaying the #N/A values (error values) we will replace using the following formula:


Step 10

The output will be like this:




JetBrains ReSharper Ultimate
.NET code analysis, refactorings, navigation, unit testing, code coverage and profiling in Visual Studio, a bonus for C++ devs, bundled at 50% off.

Trending up