Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Conflicting Multiple Conditional Formatting Rules in Excel
  • How to count table rows in Excel
  • Everything about Charts in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • Get column name from index in Excel Table

References

  • Vlookup Examples in Excel
  • How to get last row in numeric data in Excel
  • Count rows that contain specific values in Excel
  • How to use Excel INDIRECT Function
  • Multi-criteria lookup and transpose in Excel

Data Validations

  • Excel Data validation must contain specific text
  • Excel Data validation don’t exceed total
  • Excel Data validation no punctuation
  • Excel Data validation allow uppercase only
  • Excel Data validation number multiple 100

Get nth match with INDEX / MATCH in Excel

by

This tutorial shows how to Get nth match with INDEX / MATCH in Excel using the example below;

Formula

{=INDEX(array,SMALL(IF(vals=val,ROW(vals)-ROW(INDEX(vals,1,1))+1),nth))}

Explanation

To retrieve multiple matching values from a set of data with a formula, you can use the IF and SMALL functions to figure out the row number of each match and feed that value back to INDEX. In the example shown, the formula in I7 is:

{=INDEX(amts,SMALL(IF(ids=id,ROW(ids)-ROW(INDEX(ids,1,1))+1),H6))}

Where named ranges are amts (D4:D11), id (I3), and ids (C4:C11).

Note that this is an array formula and must be entered with Control + Shift + Enter.

How this formula works

At the core, this formula is simply an INDEX formula that retrieves the value in an array at a given position. The value for n  is supplied in column H, and all the “heavy” work that the formula does is to figure out the row from which to retrieve a value, where row corresponds to “nth” match.

Worked Example:   Next largest match with the MATCH function in Excel

The IF function does the work of figuring out which rows contain a match, and the SMALL function returns the nth value from that list. Inside of IF, the logical test is:

ids=id

which yields this array:

{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}

Note the customer id matches at the 1st and 4th positions, which appear as TRUE. The “value if true” argument in IF generates a list of relative row numbers with this expression:

ROW(ids)-ROW(INDEX(ids,1,1))+1

which produces this array:

{1;2;3;4;5;6;7}

This array is then “filtered” by the logical test results, and the IF function returns the following array result:

{1;FALSE;FALSE;4;FALSE;FALSE;FALSE}

Note we have valid row numbers for row 1 and row 2.

Worked Example:   How to calculate next scheduled event in Excel

This array is then processed by SMALL, which is configured to use values in column H to return “nth” values. The SMALL function automatically ignores the logical values TRUE and FALSE in the array. In the end, the formulas reduce to:

=INDEX(amts,1) // I6, returns $150
=INDEX(amts,4) // I7, returns $125

Handling errors

Once there are no more matches for a given id, the SMALL function will return a #NUM error. You can handle this error with the IFERROR function, or by adding logic to count matches and abort processing once the number in column H is greater than the match count. The example here shows one approach.

Worked Example:   Get first non-blank value in a list in Excel

Post navigation

Previous Post:

Get location of value in 2D array in Excel

Next Post:

Get nth match with VLOOKUP in Excel

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Nested IF function example in Excel
  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel XOR Function
  • How to use Excel OR Function
  • IFNA function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Convert date to month and year in Excel
  • Count dates in current month in Excel
  • How to calculate project start date based on end date in Excel
  • Calculate days remaining in Excel
  • Basic Overtime Calculation Formula in Excel

Grouping

  • Group times into unequal buckets in Excel
  • If cell contains one of many things in Excel
  • Group numbers at uneven intervals in Excel
  • Running count group by n size in Excel
  • Group times into 3 hour buckets in Excel

General

  • How to create dynamic named range with INDEX in Excel
  • Automatically fill series of cells in Excel using AutoFill
  • How to calculate percent variance in Excel
  • 3D SUMIF for multiple worksheets in Excel
  • Common Errors in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning