In this tutorial, I shall write how to** find text** in an **Excel range** and **return the reference of the cell that holds the text**. Also, I will show several ways to do so. So that your requirement might match with any of the ways.

But before going into the main discussion, I want to discuss a little bit about the functions that I am going to use.

## Download Working File

This is the Excel file that I have used to make this tutorial. Download and follow along with me.

## Pre-requisite Discussions

This part is optional for those who already are using heavily the following Excel functions:

**INDEX()****MATCH()****CELL()****And OFFSET()**

### # INDEX Function in Excel

** The INDEX function** returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

**The syntax of INDEX Function:**

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

`INDEX(reference, row_num, [column_num], [area_num])`

Look at the image below:

__Explanation of the formulas__

**Example 1: **

You might find Example 1 (and also Example 2) a little bit harder to understand. This is actually an** Excel Array Formula**.

- Firstly, select the cell
**C16**then write down the following formula.

`{=INDEX(B4:D9,2,)}`

- Then I pressed
**CTRL+SHIFT+ENTER**to enter the array formula.

**How this formula actually works?**

- Here the array part of the
**INDEX**function is**B4:D9**. Its**2**row is the^{nd}**B5:D5**row. - As the column number is blank, the
**INDEX**function returns the entire**2**row.^{nd}

**Example 2**

`{=INDEX((B4:D9,F4:H9),2,,2)}`

- As the
**INDEX**function reference, there are**two**ranges here:**B4:D9**and**F4:H9.** - The row number is
**2**. No column number is specified. So, all the values of the**2**row will be returned.^{nd} - The range
**F4:H9**is used by the Index function as the area number is**2.**

**Example 3**

`=INDEX(B4:B9,3,)`

It is a very simple** INDEX** formula. **3 ^{rd}** value of the array

**B4:B9**is returned by this formula.

**Example 4**

`=INDEX(B4:D9,2,3)`

This formula returns the intersection value **2 ^{nd}** row and

**3**column of the range

^{rd}**B4:D9**.

### # MATCH Function in Excel

**The MATCH function** returns the position of a value in an array of values.

**The Syntax of MATCH function:**

`=MATCH(lookup_value, lookup_array, [match_type])`

- Now, write down the following formula in the
**C17**box.

`=MATCH(C14,B4:B9,0)`

**How does this formula work?**

- The value of cell
**C14**is**Google**. So, our lookup value is**Google.** - In the cell range
**B4:B9**, the position of Google is**6**^{th} - So, the formula returns
**6.**

### # CELL Function in Excel

**The CELL function** returns information about the formatting, location, or contents of the first cell, according to the sheet’s reading order, in a reference.

**The syntax of Excel CELL Function**

`=CELL(info_type, [reference])`

By using the** CELL** function, you can get a lot of details of a cell reference including the **ABSOLUTE** address. You can see it from the above image.

### # OFFSET Function in Excel

**Excel’s OFFSET function** returns a reference to a range that is a given number of rows and columns from a given reference.

**The syntax of OFFSET Function:**

`=OFFSET(reference, rows, cols, [height], [width])`

- Here, I used the following formula in the
**B13**box.

`=SUM(OFFSET(B4,3,1,3,2))`

**How does this formula work?**

- The reference of the
**OFFSET**function is cell reference**B4**. So, cell**B4**’s position is**0**. - Then
**3**rows down from the reference. - Then
**1**column right from the last position. - Finally, the sum of the range
**C7:D9**(height**3**rows and width**2**columns). This returns a value of**756**. The range**C7:D9**is highlighted with an orange color border.

So, the pre-requisite discussion is over.

Now, let’s come to our main discussion.

## 3 Methods to Find Text in an Excel Range and Return Cell Reference

In this section, I will explain the methods to find text in range and return cell references in Excel. Furthermore, for your better understanding, I will use the following data set.

### Method 1: Use of INDEX & MATCH Functions to Find Text in Range and Return Cell Reference

