In Microsoft Excel, there are numerous methods available to search for text in a range of cells or an array. You can search for a text value and return different outputs based on multiple criteria. In this article, you’ll learn all the suitable methods to search for text in a range in Excel with proper examples and simple illustrations.

**Download Practice Workbook**

You can download the Excel workbook that we’ve used to prepare this article.

**11 Suitable Methods to Search for Text in Range in Excel**

**1. Use of Find & Select Command to Search for Text in Any Range**

In the following picture, there are some random texts lying under the **Text **header. We’ll search for a text or a word **‘USA’** by using **Find & Select** command.

**📌**** Step 1:**

➤ Go to **Home **ribbon ➦ **Editing** group of commands ➦ **Find & Select** drop-down ➦ **Find **command.

A dialogue box will open up.

**📌**** Step 2:**

➤ In the **Find what** option, type **‘USA’**.

➤ Press **Find Next**.

You’ll see a green rectangular indicator enclosing **Cell B8** which defines that the word or text **‘USA’** is lying there.

**Read more:** **How to Find If A Range of Cells Contains Specific Text in Excel**

**2. Use ISTEXT Function to Check If a Range of Cells Contains Text**

**The ISTEXT function** is generally used to check whether a cell contains a text or not. In the following table, we’ll apply this function to all cells in **Column B** and check which ones are containing text data. As the **ISTEXT **is a logical function, it’ll return a boolean value- **TRUE **(If text is found) or **FALSE **(If text is not found).

In the output **Cell C5**, the required formula is:

`=ISTEXT(C5)`

After pressing **Enter **and auto-filling the rest of the cells in **Column C**, we’ll get all the return values with **TRUE **or **FALSE **depending on the data types in **Column B**.

**3. Search for Specific Text in a Range of Cells with IF Function in Excel**

**The IF function** is used to check whether a condition is met and the function returns a boolean value- **TRUE **or **FALSE**. In the picture below, **Column B** has some text data. Under the **Output **header in **Column C**, we’ll apply the **IF **function to search for a country name **‘England’**. The return value will be **‘Yes’** if the condition is met, otherwise it’ll be **‘No’**.

The required formula in the first output **Cell C5** will be:

`=IF(B5="England","Yes","No")`

After pressing **Enter **and filling down the rest of the cells, we’ll find the return value **Yes **for **B8 **as the cell contains the text **England**. The other output cells will show the return value **No **as the given condition has not been met there,

**4. Search for Partial Match of a Text in a Range of Cells in Excel**

By combining the **IF, ISNUMBER**, and **SEARCH **functions, we’ll look for a partial match in a range of cells and the formula will return **‘Found’** if it matches the criteria, otherwise, it’ll return **‘Not Found’**.

For example, in the given texts in **Column B**, we’ll look for a text **‘USA’,** and under the **Output **header, the formula will return **‘Found’** or **‘Not Found’** for the corresponding searches.

The required formula in the output **Cell C5** should be:

`=IF(ISNUMBER(SEARCH("USA",B5)),"Found","Not Found")`

Now press **Enter **and autofill the entire column, you’ll get the return values at once. Since **Cell B8** contains the text **‘USA’**, the formula has returned **‘Found’ **in **Cell C8**.

**🔎**** How Does the Formula Work?**

**The SEARCH function**looks for the text**‘USA’**in the cell and returns the starting position of the text. If the text is not found, the function returns a**#VALUE**error.**The ISNUMBER function**checks whether the return value found by the**SEARCH**function is a numeric value or not and returns**TRUE**or**FALSE**based on the type of the return value.- Finally, the
**IF**function searches for the boolean values-**TRUE**or**FALSE**and returns**‘Found’**for**TRUE**,**‘Not Found’**for**FALSE**.

**5. Combining IF and COUNTIF Functions to Look for Specific Text in Range**

Now in **Column D**, there are some words that are to be found in the texts in **Column B**. We’ll combine the **IF **and **COUNTIF **functions here. **The COUNTIF function** will count the number of findings of the selected text from **Column D** in **Column B**. The **IF **function will then look for the count greater than **‘0’ **and return the specified message **‘Found’**, otherwise it’ll return **‘Not Found’.**

In the first output **Cell E5**, the corresponding formula will be:

`=IF(COUNTIF($B$5:$B$9,"*"&D5&"*")>0,"Found","Not Found")`

After pressing **Enter **and auto-filling the rest of the cells in **Column E**, we’ll get all the resultant values with **‘Found’ **or **‘Not Found’** right away.

**Read more:** **How to Find If A Range of Cells Contains Specific Text in Excel**

**6. Use of Lookup Functions to Search for Text and Return Values**

**i. VLOOKUP Function to Look for Text in Range**

**The VLOOKUP function** looks for a value in the leftmost column in a table and returns a value in the same row from the specified column. In the following table, there are three columns containing some random names of the salesmen, their corresponding sales, and 10% bonuses based on the sales.

In the output **Cell C12**, we’ll apply the **VLOOKUP **function to search for the name of a salesman given in **C11**, and the function will then return the bonus amount for the corresponding salesman.

So, the related formula with the **VLOOKUP **function in **Cell C12** should be:

`=VLOOKUP(C11,B5:D9,3,FALSE)`

After pressing **Enter**, we’ll get the bonus amount for Sam at once.

**Read more:** **How to Find Text in Cell in Excel**

**ii. HLOOKUP Function to Look for Text in Range**

**The HLOOKUP function** works opposite to the **VLOOKUP **function. The **HLOOKUP **function looks for a value in the top row of a table and returns the value in the same column from the specified row.

In the following picture, the random names of the salesman, their corresponding sales, and bonuses are now in transposed order. In the output **Cell C9**, we’ll apply the **HLOOKUP **function to return the bonus amount for Sam.

