Finding values in an excel list
Question: How do I extract row numbers from specific values in a list?
Answer:
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.
How to get cell addresses: =IF(ROW()-1>COUNT(IF($C$1=$A$2:$A$12,ROW($A$2:$A$12),"")),"", ADDRESS(SMALL(IF($C$1=$A$2:$A$12,ROW($A$2:$A$12),""),ROW()-1),1))
SMALL(IF($C$1=$A$2:$A$12,ROW($A$2:$A$12),""),ROW()-1) gets the rownumbers without blanks.
{6,7,8}
ADDRESS(SMALL(IF($C$1=$A$2:$A$12,ROW($A$2:$A$12),""),ROW()-1),1) shows the cell addresses.
IF(ROW()-1>COUNT(IF($C$1=$A$2:$A$12,ROW($A$2:$A$12),"")),"", ADDRESS(SMALL(IF($C$1=$A$2:$A$12,ROW($A$2:$A$12),""),ROW()-1),1)) removes errors.
Download excel sample file for this tutorial.
finding-values-in-an-excel-list.xls
(Excel 97-2003 Workbook *.xls)
Functions in this tutorial
ADDRESS(row_num;column_num)
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
Related posts:
Finding the nearest date in a range of dates using excel formula
What values are missing in List 1 that exists i List 2?
Locate lookup values in a table [HYPERLINK]
Recommended categories
