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.

[TABLE="width: 215"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Amount[/TD]
[TD]Division[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD] $ 100.00[/TD]
[TD="align: right"]10[/TD]
[TD]KY[/TD]
[/TR]
[TR]
[TD] $ 150.00[/TD]
[TD="align: right"]10[/TD]
[TD]KY[/TD]
[/TR]
[TR]
[TD] $ 175.00[/TD]
[TD="align: right"]15[/TD]
[TD]KY[/TD]
[/TR]
[TR]
[TD] $ 130.00[/TD]
[TD="align: right"]20[/TD]
[TD]TN[/TD]
[/TR]
[TR]
[TD] $ 180.00[/TD]
[TD="align: right"]22[/TD]
[TD]TN[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Amount[/td][td]Division[/td][td]State[/td][td][/td][td]Division[/td][td]State[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

$100.00​

[/td][td]

10​

[/td][td]KY[/td][td][/td][td]

10​

[/td][td]KY[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

$150.00​

[/td][td]

10​

[/td][td]KY[/td][td][/td][td]Row[/td][td]Amount[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

$175.00​

[/td][td]

15​

[/td][td]KY[/td][td][/td][td]

3​

[/td][td]

150​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

$130.00​

[/td][td]

20​

[/td][td]TN[/td][td][/td][td]

2​

[/td][td]

100​

[/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

$180.00​

[/td][td]

22​

[/td][td]TN[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet10[/td][/tr][/table]

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))