» Retrieving Smallest Nth Value Match Within a List
CATEGORY - Lookup Formulas
VERSION - All Microsoft Excel Versions
Columns A & B contain matching pairs of numbers and letters.
Each number in column A may appear multiple times, on each occasion with a different corresponding letter.
We want to search the data and retrieve the letter corresponding to the nth match of specified numbers from column A. Column C specifies the number to search for, and column D specifies the particular match required.
Solution:
Use the INDEX, SMALL, IF, and ROW functions as shown in the following Array formula:
{=INDEX($B$2:$B$12,SMALL(IF($A$2:$A$12=C2,ROW($B$2:$B$12)-ROW($B$2)+1),D2))}
Book Store:
Recommended Books:
- Wall Street Journal Guide to Understanding Money and Investing
- Managing by the Numbers: A Commonsense Guide to Understanding and Using Your Company's Financials: An Essential Resource for Growing Businesses
- Financial Statements: A Step-By-Step Guide to Understanding and Creating Financial Reports
- AWAKEN THE GIANT WITHIN : HOW TO TAKE IMMEDIATE CONTROL OF YOUR MENTAL, EMOTIONAL, PHYSICAL AND FINANCIAL
- Definitive Guide to Excel VBA
- Fish! A Remarkable Way to Boost Morale and Improve Results
No comments have been submitted.

