Article updated on February 16, 2011

Question: How do I extract row numbers from specific values in a list?

How to get row numbers: =IF(ROW()-1>COUNT(IF(\$C\$1=\$A\$2:\$A\$12, ROW(\$A\$2:\$A\$12),"")),"",SMALL(IF(\$C\$1=\$A\$2:\$A\$12,ROW(\$A\$2:\$A\$12),""), ROW()-1))

IF(\$C\$1=\$A\$2:\$A\$12,ROW(\$A\$2:\$A\$12) extracts row numbers where \$C\$1=\$A\$2:\$A\$12.
{, , , , ,6,7,8, , , , }

SMALL(IF(\$C\$1=\$A\$2:\$A\$12,ROW(\$A\$2:\$A\$12),""),ROW()-1) removes blanks.
{6,7,8}

IF(ROW()-1>COUNT(IF(\$C\$1=\$A\$2:\$A\$12, ROW(\$A\$2:\$A\$12),"")),"",SMALL(IF(\$C\$1=\$A\$2:\$A\$12,ROW(\$A\$2:\$A\$12),""), ROW()-1)) removes errors.

SMALL(IF(\$C\$1=\$A\$2:\$A\$12,ROW(\$A\$2:\$A\$12),""),ROW()-1) gets the rownumbers without blanks.
{6,7,8}

finding-values-in-an-excel-list.xls
(Excel 97-2003 Workbook *.xls)

Functions in this tutorial

creates a cell reference as text, given specified row and column numbers

SMALL(array,k) returns the k-th smallest number in this data set.

ROW(reference) returns the rownumber of a reference

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers