|
» Retrieving the Closest Larger and Closest Smaller Values from a List When There Is No Exact Match
CATEGORY: Excel Formula and Function |
VERSIONS: All Microsoft Excel Versions |
|
Problem: Listed in B2:B5 are values to look up in column A. We want to create formulas to look up each value and, in the case of there being no exact match, return the closest larger and the colsest smaller number that is in the list. Solution: To find the closest larger number or an exact match in column A, use the SMALL and COUNTIF functions as shown in the following formula: =SMALL($A$2:$A$7,COUNTIF($A$2:$A$7,""<""&B2)+1) To find the closest smaller number or an exact match in column A, use the LARGE and COUNTIF functions as shown in the following formula: =LARGE($A$2:$A$7,COUNTIF($A$2:$A$7,"">""&B2)+1) Example: List1 2 5 4 10 1 6 Value to look______Closest larger value____Closest smaller value 3__________________4_______________________2 5__________________5_______________________5 7__________________10______________________6 9__________________10______________________6 |
| Screenshot // Retrieving the Closest Larger and Closest Smaller Values from a List When There Is No Exact Match |
  |
|
RATING:
3.67
VIEWS: 10202 |
||||
Sponsors:
 |
Book Store:
Recommended Books:
- Financial Shenanigans : How to Detect Accounting Gimmicks & Fraud in Financial Reports
- Quantitative Methods in Derivatives Pricing: An Introduction to Computational Finance
- How to Use Financial Statements: A Guide to Understanding the Numbers
- Financial Reporting and Analysis (2nd Edition)
- AWAKEN THE GIANT WITHIN : HOW TO TAKE IMMEDIATE CONTROL OF YOUR MENTAL, EMOTIONAL, PHYSICAL AND FINANCIAL
- Microsoft Word Version 2002 Inside Out
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.
