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	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

<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>

<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.