# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  Find row number of text string in a range of cells

## Steven Fleck

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

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

Try something like this:



```
Please Login or Register  to view this content.
```


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



```
Please Login or Register  to view this content.
```


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:



```
Please Login or Register  to view this content.
```


Does that help?

----------


## Steven Fleck

Hi Ron,

This worked perfectly! Many thanks!

Regards,
Steven

----------


## khalidnisar

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

Worked great for me too, thank you!

----------


## jserb

Thank you, this just saved me so much time!

----------


## tmcentee

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

----------


## protonLeah

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.

----------

