You are currently offline, waiting for your internet to reconnect

Excel: Finding the nth Value That Meets a Condition

This article was previously published under Q101167
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
In Microsoft Excel 4.0, to find the nth value in a range of cells thatmeets a condition, use the SMALL() or LARGE() function to evaluate thearray of row numbers that meet the condition.


To find the nth nonblank value in a range, enter the following formulaas an array:
NOTE: To enter the formula as an array, type the formula in a cell andpress CTRL+SHIFT+ENTER in Microsoft Excel for Windows or COMMAND+ENTERin Microsoft Excel for the Macintosh.

In above example, the range argument refers to the cells you aresearching and the n argument is a number indicating the occurrence youare looking for. For example, if range refers to cells A1:A10 and n is2, the formula returns the second nonblank value from cells A1:A10.

Following is a description of how the formula works:

In the following section of the formula,
the IF() formula returns an array of row numbers where the conditionis met. In this example, an array of row numbers for all the nonblankcells is returned by the IF() function. The SMALL() function looks atthese row numbers and returns the nth smallest row number.

In the -ROW(range)+1 section of the formula, the starting row numberin the range is subtracted from the row number returned by SMALL() andthen 1 is added. This calculates a relative "position" of the value inthe range so that the value can be returned with the INDEX() function.

If the nth value is a blank, and the remaining cells in the range areblank, the #NUM! error value will be returned to the cell. If you wantto find the nth value from the bottom up (instead of from the topdown), use the LARGE() function instead of the SMALL() function.
"Function Reference", version 4.0, pages 236-238, 250-251, 406
4.00a howto returning

Article ID: 101167 - Last Review: 11/25/2003 15:12:23 - Revision: 2.0

  • Microsoft Excel 4.0 Standard Edition
  • Microsoft Excel 4.0a
  • Microsoft Excel 4.0 for Macintosh
  • Microsoft Excel 97 Standard Edition
  • KB101167