CODEWITHSUNDEEP
excelindexingexcel-formulamatch
Spyros
Spyros

Reputation: 249

Finding the cell index of a string using a substring in MS Excel

I am trying to complete a task in an Excel spreadsheet. I have a list of strings, each of which is a substring of some slightly longer string. What I would like to do is to search each substring (a cell index) against a range of longer strings (a range of cells in a column) and return the actual cell index plus 1 (or really the numerical part of that cell index plus 1, e.g. B451 => 451+1 = 452).

So for example, I would like to search string "AAA" in cell G2 of sheet sorted_data against the cell range B2:B1001 in sheet data_all and would like the cell index + 1 of range B2:B1001 in which string "BAAAB" is found, e.g. 452 (if "BAAAB" is in cell B451). The strings and substrings are unique so there is no chance of multiple hits.

The formula has to be extended down 1000 columns of sheet sorted_data but the search range remains the same throughout.

I have the formula

=COUNTIF('data_all'!$B$2:$B$1001,"*"&'sorted_data'!G2&"*") > 0

This only return a TRUE or FALSE though, not what I want. Any ideas?

Upvotes: 0

Views: 3290

Answers (1)

Scott Craner
Scott Craner

Reputation: 152465

Use MATCH to return the row number + 1

=MATCH("*"&G2&"*",$B:$B,0)+1

The MATCH("*"&G2&"*",$B:$B,0) returns the row number and the +1 adds one row.

You can then use INDEX to return the value in any column desired in that row:

=INDEX($A:$A,MATCH("*"&G2&"*",$B:$B,0)+1)

This would return the value in column A in the row beneath where the match was found.

As you can see we use full column references so the data can grow without detriment. And INDEX/MATCH is supremely optimized so the number of formulas will not make that big of impact on the calc times.

Upvotes: 2

Related Questions