Vlookup with Match or Index/Match Approximate Values

[bmacias]

I'm at my wits-end with this one. I think the solution is staring me in the face, but I just can't get it.

I have a cross table

a1 = 0 b1=65 c1=45 d1=33
a2 = 50 b2=71 c2=52 d2=37
a3 = 75 b3=77 c3=58 d3=41

Column "A" is the percent of Economically Disadvantaged Students in the School. Column "B" is the cut-score needed for an "A" rating, Column "C" is the cut-score needed for an "B" rating, and Column "D" is the cut-score needed to earn a "C".

So supposed I have a school that has 63 (housed in cell G1) percent Economically and their score was a 58 (housed in cell H1), using our table that campus earns an "B" rating. How can I arrive at that formulaically?

I can move the cross-tab table columns/rows around if needed.

 

[Eric W]

Try this. I added headers for convenience.

	A	B	C	D	E	F	G	H	I
% Disadvantaged	A	B	C	%	Score	Rating
B

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]58[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]77[/TD]
[TD="align: right"]58[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6
​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: <a href="]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: <a href="]I2
[/TH]
[TD="align: left"]=INDEX(B1:D1,AGGREGATE(15,6,(COLUMN(B1:D1)-COLUMN(B1)+1)/(INDEX(B2:D4,MATCH(G2,A2:A4),0)<=H2),1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[bmacias]

Thank you Sir!...Works Perfectly

 

[Eric W]

You're welcome!