Using VLOOKUP and ISNA functions to find matching values in different sheets





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 #N/A error 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
 
img1
 
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 #NA error. Refer to the below screenshot.

Screenshot 2
 
img2
 
We can use the following functions to hide the #NAerror:

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

 
img3
 
IFNA

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

 
img4
 
IFERROR

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

 
img5
 
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))

 
img6
 



One thought on “Using VLOOKUP and ISNA functions to find matching values in different sheets

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

Leave a Reply

Your email address will not be published. Required fields are marked *


6 × nine =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>