In this article we will learn how to use VLOOKUP & ISNA functions to find matching values in different sheets in excel 2010.

There are many error-trapping functions like **ISNA, IFNA, IFERROR, ISERROR** which we can use in Excel. These help the formulae to run smoothly.

To hide the **#NA** error we can use the** Vlookup and ISNA** functions together.

First, let us understand the reasons why the vlookup error **#N/A** occurs:

- The data we want to lookup does not exist in our table.
- The table is not set in ascending order.

Vlookup looks up a value in the selected range of cells in the left-most column & returns the value in the same row in the index-number position.

**Syntax =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)**

Lets break down the arguments of the Vlookup function & understand each one.

**lookup_value:**The value you want to look for**table_array:**The table of data contains information from which you want to return

the output. Table array should contain at least two columns of data. The first columncontains the lookup values. These values can be text, numbers, or logical values.**col_index_num:**It contains the data from which you want the information.**range_lookup:**Range lookup is a logical value which indicates to the Vlookup function to find an exact match or approximate. Table must be sorted in ascending order. The choices are true or false. True for Approximate match, False for Exact match

In a spreadsheet, we have Emp id, Sales Person & Units sold in column A, B & C respectively.

**Screenshot 1**

Sometimes we need to use the **Vlookup** function in a different sheet other than the current sheet.

In sheet2, we are applying a **Vlookup** where B3 contains the **Lookup Value** i.e. **110** which is not present in the sheet **“Datasheet”** column A. In this case, cell C3 will return the **#N/A** error. Refer to the below screenshot.

**Screenshot 2**

We can use the following functions to hide the #N/A in Vlookup:

**1) ISNA**

**Syntax =ISNA(value)**

**2) IFNA**

**Syntax =IFNA(value,value_if_na)**

**3) IFERROR**

**Syntax =IFERROR(value,value_if_error)**

**4) ISERROR**

**Syntax =ISERROR(value)**

Lets review each function one by one –

**ISNA**

- =IF(ISNA(VLOOKUP($B$3,Datasheet!$A$4:$C$10,3,0)),”Not

Exist”,VLOOKUP($B$3,Datasheet!$A$4:$C$10,3,0))

**IFNA**

- =IFNA(VLOOKUP($B$3,DataSheet!$A$4:$C$10,3,0),”Not Exist”)

**IFERROR**

- =IFERROR(VLOOKUP($B$3,DataSheet!$A$4:$C$10,3,0),”Not Exist”)

**ISERROR**

- =IF(ISERROR(VLOOKUP($B$3,DataSheet!$A$4:$C$10,3,0)),”Not

Exist”,VLOOKUP($B$3,DataSheet!$A$4:$C$10,3,0))

i need formula for match two cell value from sheet 1 to Sheet2 if they match i want to call value from Sheet2

for Exp.

in Sheet1 in Sheet2

# col1 Col2 col3 # col1 col2

1 1/1/2014 Item1 2 1 2/1/2014 item1

2 1/1/2014 Item2 2 1/1/2014 item1

3 1/1/2014 Item3

i want to replace in col3 which row no is found

I used

‘=IF(ISNA(VLOOKUP($B$3,Datasheet!$A$4:$C$10,3,0)),”Not

Exist”,VLOOKUP($B$3,Datasheet!$A$4:$C$10,3,0))’

as a model, and it worked fine. Thanks!!

sir we have one problem plz solve this

,=INDEX(performance!$E$2:$L$301,MATCH(today!$E2&today!$F2&today!$G2&today!$H2&today!$I2&today!$J2&today!$K2,performance!$E$2:$E$301&performance!$F$2:$F$301&performance!$G$2:$G$301&performance!$H$2:$H$301&performance!$I$2:$I$301&performance!$J$2:$J$301&performance!$K$2:$K$301,0),8)

this formula was given exject but we want +/- 3% range between plz solve my problem

thank u Advance

Hi Anand,

Please post your query @ http://www.excelforum.com, you will get appropriate reply immediately.

Thanks

Site Admin

sir we have one problem plz solve this

,=INDEX(performance!$E$2:$L$301,MATCH(today!$E2&today!$F2&today!$G2&today!$H2&today!$I2&today!$J2&today!$K2,performance!$E$2:$E$301&performance!$F$2:$F$301&performance!$G$2:$G$301&performance!$H$2:$H$301&performance!$I$2:$I$301&performance!$J$2:$J$301&performance!$K$2:$K$301,0),8)

this formula was given exject but we want + or – 3% range between plz solve my problem

thank u Advance