Retrieving Smallest Nth Value Match Within a List

Problem:

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))}

Leave a Reply

Your email address will not be published. Required fields are marked *

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.