In the corporate world, we need to deal with a lot of data every day. We also work with data for the betterment. But the fact is we don’t need the same data every day. On the other hand, every day new data is imputed. To find particular data we need to search for text or value in a range of datasets in Excel or a Spreadsheet. Excel is the most used software to deal with data. In this article, we are going to demonstrate how to search for text in range in Excel.

## 📁 Download Excel File

To practice and get the overview, please download the excel file:

## Learn to Search for Text in Range with 13 Examples in Excel

Here in this article, we find out **13 ways** to search for text in range in Excel. We consider a dataset of **Nottingham City** containing **winners of 2021** and **2022** with their **IDs**. In the dataset, there are **6 columns** and **11 rows**. We are going to use **SEARCH**, **ISTEXT**, **ISNUMBER**, **CELL**, **IFERROR**, **MATCH**, **COUNTIF**, and **INDEX **functions to search for text in range in Excel.

**Method**

### 1. Using Find and Replace Option to Search Text in Range

Using the **Find **option from **Home **Menu you can easily search anything in the dataset. Follow the steps,

**⬇️⬇️ STEPS ⬇️⬇️**

- First, click on the
**Home**Menu from**Top Ribbon**. - Secondly, click on the
**Search** - Thirdly, click on the
**Find**

** **

- Afterward, a box of
**Find and Replace**will appear. - Then, From
**Find**what, choose**Werner**. - Next, click on
**Find All**. Therefore, all the data containing**Werner**will show.

Here we got **3 data **containing **Werner**. Clicking from the** Find and Replace** box will be marked on the dataset.

**📕 Read More: ****7 Ways to Find and Replace Multiple Values in Excel**

**Method**

### 2. Using ISTEXT Function to Check Text in Range

Sometimes in the dataset, the text is absent. To detect text **ISTEXT **function can be a key. Please follow the necessary steps,

**⬇️⬇️ STEPS ⬇️⬇️**

- Primarily, Navigate a blank cell i.e.
**D6**. - Then, Insert the formula in
**D6**containing**ISTEXT**function choosing the range from**B6**to**B13**.

**=ISTEXT(B6:B13)**

- Finally, We get the output with
**TRUE-FALSE**.**TRUE**means text exists.

**Method**

### 3. Check If Cell Contains Specific Text

Suppose you have a set of records of Nottingham City. One tells you to find matching from history. What to do? Using the **SEARCH** function can be your cup of tea in this case. Follow the below procedure.

**⬇️⬇️ STEPS ⬇️⬇️**

- Firstly, Select a blank cell i.e.
**D6**. - Secondly, Input the formula containing
**IF**,**SEARCH**, and**IFERROR**in**D6**to match cell**B6**to find from the text of**C6**.

**=IFERROR(IF(SEARCH(B6,C6),”Match”),”Mismatch”)**

**🔨 Formula Breakdown**

👉 General logical expression of the formula, **=IFERROR(IF(SEARCH(cell_1,cell_2),”statement_1″),”statement_2″)**

👉 **SEARCH** function search particular data within a cell or in a set of data. Here,** SEARCH(B6,C6) **means in **B6** is finding similar data to **C6**.

👉 If data is found in** C6**, then **IF** signals to make a statement as **Match**.

👉 Else it may signal an **Error**. But **IFERROR** makes the statement **Mismatch** instead of **Error**.

- By getting the result at
**D6**and using**Fill Handle**we finally achieve our result.

**📕 Read More: ****3 Easy Ways to Find and Replace in Multiple Excel Files**

**Method**

### 4. Search for Text in Cell in Excel

Using **IF**, **SEARCH **and **ISNUMBER **functions, you can also check the winner of 2022 from 2021 history. Check the below steps,

**⬇️⬇️ STEPS ⬇️⬇️**

- Primarily, select a cell i.e.
**D6**. - Secondly, Input the formula containing
**IF**,**SEARCH**, and**ISNUMBER**in**D6**to match cell**C6**to find from the text of**B6**.

**=IF(ISNUMBER(SEARCH(C6,B6)),”FOUND”,”NOT FOUND”)**

**🔨 Formula Breakdown**

