Return Index Match with multiple criteria (ABS, MIN, possible IF statements)

[qrtback10]

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]

This is the code I was using to begin searching in column P for the Rating of "AA" but could never get it to work so I'm thinking maybe my problem lies within the code above? Obviously am missing what is needed to search column P.

Table that I'm working with: (Highlighted cell (1455 in K7) 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	1500	CC-ALT-59 L	CC		3	1500
			1502		blu_square	2	CC-ALT-3707	AA
			1470		blu_square	30	CC-ALT-3707	DD
			1503		blu_square	3	CC-ALT-3740	AA
	200				buildings	1500	CC--4032	AA
			1455		buildings	45	CC-VIL-4130	AA
					buildings	1500	CC--2911	BB
		335	502		ylw_circle	998	CC-ASP-3201	BB
	675	733	886		ylw_circle	614	CC-ASP-2195	AA
552	800	706	1700		purple_square	200	CC-DEI-2791	AA
522	620	746	2900		purple_square	1400	CC-DEI-2122	AA
	780	716	1400		purple_square	100	CC-DEI-1011	BB

<tbody>
</tbody>

<table cellspacing="0" cellpadding="0" dir="ltr" border="1" fixed;="" font-family:="" arial,="" sans,="" sans-serif;="" border-collapse:="" collapse;="" border:="" 1px="" solid="" rgb(204,="" 204,="" 204);"="" class="wysiwyg_cms_table_grid wysiwyg_dashes" style="width: 500px;" width="500"><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></tbody></table>
PLEASE let me know if you or anyone else has any thoughts!

Thank you

EDIT: My original post on what I thought the issue was is here: http://www.mrexcel.com/forum/excel-questions/830028-should-quick-answer-you-guys.html

 

[AhoyNC]

See my reply under your original post.