Vlookup return row number

[ddoyle67]

I have a formula =VLOOKUP("ALTERNATE",E11:E1000,1,FALSE) which will find if the word "alternate" appears in column E. But I would like the result to return the row number that it finds the "Alternate" in so that it can feed into a couple other formulas that use that row number to stop the calculations above the row with "Alternate".

 

[awoohaw]

use MATCH or XMATCH:

=MATCH("ALTERNATE",E11:E1000,0)

=XMATCH("ALTERNATE",E11:E1000,0)

 

[AhoyNC]

Do you want the actual Excel row number or the row number in the array E11 - E1000.
See attached examples.

Book1
	A	B	C	D	E	F	G	H
1
2
3
4
5
6
7
8
9							Row #	Actual
10							in Array	Row #
11					Test 1		6	16
12					Test 2
13					Test 3
14					Test 4
15					Test 5
16					ALTERNATE
17					test 7
18					test 8
19					test 9
20					test 10
Sheet3

Cell Formulas
Range	Formula
G11	G11	=MATCH("ALTERNATE",E11:E1000,0)
H11	H11	=MATCH("ALTERNATE",E1:E1000,0)

 

[ddoyle67]

Thank you both. Yes I wanted the actual row. E11 started my data so that was why i started my range there. I was able to use this when added on to, to get the results i wanted.

 

[AhoyNC]

You're welcome. Glad we could help. Thanks for the feedback.