Khan Muhammad Kawser
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 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.
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, 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:
=CELL(“address”,INDEX(B4:B14,9))
- 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.
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 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: 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}
- 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