In this method, I will search the text in a single column and if found, the formula will return the reference. Also, I will use the **INDEX** and **MATCH** functions to find text in range and return cell references.

**Steps:**

- Firstly, select a different cell
**D17**where you want to keep the result. - Secondly, write down the following formula in the
**D17**cell.

`=CELL("address",INDEX(B4:B14,MATCH(D16,B4:B14,0)))`

- Subsequently, press
**ENTER**to get the result.

Finally, you will get a cell reference for the “**Dropbox**” text.

**How does this formula work?**

Let me explain the formula for the text **“Dropbox”**:

- This part of the formula,
, returns the value**MATCH(D16,B4:B14,0)****9**. Because the position of**Dropbox**in the array**B4:B14**is**9**. So, the overall formula becomes:^{th}

**=CELL(“address”,INDEX(B4:B14,9))**

- Now, the
part refers to cell reference**INDEX(B4:B14,9)****B12**. So, the formula becomes:*=CELL(“address”,B12)* - Then,
returns absolute reference of the cell**=CELL(“address”,B12)****B12**. - So, I get
**$B$12**as the output of the whole formula.

*Note: INDEX(B4:B14,9) can return either the value or the cell reference. This is the beauty of the INDEX Function.*

**Read More: Excel Reference Cell in Another Sheet Dynamically**

**Similar Readings**

**How to Use INDIRECT Function in Excel (12 Suitable Instances)****If Cell Contains Specific Text Then Add 1 in Excel (5 Examples)****How to Use ROW Function in Excel (With 8 Examples)****If Cell Contains Text Then Add Text in Another Cell in Excel****How to Use COLUMNS Function in Excel (3 Examples)**

### Method 2: Applying INDEX, MATCH & OFFSET Functions

In this method, I can search text from more than one column. But you have to select the column yourself. Furthermore, I will use the **INDEX, OFFSET, **and **MATCH** functions to find text in range and return cell references.

**Steps:**

- Firstly, write down the following formula in the
**D18**cell.

`=CELL("address",INDEX(OFFSET(B4,0,D17-1,11,1), MATCH(D16,OFFSET(B4,0,D17-1,11,1),0)))`

- Secondly, press
**ENTER**to get the result.

Finally, you will get a cell reference for the “**Mike Little**” text.

**How does this formula work?**

- This formula works like the above one. The only difference is: that the column is selected dynamically using Excel’s
**OFFSET**function. If you understand the**OFFSET**function, then this part is simple to understand:**OFFSET(B4,0,D17-1,11,1)**

**Read More: Examples of OFFSET Function in Excel (Formula+VBA )**

### Method 3: Use of Combined Functions to Find Text in Range and Return Cell Reference

Sometimes a text value might repeat in a range more than once. I can return the row number of that text in the range. Here, I will use the **SMALL, ROW,** and

**IF**functions to find text in range and return cell reference.

You see from the following image that the text **“Apple”** is repeating itself **3** times in the range **B4:B14**.

Let me show you how I get these row numbers.

- I have used this formula in cell
**D9**.

`{=SMALL(IF($D$6=$B$4:$B$14,ROW($B$4:$B$14)-ROW($B$4)+1),ROW(1:1))}`

- Then I copied down this formula in the
**D10**cell.

`=SMALL(IF($D$6=$B$4:$B$14,ROW($B$4:$B$14)-ROW($B$4)+1),ROW(2:2))`

- Here, I pressed
**CTRL + SHIFT + ENTER**to get the result.

- Similarly, I have copied the formula until the formula returns an error value.

It is clearly an Excel array formula.

But before, you have to know how the** SMALL **function works in Excel.

**The syntax of SMALL function:**

`SMALL(array,k)`

For example, **SMALL({80;35;55;900},2)** will return the **2 ^{nd}**smallest value in the array

**{80;35;55;900}**. The output will be:

**55**.

**So, how does the formula work?**

