|
» Using VLOOKUP and ISNA functions to find matching values in different sheets
CATEGORY: Lookup Formulas |
VERSIONS: All Microsoft Excel Versions |
|
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 |
  |
|
RATING:
3.29
VIEWS: 66773 |
||||
Sponsors:
 |
Book Store:
Recommended Books:
- Analyzing Markets, Products, and Marketing Plans
- Microsoft Outlook Version 2002 Step by Step (With CD-ROM)
- Seven Habits Of Highly Effective People
- Final Accounting: Ambition, Greed and the Fall of Arthur Andersen
- The South Beach Diet: The Delicious, Doctor-Designed, Foolproof Plan for Fast and Healthy Weight Loss
- Retire Young, Retire Rich
Related MS EXCEL TIPS:
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
