Index and Match lowest nth and highest nth value of a given number from a list

[NileshAPatel]

Hi All,
I want to workour formula to find lowest decreasing nth value from a list base on a specific value and same for highest increasing nth value as showing in column K

H5 and H14 are figure to be input manually.


Thanks.

How-to-find-nth-closest-match-in-Excel-Updated.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4		Id	Name	Type	Stats Total					Lowest in decreasing order
5		4	Charmander	Fire	309		Match	499		nth	Matched Stat
6		7	Squirtle	Water	314					1	405
7		5	Charmeleon	Fire	405					2	405
8		8	Wartortle	Water	405					3	314
9		1	Bulbasaur	Grass	499
10		2	Ivysaur	Grass	524
11		3	Venusaur	Grass	525
12		9	Blastoise	Fire	530
13		6	Charizard	Fire	534					Higher in increasing order
14							Match	499		nth	Matched Stat
15										1	524
16										2	525
17										3	530
18
Closest match (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:E13	Expression	=$C5=#REF!	text	NO
B5:E13	Expression	=$C5=#REF!	text	NO
B5:E13	Expression	=$C5=#REF!	text	NO

 

[Habtest]

Hi, please try:

Book2
	A	B	C	D	E	F	G	H	I	J	K
1
2
3
4		Id	Name	Type	Stats Total					Lowest in decreasing order
5		4	Charmander	Fire	309		Match	499		nth	Matched Stat
6		7	Squirtle	Water	314					1	405
7		5	Charmeleon	Fire	405					2	405
8		8	Wartortle	Water	405					3	314
9		1	Bulbasaur	Grass	499
10		2	Ivysaur	Grass	524
11		3	Venusaur	Grass	525
12		9	Blastoise	Fire	530
13		6	Charizard	Fire	534					Higher in increasing order
14							Match	499		nth	Matched Stat
15										1	524
16										2	525
17										3	530
Sheet1

Cell Formulas
Range	Formula
K6:K8	K6	=IFERROR(AGGREGATE(14,6,($E$5:$E$13)/($E$5:$E$13<$H$5),J6),"")
K15:K17	K15	=IFERROR(AGGREGATE(15,6,($E$5:$E$13)/($E$5:$E$13>$H$5),J15),"")

 

[NileshAPatel]

Thanks a lots sir .....

My Problem get solved.


Regards
Nilesh Patel