Multiple Ranges with Multiple Criteria

[RainGauge]

I need a formula to find the most ideal row and displays that row's column 1 value.

To evaluate a row, I place the highest priority on it having the lowest value in column 3. (MIN(M3:M10)
If there is only 1 row with this lowest value then I display the value from column 1. INDEX(K3:K10,MATCH(MIN(M3:M10),M3:M10,0))

If there is more than 1 row with the lowest value in column 3 then the formula takes those rows and prioritizes the lowest value in column 2.

Again, if there is more than 1 row that meets the criteria (lowest value in column 2) I then need it to prioritize the value closest to 1 in column 1.

In the end, the formula should display the value of column 1 in the most suitable row.

In my example image, the formula would look at column 3: find row 5, row 7, and row 10 each having the lowest value (4).
It would then look at column 2: find row 7 and 10 each having the lowest value (2).
It would then look at column 1: find that row 10 has the closest value to 1 (1.25).
Finally it would display the value of column 1 of the ideal row.
The desired result in my example image is 1.25.

I'm struggling to find the correct way to structure the formula or even how to have it evaluate subsets of data.

 

[Eric W]

Welcome to MrExcel!

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2											Factor	>=40%	>=50%
3			ZR Coefficient	Factor							1.00	0	7
4			a200 b1.6	1.25							1.19	1	5
5											1.45	3	4
6											0.99	1	7
7											2.19	2	4
8											2.45	0	6
9											2.64	1	6
10											1.25	2	4
Sheet4

Cell Formulas
Range	Formula
D4	D4	=INDEX(K3:K10,MATCH(MIN(M3:M10*10000+L3:L10*100+ABS(K3:K10-1)),M3:M10*10000+L3:L10*100+ABS(K3:K10-1),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[RainGauge]

Thank you,
I hadn't considered multiplying the values in each range to incorporate them all into a single values for comparison.

 

[Eric W]

Yes, that's a standard trick to make the comparison easier. It's easy in this case since you wanted the smallest value in each column. If you want the smallest in one column, but in case of ties, the largest of another column, it gets trickier.

Glad I could help.