index match large

[ste33uka]

Hi would anyone have a formula that would return highest value and second highest value where match if found ?
formulas would be in cell d1 and d2
So as in example imige, match if found column A to cell a1, coloumn B to cell b1
D1 would return lagest
D2 second largest
But i only want it to look at range A100:A1000 , range b100:b1000
Thanks

 

[etaf]

Change ranges to suite your example

Book1
	A	B	C	D
1			Max	2nd max
2	red	peter	7	6
3
4
5
6	red	peter	1
7	red	peter	2
8	red	peter	3
9	red	peter	4
10	red	peter	5
11	red	peter	6
12	red	peter	7
13	blue	john	8
14
Sheet1

Cell Formulas
Range	Formula
C2	C2	=MAXIFS(C6:C100,A6:A100,A2,B6:B100,B2)
D2	D2	=AGGREGATE(14,6,C6:C100/(A6:A100=A2)/(B6:B100=B2),2)

 

[Habtest]

D1, D2:

=LARGE((A100:A109=A1)*(B100:B109=B1)*(C100:C109),1)

=LARGE((A100:A109=A1)*(B100:B109=B1)*(C100:C109),2)

 

[ste33uka]

D1, D2:

=LARGE((A100:A109=A1)*(B100:B109=B1)*(C100:C109),1)

=LARGE((A100:A109=A1)*(B100:B109=B1)*(C100:C109),2)

Thanks alot for that, appreciate it