Location of arbitrary lookup

[Andre Croteau]

Hello,

I am still blown away by Chip Pearson's formulas to find arbitrary lookups:
It's GREAT!!!
http://www.cpearson.com/excel/lookups.htm


However, I would like to have a formula that would give me the "row number"
of the result.
In Chip's example, he is looking for the 3rd "Chip" reference giving the
lookup value of 120.
I would like a formula to get the row number 16 which is the location of the
result 120.

I have seen some array formulas giving the location for the Max (or Min) in
a range, and tried to incorporate that in a new formula, without success.

Can ayone help, please? Table was replicated below

Thanks

André

A B C
4
5 Stephen 10
6 Chip 20
7 Tom 30
8 John 40
9 Rob 50
10 Stephen 60
11 Chip 70
12 Tom 80
13 John 90
14 Rob 100
15 Stephen 110
16 Chip 120
17 Tom 130
18 John 140
19 Rob 150
20
21 Chip 3 The Third "CHIP",
and the result is 120. I would like to have a formula giving row numer
as 16
22
23 120
=INDEX(B5:C19,SMALL(IF(B5:B19=B21,ROW(B5:B19)-ROW(B5)+1,ROW(B19)+1),C21),2)

 

[Peo Sjoblom]

Remove the index part plus 2 parenthesis, the first ( and the last ) of the
formula and you should get the row number

 

[Andre Croteau]

This is perfect, thank you!
André

Remove the index part plus 2 parenthesis, the first ( and the last ) of the
formula and you should get the row number

--

Regards,

Peo Sjoblom



=INDEX(B5:C19,SMALL(IF(B5:B19=B21,ROW(B5:B19)-ROW(B5)+1,ROW(B19)+1),C21),2)