## Extract cell values in a range using a criterion in excel

*Article updated on December 07, 2010*

If you can rearrange your data, read this post:

Lookup values in a range using two or more criteria and return multiple matches in excel, part 2

You can use a lot easier array formula if you rearrange your data.

### Array formula in B15:

copied down as far as necessary.

**Named ranges**

tbl (B3:I9)

What is named ranges?

### How to implement array formula to your workbook

Change the named ranges. Change "South Carolina" to whatever you want to search for, in the above array formula.

### Download excel sample file for this tutorial.

Filter cells in a range using criteria.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

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

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

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

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

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

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

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

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 […]### 5 Responses to “Extract cell values in a range using a criterion in excel”

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

This is brill. I need to work out:

I have data in a row column A-F

I need to search the data for a word within the cells each cell has a string of words.

Then I need to show the name of the cell the word appears in ie G1

So if I had

A1: "Name, Joe"

B1: "Address, 17 the place"

C1: "Criteria, This is a Test"

D1: "Speech, How Are you"

E1: "Number, 12345"

F1: "Location, In the Field"

A2: "Name, Joe"

B2: "Criteria, This is a Test"

C2: "Address, 17 the place"

D2: "Location, In the Field"

E2: "Number, 12345"

F2: "Speech, How Are you"

I need to know where the word "Number" is located (ie, E1)

I have a table basically with loads of data but the fields have been mixed up so location might be in column C or it could be in column F depending on who typed it in. I want to recreate the table automatically with the data in the correct columns. The table has 100's of rows so I need to be able to search for a specific word.

Please help!!

Tina,

I am not sure I understand.

Download excel file *.xlsx

Tina.xlsx

Thanks for your great site!!!! Congrats.

Yoram,

Thank you for commenting!

Hi,

I have a few rows and column A is the key. Column A has duplicate values. I need to compare the value of D with A and return the entire row with all duplicate values which match the criteria. Could you help