Cell **D9** =** {=SMALL(IF($D$6=$B$4:$B$14,ROW($B$4:$B$14)-ROW($B$4)+1),ROW(1:1))}**

To understand this array formula clearly, you can read my guide: **Excel Array Formula Basic 2 – Breakdown of Array Formula**

- This part of the formula,
**IF($D$6=$B$4:$B$14,ROW($B$4:$B$14)-ROW($B$4)+1)**, actually returns the array for the**SMALL**function.- Logical test part of
**the IF function**is:**$D$6=$B$4:$B$14**. This part tests (one by one) whether the values of the range**$B$4:$B$14**is equal to**$D$6**or not. If equal, a**TRUE**value is set in the array and if not equal, a**False**value is set in the array:**{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}** - And
**value_if_true**part is:**ROW($B$4:$B$14)-ROW($B$4)+1)**. This whole part returns something like this:**{1;2;3;4;5;6;7;8;9;10;11} – {1} + 1****= {0;1;2;3;4;5;6;7;8;9;10} + 1 = {1;2;3;4;5;6;7;8;9;10;11}**

- Logical test part of
**ROW(1:1)**is actually the**k**of the**SMALL**function. And it returns**1**.- So, the formula in the cell
**D9**becomes like this:**SMALL(IF({FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10;11}),1).** - Now the
**IF**function returns this array:**{FALSE;FALSE;3;FALSE;FALSE;FALSE;7;FALSE;9;FALSE;FALSE}.** - The formula becomes:
**SMALL({FALSE;FALSE;3;FALSE;FALSE;FALSE;7;FALSE;9;FALSE;FALSE},1).** - Finally, the formula returns
**3.**

I hope you get how this complex formula works.

**Read More:** **Excel If Cell Contains Text Then Return Value (8 Easy Ways)**

## Conclusion

I hope you found this article helpful. Here, I have explained **3 ** suitable methods to understand how to **find text in range and return cell reference in Excel**. You can visit our website **Exceldemy** to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

## Related Articles

**Check If Cell Contains Partial Text in Excel (5 Ways)****How to Use ROWS Function in Excel (With 7 Easy Examples)****If Cell Contains Text Then Copy to Another Sheet in Excel****How to Sum If Cell Contains Specific Text in Excel (6 Ways)****If Cell Contains Text Then Return Value in Another Cell Using Excel Formula**

I was wondering if there is a way to find part text with the above information to get reference cell?

There are 8 cell references to retrieve

There are blanks cells in range Z6 – Z270

DJ5 is the part cell reference

=SMALL(IF($DJ$5=$Z$6:$Z$270,ROW($Z$6:$Z$270)-ROW($Z$6)+1),ROW(1:1)) This returns 0 as not extract match

=CELL(“address”,INDEX(Z6:Z270,MATCH(DJ5,Z6:Z270,0))) This is a extract match & returns cell reference $Z$32

Is there a way to find part text from the above information?

There are 8 reference cells to retrieve.

There are blank cells in range Z6 – Z270

Reference part text cell DJ6

{=SMALL(IF($DJ$5=$Z$6:$Z$270,ROW($Z$6:$Z$270)-ROW($Z$6)+1),ROW(1:1))} Returns VALUE

=CELL(“address”,INDEX(Z6:Z270,MATCH(DJ5,Z6:Z270,0))) Returns extract match with extract cell reference $Z$32 only

=MATCH(DJ5&”*”,Z:Z,0)*1 Return extract cell row number 32.

All the above formula’s didn’t return 2 – 8 reference cells

Regards

Tony

Hi Tony,

I will check your formula.

Thanks.

Hi Kawser

I’m stump on the above information as to why I can’t get the information of the 8 reference’s & there cell reference.

Formula =MATCH(DJ5&”*”,Z:Z,0)*1 & add *2, *3, & so on for count

The reference part cell DJ5 = Track Time:

Track Time : refers to Conditions, Time & Fields

Regards

Tony