👉 Logical expression of the formula, **=IFERROR(IF(SEARCH(cell_1,cell_2),”statement_1″),”statement_2″)**

👉 **SEARCH** function search particular data within a cell or in a set of data. Here,** SEARCH(B6,C6) **means in **B6** is finding similar data to **C6**.

👉 **ISNUMBER(SEARCH(C6,B6))**=** FALSE** for **C6 **which means** C6**= **Hector **not found in **B6**.

👉 If data is found in** C6**, then **IF** signals to make a statement as **FOUND**.

👉 Else it makes the statement **NOT FOUND**.

- By getting the result at
**D6**and auto-filling, we finally obtain the final result.

**Method**

### 5. Check If Values of Two Column Matches or Not

Let’s say, you need to match 2021 winners to 2022 winners just to make sure if there is anyone who won last year, also this year. The **COUNTIF **function can be a key to comparing between columns and searching for texts.

**⬇️⬇️ STEPS ⬇️⬇️**

- Initially, Select a cell i.e.
**D6**. - Then, Input the formula containing
**COUNTIF**and**IF**in**D6**to match cell to column.

**=IF(COUNTIF($C:$C,$B6)=0,”No match in C”,””)**

**🔨 Formula Breakdown**

👉 Combining **IF** and **COUNTIF**, Let’s get the logical expression of this formula, **=IF(COUNTIF(column_by_range,Cell_Index)=0,”statement_1″,”statement_2″)**

👉 **COUNTIF **function counts the cell number based on certain criteria**.**

👉 **B6 **finds out values like itself in column-**C** with a **0** returns value.

👉 Finally, **IF **function dictates in cell **D6**, if **B6 is **unable to match to column **C, **deliver** No match in C**.

- By obtaining the result at
**D6**and using**Fill Handle**we get the final result.

**Method**

### 6. Check If Cell Contains Text from List

You can also search for specific text from some lines from another column. Comparing two columns first we will get our specific text and return it to another cell using **LEFT**, **RIGHT**, and **MID** functions. But the **LEFT** and **RIGHT** functions only match with the first word and last word respectively. But **MID** function along with a **SEARCH** function searches the text accurately.

**⬇️⬇️ STEPS ⬇️⬇️**

- Primarily, Navigate a cell i.e.
**D6**. - Then, Insert the following formula that counts words.

**=IF(D6=”Match”,MID(C6,SEARCH(B6,C6),LEN(B6)),”Not Available”)**

**🔨 Formula Breakdown**

👉 General logical expression of the formula, **=IF(D6=”Match”,MID(ref_cell,SEARCH(range),LEN(Cell_1)),”Not Available”)**

👉 **LEN** returns the no of characters in the string. Therefore, it returns the same no of characters that exist in **B6** and finds from the line using the **MID** function; where the **LEFT** and **RIGHT** function finds the value at the beginning and at end of the line respectively.

👉 ALso** IF** function works if a column like **D6** gets a **Match** value. Otherwise, it dictates Not Available.

- Finally, we get the result at
**D6,**and we finally get the total result using**Fill Handle**.

** **But, Unlike **LEFT **or **RIGHT**, the Use of the **MID **function gives us the best results.

**📕 Read More: ****3 Quick Ways to Replace Text After Specific Character**

**Method**

### 7. If Range of Cells Contains Multiple Specific Text

If you need to search multiple specific texts from the dataset. You can find it by yourself by following the below steps,

**⬇️⬇️ STEPS ⬇️⬇️**

- Initially, Navigate a cell i.e.
**D6**. - Secondarily, Insert the following formula.

**=IF(SUMPRODUCT(–ISNUMBER(SEARCH($F$6:$F$8,C6))),”Yes”,”No”)**

**🔨 Formula Breakdown**

👉 Here, General logical expression of the formula, **IF(SUMPRODUCT(–ISNUMBER(SEARCH(range_with_return,ref_cell))),”Yes”,”No”)**

👉 **SEARCH($F$6:$F$8,C6)** indicates to find **$F$6:$F$8**=** {“best”;”pianist”;”footballer”} **in** C6. **

