Find text within a range and return row number

[Monsignor]

WOW! I thought this would be simpler that it's turned out. Please help!

In range A1:G50 there are words. The columns are in no order.

What is a formula that would return the row number of a word I'm interested in?

I would like to type "Farm" and have Excel tell me it's in row 37.


What to do?

 

[meemormau]

Or try this array formula (use Ctrl+Shift+Enter and not only Enter):

=MAX((A1:C5=F1)*LIN(A1:A5))

Note: I used the sheet of Peter_SSs example.

Markmzz

This formula did not work for me. it returned this: #NAME?

 

[meemormau]

This formula did not work for me. it returned this: #NAME?

Never mind. I got it:

=MAX((A1:C5=F1)*Row(A1:A5))

 

[moks844]

I was trying to develop a formula to return the row number where the text string is in any part of the range
the formula only return the first row number but not the other row number.

 

[moks844]

I was trying to develop a formula to return the row number where the text string is in any part of the range
the formula only return the first row number but not the other row number.

I think I could have a solution using the small function in combination with other logical formulas.
here was the used formula : SMALL(IF(ISERROR(SEARCH(CONCATENATE("*",$E$3,"*"),$C$3:$C$11)),"",ROW($C$3:$C$11)),ROW(A1))
it's Array (Ctrl+shift+Enter)
and with this I could get the wanted results

 

[markmzz]

I think I could have a solution using the small function in combination with other logical formulas.
here was the used formula : SMALL(IF(ISERROR(SEARCH(CONCATENATE("*",$E$3,"*"),$C$3:$C$11)),"",ROW($C$3:$C$11)),ROW(A1))
it's Array (Ctrl+shift+Enter)
and with this I could get the wanted results

Moks844,
​

Try this small modification in your formula:


=IFERROR(SMALL(IF(ISERROR(SEARCH($E$3,$C$3:$C$10)),"",ROW($C$3:$C$10)),ROWS($F$3:$F3)),"")

Markmzz

 

[Peter_SSs]

Or if you would rather avoid the array entry (& are using Excel 2010 or later)

=IFERROR(AGGREGATE(15,6,ROW(C$3:C$11)/(SEARCH(E$3,C$3:C$11)>0),ROWS(F$3:F3)),"")