Retrieve nth match with INDEX
To find the nth match of a value from a range and retrieve its corresponding data, you can use a formula based on the INDEX, ROW, SMALL, and IF functions.
How to retrieve information associated with the nth match of a value with INDEX?
To get the information about the 3rd match of Usain Bolt in the table as shown above, you can use the ROW and IF functions together to get an array that consists of the row numbers of all the Usain Bolt values. Then use the SAMLL function to retrieve the row number of the nth matched value you specified. Finally, feed the result to the INDEX function to get the corresponding information at the same row.
Generic syntax
=INDEX(return_range,SMALL(IF(lookup_array=lookup_value,ROW(lookup_array-ROW(INDEX(lookup_array,1,1))+1),n))
√ Note: This is an array formula that requires you to enter with Ctrl + Shift + Enter.
- return_range: The range to return the information corresponding to the nth match from. Here refers to the year or time range.
- lookup_array: The range to look for the row position of the nth match from. Here refers to the winner range.
- lookup_value: The value you specified to lookup its nth match. Here refers to Usain Bolt.
- n: Indicate the nth match. To find the 1st match of a value, set n as 1; to find the 2nd match, set n as 2.
To get the information about the 3rd match of Usain Bolt, please copy or enter the formulas below in the cells G8 and G9, and press Ctrl + Shift + Enter to get the results:
Year (Cell G8)
=INDEX(B6:B12,SMALL(IF(C6:C12=G5,ROW(C6:C12-ROW(INDEX(C6:C12,1,1))+1),G6))
Time (Cell G9)
=INDEX(D6:D12,SMALL(IF(C6:C12=G5,ROW(C6:C12-ROW(INDEX(C6:C12,1,1))+1),G6))
Explanation of the formula
Here we use the formula below as an example:
=INDEX(B6:B12,SMALL(IF(C6:C12=G5,ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6))
- ROW(C6:C12): The ROW function returns the row numbers of each cells in the range C6:C12 in an array like this: {6;7;8;9;10;11;12}.
- ROW(INDEX(C6:C12,1,1)): The INDEX and ROW functions return the first row number of the lookup range C6:C12 in this Excel worksheet, which is 6.
- IF(C6:C12=G5,ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1) = IF(C6:C12=G5,{6;7;8;9;10;11;12}-6+1): The IF function checks if each value in the range C6:C12 matches the value in the cell G5, Usain Bolt. If yes, the function will return the result of “its corresponding row number-6+1”; Otherwise return a FALSE. So the results would be in an array like this: {FALSE;2;3;4;FALSE;FALSE;FALSE}.
- SMALL(IF(C6:C12=G5,ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6) = SMALL({FALSE;2;3;4;FALSE;FALSE;FALSE},G6): The snippet returns the 3rd (value in cell G6) smallest value from the array, which is 4.
- INDEX(B6:B12,SMALL(IF(C6:C12=G5,ROW(C6:C12)-ROW(INDEX(C6:C12,1,1))+1),G6)) = INDEX(B6:B12,4) The INDEX function returns the 4th value in the year range B6:B12, which is Beijing 2008.
Related functions
The Excel INDEX function returns the displayed value based on a given position from a range or an array.
The IF function is one of simplest and most useful functions in Excel workbook. It performs a simple logical test which depending on the comparison result, and it returns one value if a result is TRUE, or another value if result is FALSE.
The Excel ROW function returns the row number of a reference.
Related Formulas
Retrieve nth match with VLOOKUP
Suppose you have a list of values and their corresponding information, to retrieve the related information of the nth match of a value, you can use a formula based on the VLOOKUP function.
Retrieve information associated with lowest n values
To retrieve data that are corresponding to the smallest, 2nd smallest, or nth smallest value in a list, table or row in Excel, you can use an INDEX and MATCH formula together with the SMALL function.
Get information corresponding to maximum value
To retrieve data that are corresponding to the maximum value in a list, table or row in Excel, you can use an INDEX and MATCH formula together with the MAX function.
Get information corresponding to minimum value
To retrieve data that are corresponding to the minimum value in a table, you can use an INDEX and MATCH formula together with the MIN function.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.