👉 **SUMPRODUCT(–ISNUMBER(SEARCH(range_with_return,ref_cell)))= 0 **means no data found**. IF **function writes** NO.**

👉 **IF** function signals to write **Yes **if it is able to match with specific texts in **C6. **Otherwise, it signals to write** No**.

- Finally, we get the result at
**D6,**and we finally desired the result using**Fill Handle**.

**Method**

### 8. Text Joining After Searcing for Multiple Text in Range in Excel

Finding the specific texts you can also write specific texts in a cell joining a **comma(,)**. Let’s follow the procedure.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, get a blank cell i.e.
**D6**. - Then, Write down the specific texts under the
**Specific Text**column (**F6:F10**). - Next, Insert the following formula.

**=TEXTJOIN(“, “, TRUE, IF(COUNTIF(C6, “*”&$F$6:$F$10&”*”), $F$6:$F$10, “”))**

**🔨 Formula Breakdown**

👉 **COUNTIF(C6, “*”&$F$6:$F$10&”*”) **means search specific texts in **C6**.

👉 **COUNTIF(C6, “*”&$F$6:$F$10&”*”)**= **{0;0;0;0;0} **means there is no specific texts in **C6.**

👉 But in **C7**, **COUNTIF(C7, “*”&$F$6:$F$10&”*”)**= **{1;0;0;1;0}** means **{“best”;”pianist”}** matches in **C7.**

👉 **IF **function leaves the cell blank if it unable to match the values.

👉 **TEXTJOIN **function finally gather matched text in a cell by **comma(,)**.

- Lastly, we get the result at
**D6,**and we finally obtain the result using**Fill Handle**.

**📕 Read More: ****5 Tricks to Insert Comma Between Words in Excel**

**Method**

### 9. Using IF and COUNTIF Functions to Search Specific Text in Range

Using **IF** and **COUNTIF** functions combined you can search specific multiple texts in a range. Follow the below steps,

**⬇️⬇️ STEPS ⬇️⬇️**

- Firstly, select a blank cell i.e.
**D6**. - Secondly, Write down the specific texts under the
**Specific Text**column(**F6:F11**) - Thirdly, Insert the following formula.

**=IF(COUNTIF($C$6:$C$11,”*”&F6&”*”)>0,”Found”,”Not Found”)**

**🔨 Formula Breakdown**

👉 In the formula, **COUNTIF($C$6:$C$11,”*”&F6&”*”)>0**=**TRUE **means F6 exits in the range with returned value of **$C$6:$C$11**.

👉 **IF **function writes **Found **for **TRUE**; Else **Not Found** for **FALSE**.

- Finally, at
**D6,**we get the result, and using**Fill Handle**we get the total output.

**Method**

### 10. Use VLOOKUP Function to Search for Text in Range in Excel

**VLOOKUP **function dedicates to looking for a value within a specific **array**. To compare columns one can use **VLOOKUP **along with the **UPPER **function.

**⬇️⬇️ STEPS ⬇️⬇️**

- Initially, navigate a blank cell i.e.
**J6**. - After that, Input the following formula in
**J6.**

**=VLOOKUP(UPPER(H6),E6:F11,2)**

**🔨 Formula Breakdown**

👉 Formula syntax, **=VLOOKUP(UPPER(lookup_value),range,by_column)**

👉 **UPPER** is also used to find the data of** H6** in the specified data range** E6** to **F11**.

👉 **VLOOKUP function** indicates to lookup the data from the range and takes from the **2nd** column.

- After all, we get the result at
**D6**, and we finally get the output using**Fill Handle**.

**Method**

### 11. Search for Repeated Text with Position

Suppose you need to know the text position of repetitive value. Following the steps, you will be able to determine the positions.

**⬇️⬇️ STEPS ⬇️⬇️**

- Initially, navigate a blank cell i.e.
**E6**. - After that, Input the following formula in
**E6**.

**=SMALL(IF($E$5=$B$6:$B$17,ROW($B$6:$B$17)-ROW($A$1)+1),ROW(1:1))**

**🔨 Formula Breakdown**

