Excel Search for Text in Range (11 Quick Methods)

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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.

You May Also Like to Explore

• Find Matches or Duplicate Values in Excel (8 Ways)
• How to Use FIND Function in Excel (7 Suitable Examples)
• How to Find Top 5 Values and Names in Excel (8 Useful Ways)
• Find Duplicates in Two Columns in Excel (6 Suitable Approaches)
• How to Find a Character in String in Excel