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

by  About
       

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



Leave a Reply

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


two × = 6

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>