## Finding values in an excel list

*Article updated on February 16, 2011*

**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

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

## Share this article