Row Lookup

[RBone90]

So I have a large list of data that contains multiple categories. I am wanting it to automatically pull from highest to lowest based off criteria in another cell. If I wanted to see the highest for KY for Division 10 then the second highest. I would also want to pull more than just the amounts so I figure knowing the row number is the best way.

Amount	Division	State
$ 100.00	10	KY
$ 150.00	10	KY
$ 175.00	15	KY
$ 130.00	20	TN
$ 180.00	22	TN

<colgroup><col><col span="2"></colgroup><tbody>
</tbody>

 

[DRSteele]

This should help. More condition can be added by expanding upon the logic in the array argument of function AGGREGATE.

Copy the formula across and down as necessary.

Book1
	A	B	C	D	E	F	G	H	I
3							Amount rank
4	Amount	Division	State		Division	State	1	2	3
5	$100.00	10	KY		KY	10	150	100	na
6	$150.00	10	KY		TN	22	180	na	na
7	$175.00	15	KY		AB	16	na	na	na
8	$130.00	20	TN		KY	15	175	na	na
9	$180.00	22	TN
Sheet63

Cell Formulas
Range	Formula
G5	=IF(COUNTIFS($C$5:$C$9,$E5,$B$5:$B$9,$F5)>=G$4,AGGREGATE(14,6,($E5=$C$5:$C$9)*($F5=$B$5:$B$9)*($A$5:$A$9),G$4),"na")

 

[Aladin Akyurek]

@ RBone90

What is the expected output?

 

[Oscar Cronquist]

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​	G​
1​	Amount	Division	State		Division	State
2​	$100.00​	10​	KY		10​	KY
3​	$150.00​	10​	KY		Row	Amount
4​	$175.00​	15​	KY		3​	150​
5​	$130.00​	20​	TN		2​	100​
6​	$180.00​	22​	TN
7​

Sheet: Sheet10

Array formula in cell E4:
=MATCH(F4&"-"&$E$2&"-"&$F$2,$A$1:$A$6&"-"&$B$1:$B$6&"-"&$C$1:$C$6,0)

Array formula in cell F4:
=LARGE(IF(($B$2:$B$6=$E$2)*($C$2:$C$6=$F$2),$A$2:$A$6,""),ROWS($A$1:A1))