Lookup the Cell Address of a Specific Word and Its Next Occurrences

Sometimes, you may want to find the cell address of a cell that contains a specific word or text in Excel. For example, you may have a list of product names in column A and you want to find the cell address of the first product that contains the word “apple”. Or, you may want to find the cell address of the next product that contains the word “apple” after the first one, and so on.

There are different ways to achieve this task in Excel, but one of the most common and versatile methods is to use a combination of the following functions:

The CELL function, which returns information about a cell, such as its address, format, value, etc. The syntax of the CELL function is: =CELL(info_type, [reference]), where info_type is a text value that specifies what type of information you want to get, and reference is an optional argument that specifies the cell that you want to get information from. If you omit the reference argument, the function will use the current cell as the reference. For example, =CELL("address") will return the address of the current cell, such as “$A$1”.
The INDEX function, which returns a value or a reference to a value from within a table or range. The syntax of the INDEX function is: =INDEX(array, row_num, [column_num]), where array is the table or range of cells that you want to look up a value from, row_num is the row number in the array that contains the value, and column_num is an optional argument that specifies the column number in the array that contains the value. If you omit the column_num argument, the function will return the entire row as a reference. For example, =INDEX(A1:B10, 3, 2) will return the value in the cell B3, which is in the third row and second column of the range A1:B10.
The MATCH function, which returns the relative position of a value in a range or array. The syntax of the MATCH function is: =MATCH(lookup_value, lookup_array, [match_type]), where lookup_value is the value that you want to find, lookup_array is the range or array of cells that you want to search, and match_type is an optional argument that specifies how you want to match the value. The match_type argument can be one of the following values:
- 1 or omitted: The function will find the largest value that is less than or equal to the lookup_value. The lookup_array must be sorted in ascending order.
- 0: The function will find the exact match of the lookup_value. The lookup_array can be in any order.
- -1: The function will find the smallest value that is greater than or equal to the lookup_value. The lookup_array must be sorted in descending order.

For example, =MATCH("apple", A1:A10, 0) will return the position of the first cell in the range A1:A10 that contains the exact value “apple”.

By using these three functions together, you can create a formula that will look up the cell address of a cell that contains a specific word or text in a range. The basic formula is:

=CELL("address", INDEX(lookup_range, MATCH("*"&word&"*", lookup_range, 0)))

Where lookup_range is the range of cells that you want to search, and word is the word or text that you want to find. The asterisks (*) are wildcards that allow you to match any text before or after the word. The MATCH function will return the row number of the first cell that contains the word in the lookup_range, and the INDEX function will return the reference to that cell. The CELL function will then return the address of that cell as a text value.

Procedures

To use the formula to lookup the cell address of a cell that contains a specific word, then next occurrence and so on, you need to follow these steps:

Enter the word or text that you want to find in a cell, such as B1.
Enter the formula =CELL("address", INDEX(lookup_range, MATCH("*"&B1&"*", lookup_range, 0))) in another cell, such as C1, where lookup_range is the range of cells that you want to search, such as A1:A10. Press Enter to get the result.
To find the next occurrence of the word, you need to modify the formula slightly by adding a small value to the MATCH function, such as 0.0001. This will make the function look for the next largest value that matches the word, instead of the exact match. The modified formula is: =CELL("address", INDEX(lookup_range, MATCH("*"&B1&"*"+0.0001, lookup_range, 1))). Enter this formula in another cell, such as C2, and press Enter to get the result.
To find the next occurrence after that, you can simply copy and paste the formula in C2 to the next cell, such as C3, and so on. The formula will automatically adjust the MATCH function to look for the next largest value that matches the word.

Explanation

The formula =CELL("address", INDEX(lookup_range, MATCH("*"&B1&"*", lookup_range, 0))) works by using the MATCH function to find the relative position of the first cell that contains the word or text in B1 within the lookup_range. The MATCH function uses the wildcard characters (*) to match any text before or after the word, and the match type 0 to find the exact match. For example, if the word in B1 is “apple”, and the lookup_range is A1:A10, the MATCH function will return 4, because the cell A4 contains the value “apple pie”.

The INDEX function then uses the value returned by the MATCH function as the row number argument, and returns the reference to the cell in that row within the lookup_range. For example, if the lookup_range is A1:A10, and the row number is 4, the INDEX function will return the reference to the cell A4.

The CELL function then uses the reference returned by the INDEX function as the reference argument, and returns the address of that cell as a text value. For example, if the reference is A4, the CELL function will return “$A$4” as the address.