👉 **(IF($E$5=$B$6:$B$17,ROW($B$6:$B$17)-ROW($A$1)+1)**= **{FALSE;FALSE;FALSE;9;FALSE;FALSE;12;FALSE;FALSE;15;FALSE;FALSE}** dictates that repeated value present at row **9,12,15**.

👉 **$E$5=$B$6:$B$17 **indicates to find **$E$5=Hector **from** $B$6:$B$17 **with return value.

👉 **SMALL(IF($E$5=$B$6:$B$17,ROW($B$6:$B$17)-ROW($A$1)+1),ROW(1:1))**=** {9} **means **Hector **present in** 9th **row**.**

👉 Similarly, **SMALL(IF($E$5=$B$6:$B$17,ROW($B$6:$B$17)-ROW($A$1)+1),ROW(2:2))**=** {12} **means **Hector **present in** 12th **row**.**

👉 And, **SMALL(IF($E$5=$B$6:$B$17,ROW($B$6:$B$17)-ROW($A$1)+1),ROW(3:3))**=** {15} **means **Hector **present in** 15th **row**.**

- Lastly, Getting the result at
**E6**and**auto-filling**we got**3 rows**where**Hector**is located.

**Method**

### 12. Search Text and Get Cell Reference

Suppose you need to know the cell reference of a particular value. Following the steps, you will be able to determine.

**⬇️⬇️ STEPS ⬇️⬇️**

- Initially, navigate a blank cell i.e.
**E6**. - After that, Input the following formula in
**E6**.

**=CELL(“address”,INDEX(B6:B17,MATCH(“*”&E5&”*”,B6:B17,0)))**

**🔨 Formula Breakdown**

👉 Logical expression of the formula is, **=CELL(“address”,INDEX(range,MATCH(“*”&cell_no&”*”,range,0)))**

👉 **MATCH(“*”&E5&”*”,B6:B17,0) **means** E5**= **Jammy **presents in the** 6th **cell of the range **B6:B17**.

👉 **INDEX(B6:B17,MATCH(“*”&E5&”*”,B6:B17,0))= “Jammy” ; **Here I**NDEX** function recognize the** 6th **cell value.

👉 **CELL **function denotes the cell number that is** $B$11** which is with the returned value.

- Finally, Get the result of the cell
**$B$11**at**E6**.

**Method**

### 13. Search Option to Search for Text in a Table

The simplest way is to search manually selecting a table. Follow the below steps,

**⬇️⬇️ STEPS ⬇️⬇️**

- Initially, select the entire
**dataset**. - Then,
**Right-click**on the mouse. - Next, click on the
**Filter**and select**Filter by Selected Cell’s Value.**

- After that, a
**filter menu box**will appear beside the**column heading**. - Then, select the name, you wish.
- Next, click on
**OK**

- Finally, by Clicking
**Payne**, you will also get the details in column**C**.

Also, watch carefully, Information about **Payne **is in the **8th **column. Here only the **8th** column appears using this method.

## 📄 Important Notes

🖊️ Combination of **MID **along with **SEARCH **function search data from the entire line. However, Be cautious while using **LEFT **or **RIGHT **functions. These two functions only consider the character of the beginning and end of the lines.

🖊️ Inspect functions and **syntax **carefully during the use of the formulae.

🖊️ **CELL **and** ISNUMBER** functions are only available on **Microsoft 360** version.

## 📝 Takeaway from This Article

📌 **TEXTJOIN **function uses to join text in a single cell.** **

**📌 ISTEXT **function uses to identify texts in a dataset.

**📌 IF **is a logical function that deals with statements based on **TRUE-FALSE**.

** 📌 IFERROR **function eliminates Error with a value.

**📌 EXACT** function considers case-sensitivity.

**📌 VLOOKUP **Function looks up the values in a specified area.

**📌 **In this article, we also used **SEARCH****, COUNTIF, MID, UPPER, **and** INDEX **functions to search for text in the dataset.

📌 We demonstrated every formula briefly so that it may also help during other applications.

## Conclusion

In this article, we tried to demonstrate every possible way to search for texts in a range of single or multiple columns. Hope that you enjoyed your learning. For queries and any kind of suggestions please comment below. Also for more, please visit **www.ExcelDen.com.**