In Rank formula, please help with making address for the array dependant on vlookup formula

[harshlath]

Can you please help me with below:

RANK (Cell Reference e.g. A1, Array reference e.g. A1:A10)

Summary of Query: Need to derive row number in Array reference on basis of vlookup.

Details:
Basically I have list like below:

Country Agent Amount
India ABC 100
India DEF 200
China XYZ 20
China XXX 30
China YYY 40

- Every month, number of Agents in each country would vary e.g. in above list India has two agents, it might be three agents in next month
- I need to calculate Rank of each Agent in his country. Have made another table which is giving me Row Number for First and Last Agent in each country (table like below)
First Agent Last Agent
India 2 3
China 4 6

- So I want to create RANK formula in which Row number in Array reference is vlookup value from the table

Thanks a lot in advance

 

[NeonRedSharpie]

=RANK($C2,INDIRECT("C"&VLOOKUP($A2,$G$1:$I$3,2,0)&":C"&VLOOKUP($A2,$G$1:$I$3,3,0)))

This assumes table 1 is in A1:C6 and table2 is in G1:I3

 

[Marcelo Branco]

To calculate the rank of the Agents for each country try

	A​	B​	C​	D​
1​	Country​	Agent​	Amount​	Rank​
2​	India​	ABC​	100​	2​
3​	India​	DEF​	200​	1​
4​	China​	XYZ​	20​	3​
5​	China​	XXX​	30​	2​
6​	China​	YYY​	40​	1​

Formula in D2 copied down
=COUNTIFS($A$2:$A$100,A2,$C$2:$C$100,">"&C2)+1

Hope this helps

M.

 

[harshlath]

Thanks a lot Neonredsharpie and Marcelo for solution, very kind of you!

 

[Marcelo Branco]

Thanks a lot Neonredsharpie and Marcelo for solution, very kind of you!

You are welcome. Thanks for the feedback

M.