Find row number of text string in a range of cells

**Steven Fleck** · 02-01-2008, 08:35 AM

Hi,

I am trying to do the following

I have a set of variable names (i.e., text strings), which are embedded amongst other text in a large number of rows (i.e., in a range). I need to find the row number in the range where each variable name occurs.

To illustrate

I have might have two variables, called "Variable 1" and "Variable 2".

The range of rows with text might look like this:

random text random text
random text random text
random textVariable 1
[blank row]
random text random text random text
random text random text
[blank row]
[blank row]
random text random text
random Variable2text random text

In this range, Variable 1 occurs in row number 3, and Variable 2 occurs in row number 10 (but in other instances my variables could be in other rows) (also note that the range is a single column).

What formula can I use to pull out the row number information for each of my variables? I have tried to use a combination of "find" and "match" functions, but suspect I may need to enter these into an array formula of some kind? Any help on this would be great.

Regards,
Steven

**Ron Coderre** · 02-01-2008, 09:03 AM

If you source list begins in Cell A1....

Try something like this:

D1: Variable 1

This formula directly calculates the rownumber of the 
cell containing "Variable 1", even if it's embedded in a sentence:
E1: =MATCH("*"&D1&"*",A:A,0)

Otherwise, if your list is further down the column and other non-list items, may be above/below the list...
perhaps this:

D1: Variable 1
E1: =MATCH("*"&D1&"*",$A$3:$A$20,0)+ROW($A$3)-1

Note: There is no error checking in those formulas. If the search text doesn't exist in one of the cells, the formula returns #N/A.

These are the more robust versions of those formulas:

E1: =IF(COUNT(MATCH("*"&D1&"*",A:A,0)),MATCH("*"&D1&"*",A:A,0),"no match")
or
E1: =IF(COUNT(MATCH("*"&D1&"*",$A$3:$A$20,0)),MATCH("*"&D1&"*",$A$3:$A$20,0)+ROW($A$3)-1,"no match")

Does that help?

**Steven Fleck** · 02-01-2008, 09:38 AM

Hi Ron,

This worked perfectly! Many thanks!

Regards,
Steven

**khalidnisar** · 03-27-2012, 02:02 AM

thx that was a great help actually i was doing formula for fina and match text in Column A="Alpha1" and if Text in Column B= contain "Alpha2" then export value of same row from Column C.
did something like that and it worked:
=IF(INDEX(IRLOG, IF(COUNT(MATCH("*"&$F5&"*",Comm,0)),MATCH("*"&$F5&"*",Comm,0),1), 4)="WCEL",INDEX(IRLOG, IF(COUNT(MATCH("*"&$F5&"*",Comm,0)),MATCH("*"&$F5&"*",Comm,0),1), 6),"")

IRLOG = TABLE name
F5 = Alpha2
Comm = column B list name/range
WCEL = Alpha1
6 is the column number for which i want to export the value for.

**barcky42** · 12-10-2012, 10:39 AM

Worked great for me too, thank you!

**jserb** · 12-20-2012, 08:54 PM

Thank you, this just saved me so much time!

**tmcentee** · 01-06-2013, 04:14 PM

I am a new member and just want to say thanks for once again giving me exactly the answer I needed!

**protonLeah** · 01-06-2013, 08:38 PM

tmcentee, jserb
Please do not resurrect old threads to say thanks (or to ask questions). To say thanks for a thread not your own, use the star * Icon at the lower left of the frame of that particular post.