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.

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

📌 Step 1:

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

A dialogue box will open up.

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

📌 Step 2:

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

➤ Press Find Next.

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

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

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


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

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

In the output Cell C5, the required formula is:

=ISTEXT(C5)

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

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.

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


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

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

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

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

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

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,

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


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.

Search for Partial Match of a Text in a Range of Cells in Excel

The required formula in the output Cell C5 should be:

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

Search for Partial Match of a Text in a Range of Cells in Excel

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.

Search for Partial Match of a Text in a Range of Cells in Excel

🔎 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’.

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

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

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

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

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.

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


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.

VLOOKUP Function to Look for Text in Range

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

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

VLOOKUP Function to Look for Text in Range

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

VLOOKUP Function to Look for Text in Range


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.

HLOOKUP Function to Look for Text in Range

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

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

HLOOKUP Function to Look for Text in Range

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

HLOOKUP Function to Look for Text in Range


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)

XLOOKUP Function to Look for Text in Range

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

XLOOKUP Function to Look for Text in Range

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)

XLOOKUP Function to Look for Text in Range

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

XLOOKUP Function to Look for Text in Range


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)

Apply INDEX-MATCH Formula to Find Text in Range in Excel

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

Apply INDEX-MATCH Formula to Find Text in Range in Excel


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.

Search for Text in Range and Return the Cell Reference

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

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

Search for Text in Range and Return the Cell Reference

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

Search for Text in Range and Return the Cell Reference


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.

Search for Text in Repeated Occasions and Return All Positions

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))

Search for Text in Repeated Occasions and Return All Positions

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

Search for Text in Repeated Occasions and Return All Positions

🔎 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 nth 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)

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

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.

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

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.

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


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)

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

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

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

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.

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


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

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

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

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

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

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

📌 Step 2:

➤ Click on the Salesman drop-down now.

➤ Now type ‘Peter’ in the text box.

➤ Press OK and you’re done.

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

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

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


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)

INDEX-MATCH Formula to Find Minimum Value in Excel (4 Suitable Ways)

Tags:

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo