» 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