Should be a quick answer for you guys...

[qrtback10]

I have the formula below working like I want but I need to add ONE MORE criteria: the returned value must also have a rating of "AA" in column P2:P14. How in the world do I do this??

=INDEX([COLOR=#F7981D]O2:O13[/COLOR],MATCH(MIN(ABS([COLOR=#7E3794]K2:K14[/COLOR]-[COLOR=#11A9CC]S2[/COLOR])),ABS([COLOR=#7E3794]K2:K14[/COLOR]-[COLOR=#11A9CC]S2[/COLOR]),[COLOR=#1155CC]0[/COLOR]))

Thanks!

 

[GNB]

Would wrapping an IF statement around it work? Something like the below?
=IF(P2:P14="AA",INDEX(O2:O13,MATCH(MIN(ABS(K2:K14-S2)),ABS(K2:K14-S2),0)),0)</pre>

 

[qrtback10]

Unfortunately no

 

[AhoyNC]

Maybe:

=INDEX(O2:O13,MATCH("AA"&MIN(ABS(K2:K14-S2)),P2:P14&ABS(K2:K14-S2),0))

 

[qrtback10]

Again, no but I think you're on the right track! Maybe I didn't have the formula right to begin with? What I have is in the table below. I need to find the "Maximum Minimum" of S2 (the closest to S2 without going over like the Price is Right), WITH P2:P14 being equal to "AA". Sometimes it works, sometimes it doesn't. I feel like maybe now maybe the problem is with my original formula? Because your answer looks correct as far as what I'm looking for but again, I'm a novice at this obviously. Thoughts?

Beginning code that might not be doing what I need it to be doing:

[COLOR=#000000]=INDEX([/COLOR][COLOR=#F7981D]O2:O13[/COLOR][COLOR=#000000],MATCH(MIN(ABS([/COLOR][COLOR=#7E3794]K2:K14[/COLOR][COLOR=#000000]-[/COLOR][COLOR=#11A9CC]S2[/COLOR][COLOR=#000000])),ABS([/COLOR][COLOR=#7E3794]K2:K14[/COLOR][COLOR=#000000]-[/COLOR][COLOR=#11A9CC]S2[/COLOR][COLOR=#000000]),[/COLOR][COLOR=#1155CC]0[/COLOR][COLOR=#000000]))[/COLOR]

Table that I'm working with: (Highlighted cell is what it should be returning but it's not)

0 BD From	1 Bd	2 BD From	3 BD From	4 BD From	Marker Type	ABS Value	Code	Rating		Bedrooms	Rent Max
					blu_square	CC-ALT-59 L	CC
				blu_square	CC-ALT-3707	AA
				blu_square	CC-ALT-3707	DD
				blu_square	CC-ALT-3740	AA
				buildings	CC--4032	AA
				buildings	CC-VIL-4130	AA
					buildings	CC--2911	BB
			ylw_circle	CC-ASP-3201	BB
		ylw_circle	CC-ASP-2195	AA
	purple_square	CC-DEI-2791	AA
	purple_square	CC-DEI-2122	AA
		purple_square	CC-DEI-1011	BB

<colgroup><col style="width: 100px"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"><col width="100"></colgroup><tbody>
[TD="align: right"]1500[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]1500[/TD]

[TD="align: right"]1502[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]1470[/TD]

[TD="align: right"]30[/TD]

[TD="align: right"]1503[/TD]

[TD="align: right"]3[/TD]

[TD="align: right"]200[/TD]

[TD="align: right"]1500[/TD]

[TD="bgcolor: #ffff00, align: right"]1455[/TD]

[TD="align: right"]45[/TD]

[TD="align: right"]1500[/TD]

[TD="align: right"]335[/TD]
[TD="align: right"]502[/TD]

[TD="align: right"]998[/TD]

[TD="align: right"]675[/TD]
[TD="align: right"]733[/TD]
[TD="align: right"]886[/TD]

[TD="align: right"]614[/TD]

[TD="align: right"]552[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]706[/TD]
[TD="align: right"]1700[/TD]

[TD="align: right"]200[/TD]

[TD="align: right"]522[/TD]
[TD="align: right"]620[/TD]
[TD="align: right"]746[/TD]
[TD="align: right"]2900[/TD]

[TD="align: right"]1400[/TD]

[TD="align: right"]780[/TD]
[TD="align: right"]716[/TD]
[TD="align: right"]1400[/TD]

[TD="align: right"]100[/TD]

</tbody>

I don't know if this is allowed or not, but I'm going to create a new thread based on this issue but PLEASE let me know if you or anyone else has any thoughts!

Thank you

 

[AhoyNC]

See if this works.
NOTE: this is an array formula and must be entered with

Excel Workbook
	H	I	J	K	L	M	N	O	P	Q	R	S
1	0 BD From	1 Bd	2 BD From	3 BD From	4 BD From	Marker Type	ABS Value	Code	Rating		Bedrooms	Rent Max
2						blu_square	1500	CC-ALT-59 L	CC		3	1500
3				1502		blu_square	2	CC-ALT-3707	AA
4				1470		blu_square	30	CC-ALT-3707	DD			CC-VIL-4130
5				1503		blu_square	3	CC-ALT-3740	AA
6		200				buildings	1500	CC--4032	AA
7				1455		buildings	45	CC-VIL-4130	AA
8						buildings	1500	CC--2911	BB
9			335	502		ylw_circle	998	CC-ASP-3201	BB
10		675	733	886		ylw_circle	614	CC-ASP-2195	AA
11	552	800	706	1700		purple_square	200	CC-DEI-2791	AA
12	522	620	746	2900		purple_square	1400	CC-DEI-2122	AA
13		780	716	1400		purple_square	100	CC-DEI-1011	BB
CTRL-SHIFT-ENTER.

 

[qrtback10]

Thank you Ahoy! Does exactly as I want. Much appreciated.

 

[AhoyNC]

You're welcome. Glad that did the trick. Thanks for the feedback.