» Checking If Matching Numbers Exist in Different Ranges
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
Column A in Range1 contains numbers. Columns A & B in Range2 contain numbers and their matching text.
For each number in Range1, we want to find a match in Range2 and then copy that number's corresponding text to column B for Range1.
Solution:
Use the IF, ISNA, and VLOOKUP functions as shown in the following formula:
=IF(ISNA(VLOOKUP(A3,$A$9:$B$14,1,FALSE)),"Not Found",VLOOKUP(A3,$A$9:$B$14,2,FALSE))
Book Store:
Recommended Books:
- Essentials of Accounting and Post Test Booklet 8, Eighth Edition
- How to Pay Zero Taxes (Annual)
- East of Eden (Oprah's Book Club)
- Real Estate Finance and Investments (Real Estate Finance and Investments, 11th Ed)
- The Sweet Potato Queens' Big-Ass Cookbook and Financial Planner
- Personal Finance for Dummies
Multiple Sheets
nsleiter
I understand how one can look at multiple ranges in a given worksheet, but is it possible to use this or another formula to look at multiple worksheets in a given workbook. Please advise.
[SOLVED] Checking If Matching Numbers Exist in Different Ranges
nsleiter
Is it possible to do this over multiple sheets? I trying to develop a formula that will search all sheets in a workbook for a desired value, in this case a date.
Thank-you
gmontag
I refined the example a bit and worked out multi-sheet lookups:
=IF(ISNA(VLOOKUP(A1,SHEET1!A:A,1,FALSE)),"Not Found",IF(ISNA(VLOOKUP(A1,SHEET2!A:A,1,FALSE)),"Not Found",VLOOKUP(A1,SHEET1!A:A,1,FALSE)))
Where:
A1 = text you wish to match for
SHEET1 = worksheet to search
A:A = column on SHEET1 to search in
You can continue to add other sheets up to 7 levels if you wish.
