## Search for a cell value in a dataset

*Article updated on September 20, 2017*

*This blog article is one out of five articles on the same subject.*

- Search for a cell value in an excel table
- Lookup with multiple criteria and display multiple search results using excel formula
- Lookup with multiple criteria and display multiple search results using excel formula, part 2
- Lookup with multiple criteria and display multiple search results using excel formula, part 3

**Question:**

How do I identify rows that have a specific cell value in a excel 2007 table?

**Answer:**

**Excel 2007 array formula in B13:E15**

**Named ranges**

Table1 (B3:E6)

What is named ranges?

### Array formula explanation

*Step 1 - Find cells in named range matching criterion*

=IFERROR(INDEX(Table1, SMALL(IF(**Table1=$C$9**, ROW(Table1)-MIN(ROW(Table1))+1, ""), ROW(Table1)-MIN(ROW(Table1))+1), COLUMN(Table1)-MIN(COLUMN(Table1))+1), "")

Table1=$C$9

becomes

B3:E6=$C$9

becomes

{East, AA, EE, HH; North, BB, AA, GG; South, CC, FF, DD; West, DD, CC, AA}= AA

becomes

{FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE, FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE}

*Step 2 - Extract row numbers*

=IFERROR(INDEX(Table1, SMALL(**IF(Table1=$C$9, ROW(Table1)-MIN(ROW(Table1))+1, "")**, ROW(Table1)-MIN(ROW(Table1))+1), COLUMN(Table1)-MIN(COLUMN(Table1))+1), "")

IF(Table1=$C$9, ROW(Table1)-MIN(ROW(Table1))+1, "")

becomes

IF({FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,FALSE, FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,TRUE}, {1,1,1,1;2,2,2,2;3,3,3,3;4,4,4,4}, "")

becomes

{"",1,"","";"","",2,"";"","","","";"","","",4}

*Step 3 - Return the k-th smallest row number*

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

SMALL(IF(Table1=$C$9, ROW(Table1)-MIN(ROW(Table1))+1, ""), ROW(Table1)-MIN(ROW(Table1))+1)

becomes

SMALL({"",1,"","";"","",2,"";"","","","";"","","",4}, {1,1,1,1;2,2,2,2;3,3,3,3;4,4,4,4})

becmoes

{1,1,1,1;2,2,2,2;4,4,4,4}

*Step 4 - Return matching records*

INDEX(Table1, SMALL(IF(Table1=$C$9, ROW(Table1)-MIN(ROW(Table1))+1, ""), ROW(Table1)-MIN(ROW(Table1))+1), COLUMN(Table1)-MIN(COLUMN(Table1))+1)

becomes

INDEX(Table1, {1,1,1,1;2,2,2,2;4,4,4,4}, COLUMN(Table1)-MIN(COLUMN(Table1))+1)

becomes

INDEX(Table1, {1,1,1,1;2,2,2,2;4,4,4,4}, {1,2,3,4;1,2,3,4;1,2,3,4;1,2,3,4})

and returns:

{"East","AA","EE","HH";"North","BB","AA","GG";"West","DD","CC","AA"}

**Download excel example file **

search-for-a-string-in-excel-table.xlsx

(Excel 2007 Workbook *.xlsx)

**Functions in this article:**

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**ROW(**reference**)** returns the rownumber of a reference

**COLUMN(**reference**)** Returns the column number of a reference

**IFERROR(**value;value_if_error**)** Returns value_if_error if expression is an error and the value of the expression itself otherwise

**MIN(**number1,[number2]**)**

Returns the smallest number in a set of values. Ignores logical values and text

### Read more articles about this topic

The following article shows you how to VLOOKUP and return multiple values, the lookup value must match the entire cell value.

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Search for a cell value in a dataset:

Search for a cell value in a dataset

This blog article is one out of five articles on the same subject. Search for a cell value in an […]

How to look for values in a column that contain two text strings:

Search for multiple text strings in multiple cells

Question: How do I search a list for two text strings and return a list with where both strings occurs? […]

Search two columns for two text strings. The strings can be anywhere in these two columns but both text strings have to be found in different cell values or a single cell value and be on the same row to match:

A record is returned if both search strings are found on the same row

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

Search for three text strings in three columns, one text string in each column. Return values in which all three cases are found:

Search each column for a string each and return multiple records – AND logic

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

Search for **multiple text strings** in **multiple columns**, one text string in each column. Return values in which **all text strings match**:

Extract records where all criteria match if not empty

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

Search for a single text string in a **single** column and return multiple matches.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Search for a text string in a **single** column and return multiple corresponding values.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Search for multiple text strings in a **single** column and return multiple corresponding values.

Search for a text string and return multiple adjacent values

This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]

Search for a text string in **multiple** columns and return corresponding values.

Search for a text string and return multiple adjacent values

Vlookup with 2 or more lookup criteria and return multiple matches

VLOOKUP and return multiple matches based on many criteria.Lookup values in a range using two or more criteria and return multiple matches in excel, part 2

In this post we are going to extract multiple text values. We are looking for names and the criteria are […]Lookup and return multiple values on the same row from a range excluding blanks

Jim asks: I downloaded the file lookup-vba3. I think I can use this to help me populate a calendar.I substituted […]Lookup multiple values in a range and return multiple corresponding values

In a previous blog post Extract cell values in a range using a criterion in excel I provided a formula […]Search values distributed horizontally and return corresponding value

Question: Hi, The formula here works great but I can't figure out how to change it to work with data […]### One Response to “Search for a cell value in a dataset”

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

Thank you