Vlookup with Match or Index/Match Approximate Values

bmacias

Board Regular
Joined
Sep 11, 2002
Messages
210
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.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,751
Try this. I added headers for convenience.


ABCDEFGHI
1% DisadvantagedABC%ScoreRating
206545336358B
350715237
475775841

<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>
</tbody>
Sheet6


Worksheet Formulas
CellFormula
I2
=INDEX(B1:D1,AGGREGATE(15,6,(COLUMN(B1:D1)-COLUMN(B1)+1)/(INDEX(B2:D4,MATCH(G2,A2:A4),0)<=H2),1))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,082
Messages
5,466,531
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top