The required formula with the **HLOOKUP **function in **C9 **will be:

`=HLOOKUP(C8,C4:G6,3,FALSE)`

After pressing **Enter**, the function will return the bonus amount for Sam right away.

**Read more:** **How to Find Value In Range in Excel**

**iii. XLOOKUP Function to Look for Text in Range**

**The XLOOKUP function** is a tremendous addition to Microsoft Excel as this function outplays both of the **VLOOKUP **and **HLOOKUP **functions. The **XLOOKUP **function searches a range for a match and returns the corresponding item from the second range of the array. The one problem with this function is it is available in **Excel 365** only.

In the following table, where the **VLOOKUP **function was used earlier, we’ll apply the **XLOOKUP **function now to return the similar output in **Cell C12**.

So, the related formula in the corresponding cell is:

`=XLOOKUP(C11,B5:B9,D5:D9)`

Now press **Enter **and you’ll get the bonus amount for Sam.

And now the data table is transposed. So, the **XLOOKUP **function will look for the value horizontally and return the output from the specified row for the given value or text.

The related formula with the **XLOOKUP **function in **Cell C9** will be:

`=XLOOKUP(C21,C17:G17,C19:G19)`

After pressing **Enter**, you’ll get similar result as found previously.

**7. Apply INDEX-MATCH Formula to Find Text in Range in Excel**

In this section, we’ll apply the combination of the **INDEX **and **MATCH **functions. **The INDEX function** returns a value or a reference at the intersection of the particular row and column. **The MATCH function** returns the relative position of an item in an array that matches a specified value in a specified order.

So, the required formula comprising of the **INDEX **and **MATCH **functions in the output **Cell C12** will be:

`=INDEX(B5:D9,MATCH(C11,B5:B9,0),3)`

Now press **Enter **and you’ll find the resultant value immediately.

**8. Search for Text in Range and Return the Cell Reference**

By applying the **CELL **function, we can return the cell reference of a lookup text in a range of cells or a table. In the following table **(B5:B9)**, we’ll look for the partial match of the text **‘USA’ **and the corresponding formula will return the cell reference of the finding in **C12**.

The required formula with the **CELL **function in the output **Cell C12** will be:

`=CELL("address",INDEX(B5:B9,MATCH("*"&C11&"*",B5:B9,0)))`

The formula will return the absolute cell reference of the related search as shown in the screenshot below.

**9. Search for Text in Repeated Occasions and Return All Positions**

Let’s assume, we have some texts with repetitions in **Column B** under the **Text **header. What we’ll do is now apply a formula to return all the row positions of the repetitions for the selected text value.

If we want to look for the text **‘USA’ **in **Column B** and return all the row numbers for the repetitions, we have to apply the following formula in the output **Cell E5**:

`=SMALL(IF($E$4=$B$5:$B$12,ROW($B$5:$B$12)-ROW($A$1)+1),ROW(1:1))`

After pressing **Enter **and using the **Fill Handle** to fill down until the **#NUM** error is found, we’ll get all the row numbers from **Column B** for the selected text **‘USA’**.

**🔎**** How Does the Formula Work?**

- The
**IF**function here looks for the condition to be met and returns the row numbers (by using**the ROW function**) for the matches along with the boolean value**FALSE**for the non-matches. So, the return values found here are:

**{FALSE;FALSE;7;FALSE;9;FALSE;11;FALSE}**

**The SMALL function**returns the n^{th}smallest value from the array found in the previous step.

**10. Look for Specific Text and Return the Starting Position of the First Character**

**i. Use of FIND Function**

**The FIND function** looks for a text in another text string and returns the starting position of the selected text. The **FIND **function is case-sensitive.

Assuming that we’re going to look for the text **‘GER’ **in **Cell B5**.

The required formula in the output **Cell C8** will be:

`=FIND(C7,B5)`

After pressing **Enter**, the function will return **12 **that means the text **‘GER’ **has been found from the 12th character of the text string lying in **Cell B5**.

As the **FIND **function is case-sensitive, if the function looks for the text **‘ger’ **instead of **‘GER’ **then it’ll return a **#VALUE** error.

**ii. Use of SEARCH Function**

**The SEARCH function** works similarly to the **FIND **function. The only difference is the **SEARCH **function is case-insensitive whereas the **FIND **function is case-sensitive.

As the **SEARCH **function also returns the starting position of a text value in another text string, the required formula in the output **Cell C8** will be:

`=SEARCH(C7, B5)`

After pressing **Enter**, the function will return the similar result as found by the **FIND **function before.

Since the **SEARCH **function is case-insensitive, the function will not return a **#VALUE **error unlike the **FIND **function for the lookup text **‘ger’ **here.

**11. Use of Excel Table to Search for Text and Return Filtered Data**

In our last example, we’ll use the **Excel **table to look for a text and display the corresponding row after filtering. So, let’s use the following data table to convert it into an **Excel **table and then search for the text **‘Peter’**.

**📌**** Step 1:**

➤ Select the entire table **(B4:D9)** first.

➤ Now press **CTRL+T** to convert the data into an **Excel **table.

➤ In the **Create Table** dialogue box, the data location will be selected automatically. Now press **OK **only.

So, your data table has just turned into an Excel table.

**📌**** Step 2:**

➤ Click on the **Salesman **drop-down now.

➤ Now type **‘Peter’ **in the text box.

➤ Press **OK **and you’re done.

Like in the picture below, you’ll be displayed the filtered data for Peter only.

**Concluding Words**

I hope, all of these methods mentioned above will now help you to apply them in your Excel spreadsheets when you have to search for a text in a range for various purposes. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.