How to apply VLOOKUP over a range

Question

I have two tables. Table1 has Names and Marks. Table2 has a mark Range and corresponding Grade

I want to add column Grade in Table1 depending upon the Marks scored

Table1

+------+-------+
| Name | Marks |
+------+-------+
| Ab   |    98 |
| Bb   |    93 |
| Cc   |    87 |
| Dd   |    72 |
+------+-------+

Table2

+--------+-------+
| Range  | Grade |
+--------+-------+
| 91-100 | A     |
| 81-90  | B     |
| 71-80  | C     |
+--------+-------+

My desired output

| Name | Marks | Grade |
+------+-------+-------+
| Ab   |    98 | A     |
| Bb   |    93 | A     |
| Cc   |    87 | B     |
| Dd   |    72 | C     |
+------+-------+-------+

How do I do it with VLOOKUP, if not VLOOKUP what are other good alternatives except for marcros/vba

Answer 1

Change your lookup table to the minimums only and order them ascending:

Then a simple VLOOKUP will work:

=VLOOKUP(B2,F:G,2,TRUE)

---

If you insist on leaving the data as is:

=INDEX(G:G,AGGREGATE(15,6,ROW($F$2:$F$4)/((--LEFT($F$2:$F$4,FIND("-",$F$2:$F$4)-1)<=B2)*(--MID($F$2:$F$4,FIND("-",$F$2:$F$4)+1,3)>=B2)),1))