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