Find highest number in one column based on other criteria.

[Gaffee]

Hi all,

I've been playing for a while now and can't figure out if/how to get what I need. I want to find the highest possible number in column A, but from matching to othert criteria.

I want to input a defined number in cell AA3 which references B3:N3, and then input a number in cell AA5. This number will refer to B4:N21, I need it to be equal to or greater than (but be the closest to it) the number in cell AA5.

I then want to return the highest value in column A that meets these criteria.

eg.

If I put 31.2 in AA3, and 18 in AA5, I would want it to return the highest possible number in Column A which is 7 in this instance.

If you need any more clarity please ask, and thanks for any help.

Joe

 

[JamesCanale]

I lost a lot of savviness for 2016. Someone here can do better than this. I changed the I9 to 18.7, because 18.6 was otherwise the lowest and returns a 4.

MrExcelPlayground24.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3		10.3	10.3	13.8	17.3	20.7	24.2	27.7	31.2	34.6	38.1	41.6	45.1	48		31.2
4	2.1	60
5	2.4	58.8														18
6	2.5	58.2
7	3	54.6	42.3													8	helper - could be integrated below
8	3.5	49.9	42.3
9	4	44.5	40.5						18.7							18.7	helper - could be integrated below
10	4.5	39.8	37.2						18.9
11	5	36.4	34.2						19.1							7
12	6	31.6	28.7						19.2
13	7	26.5	24.1						18.7
14	8								17.7
15	9								15.9
16	10								11.3
17	11								12.6
18	12								11.6
19	13								10.5
20	14								9.4
21	15								8.5
Sheet4

Cell Formulas
Range	Formula
P7	P7	=MATCH(P3,B3:N3,0)
P9	P9	=MIN(IF(INDEX(B4:N21,,P7)<P5,9999999,INDEX(B4:N21,,P7)))
P11	P11	=INDEX(A1:A21,MAX((INDEX(B4:N21,,P7)=P9)*ROW(INDEX(B4:N21,,P7))))