The formula =CELL("address", INDEX(lookup_range, MATCH("*"&B1&"*"+0.0001, lookup_range, 1))) works similarly, but with a slight modification. The MATCH function adds a small value (0.0001) to the word or text in B1, and uses the match type 1 to find the largest value that is less than or equal to the word plus the small value. This will make the function look for the next occurrence of the word in the lookup_range, instead of the first one. For example, if the word in B1 is “apple”, and the lookup_range is A1:A10, the MATCH function will return 6, because the cell A6 contains the value “apple juice”, which is the next largest value that is less than or equal to “apple”+0.0001.

The INDEX and CELL functions then work the same way as before, and return the address of the cell in the sixth row of the lookup_range, which is “$A$6”.

Scenario

Suppose you have a list of sales data in the range A1:B10, where column A contains the product names and column B contains the sales amounts. You want to find the cell address of the first product that contains the word “pen”, then the next product that contains the word “pen”, and so on. You can use the following steps:

Enter the word “pen” in cell D1.
Enter the formula =CELL("address", INDEX(A1:A10, MATCH("*"&D1&"*", A1:A10, 0))) in cell E1, and press Enter. You will get the result “$A$3”, which is the address of the first product that contains the word “pen” in column A.
Enter the formula =CELL("address", INDEX(A1:A10, MATCH("*"&D1&"*"+0.0001, A1:A10, 1))) in cell E2, and press Enter. You will get the result “$A$7”, which is the address of the next product that contains the word “pen” in column A.
Copy and paste the formula in E2 to the next cell, E3, and press Enter. You will get the result “$A$9”, which is the address of the next product that contains the word “pen” in column A.

Table

Product	Sales	Word	Cell Address
Notebook	$10	pen	$A$3
Pencil	$5	pen	$A$7
Pen	$8	pen	$A$9
Eraser	$3	pen	#N/A
Marker	$6	pen	#N/A
Highlighter	$4	pen	#N/A
Pen holder	$7	pen	#N/A
Pen case	$9	pen	#N/A
Pen stand	$11	pen	#N/A
Pen drive	$12	pen	#N/A

The result of the scenario is that the formula can find the cell address of the first three products that contain the word “pen” in column A, but it will return an error value (#N/A) for the rest of the products, because there are no more matches in the lookup range.

Other Approaches

There are other ways to lookup the cell address of a cell that contains a specific word, then next occurrence and so on, in Excel. Here are some of them:

You can use the FIND function, which returns the starting position of a text string within another text string. The syntax of the FIND function is: =FIND(find_text, within_text, [start_num]), where find_text is the text that you want to find, within_text is the text that you want to search, and start_num is an optional argument that specifies the position from which you want to start the search. The FIND function is case-sensitive, so you need to make sure that the text matches exactly. For example, =FIND("pen", A1) will return 1, because the word “pen” starts at the first position in the cell A1.

You can use the FIND function with the CELL function to create a formula that will look up the cell address of a cell that contains a specific word. The formula is:

=CELL("address", IFERROR(FIND(word, lookup_range), ""))

Where word is the word or text that you want to find, and lookup_range is the range of cells that you want to search. The IFERROR function will return a blank value if the FIND function returns an error, such as when there is no match. For example, =CELL("address", IFERROR(FIND("pen", A1:A10), "")) will return the address of the first cell in the range A1:A10 that contains the word “pen”.

To find the next occurrence of the word, you need to modify the formula slightly by adding a small value to the FIND function, such as 0.0001. This will make the function look for the next position that contains the word, instead of the first one. The modified formula is:

=CELL("address", IFERROR(FIND(word+0.0001, lookup_range), ""))

Enter this formula in another cell, and copy and paste it to the next cells to get the address of the next occurrences of the word.

You can use the SEARCH function, which is similar to the FIND function, but it is not case-sensitive. The syntax of the SEARCH function is: =SEARCH(find_text, within_text, [start_num]), where find_text is the text that you want to find, within_text is the text that you want to search, and start_num is an optional argument that specifies the position from which you want to start the search. For example, =SEARCH("pen", A1) will return 1, because the word “pen” starts at the first position in the cell A1, regardless of the case.

You can use the SEARCH function with the CELL function to create a formula that will look up the cell address of a cell that contains a specific word. The formula is:

=CELL("address", IFERROR(SEARCH(word, lookup_range), ""))

Where word is the word or text that you want to find, and lookup_range is the range of cells that you want to search. The IFERROR function will return a blank value if the SEARCH function returns an error, such as when there is no match. For example, =CELL("address", IFERROR(SEARCH("pen", A1:A10), "")) will return the address of the first cell in the range A1:A10 that contains the word “pen”, regardless of the case.

To find the next occurrence of the word, you need to modify the formula slightly by adding a small value to the SEARCH function, such as 0.0001. This will make the function look for the next position that contains the word, instead of the first one. The modified formula is:

=CELL("address", IFERROR(SEARCH(word+0.0001, lookup_range), ""))