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





Problem:

Column B in sheet1 contains numbers. Columns B:C in sheet2 contain numbers and their matching text.
How could we find the text in sheet2 matching each number in sheet1?

Solution:

The following formula will find the text in sheet2 matching to the number in cell B13 in sheet1:
=IF(ISNA(VLOOKUP(B13,$B$20:$B$24,1,FALSE)),”Not Found”,VLOOKUP(B13,$B$20:$C$24,2,FALSE))
In case the number is not found in sheet2, using ISNA function, the formula will return Not Found.

Example:

Sheet1

Number__Result_______Formula
1_______One__________=IF(ISNA(VLOOKUP(B13,$B$20:$B$24,1,FALSE)),”Not Found”,VLOOKUP(B13,$B$20:$C$24,2,FALSE((
9_______Not Found____=IF(ISNA(VLOOKUP(B14,$B$20:$B$24,1,FALSE)),”Not Found”,VLOOKUP(B14,$B$20:$C$24,2,FALSE((
3_______Three________=IF(ISNA(VLOOKUP(B15,$B$20:$B$24,1,FALSE)),”Not Found”,VLOOKUP(B15,$B$20:$C$24,2,FALSE))

Sheet2

Number__Text
4_______Four
5_______Five
3_______Three
6_______Six
1_______One
Screenshot // Using VLOOKUP and ISNA functions to find matching values in different sheets
Using VLOOKUP and ISNA functions to find matching values in different sheets



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 *


two × 2 =

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>