Simpler excel function to match for values in a range/vector/range and return it's row number

[TTYF]

Hi all,

I am looking for a simpler nested excel function to return the row no. of a searched text/No. from a range. I couldn't quite wrapped my head around on the excel function. I do think this is achievable and easy. Do you have any better ways ?

instead of:
=MATCH("F",1:1,0)
=MATCH("F",2:2,0)
=MATCH("F",3:3,0)
=MATCH("F",4:4,0)
=MATCH("F",5:5,0)
=MATCH("F",6:6,0)

Summary: Trying to get the row number of searched word "F" from a range if you do not know which specific column or row the search text is in.

Thanks alot. Appreciate it.

T

 

[Phuoc]

Try this:

=AGGREGATE(15,6,Row(rangge)/(Range = "F"),1)

Or:

=AGGREGATE(14,6,Row(rangge)/(Range = "F"),1)

 

[TTYF]

Hi Phuoc, I think it doesn't work. Are you able to use reference from excel pic and come out with it? Does it work on your end?

 

[Phuoc]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	3
2
3				F
4
5
6
7
8
9
10
11
Sheet1

Cell Formulas
Range	Formula
A1	A1	=AGGREGATE(15,6,ROW(C1:M11)/(C1:M11 = "F"),1)

 

[TTYF]

Yeah, it works now. Thank you so much Phuoc!

 

[TTYF]

How about if u use it to find a number ? I had #num error

 

[TTYF]

How about if u use it to find a number ? I had #num error

Nvm. Just put number will do. Ignore this question