# Find Text in Excel Range and Return Cell Reference (3 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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 requirements 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.

## 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 2nd row is the B5:D5 row.
• As the column number is blank, the INDEX function returns the entire 2nd row.

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 2nd row will be returned.
• 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. 3rd value of the array B4:B9 is returned by this formula.

Example 4

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

This formula returns the intersection value 2nd row and 3rd column of the range 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 6th
• 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.

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

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, MATCH(D16,B4:B14,0), returns the value 9. Because the position of Dropbox in the array B4:B14 is 9th. So, the overall formula becomes:

• Now, the INDEX(B4:B14,9) part refers to cell reference B12. So, the formula becomes: =CELL(“address”,B12)
• Then, =CELL(“address”,B12) returns absolute reference of the cell 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.

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

### 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 2ndsmallest 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: 5 Examples of Using Array Formula in Excel

• 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}
• 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.

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

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Kawser Ahmed

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy: udemy.com/user/exceldemy/

1. Reply Tony O'Brien Jan 7, 2021 at 7:17 PM

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

2. Reply Tony O'Brien Jan 7, 2021 at 7:43 PM

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

• Reply Hi Tony,
Thanks.

3. Reply 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 Advanced Excel Exercises with Solutions PDF  