Usually when I use VLOOKUP, I want to pull information for an exact match. For example, if I enter a customer number in one cell, I want the customer name in the adjacent cell. I don’t want the name of a customer whose number is CLOSE to the one that I entered.

In some situations though, an approximate match is preferred, so several values will return the same result. For example, when working with student grades, I don’t want to create a lookup table where I have to enter every possible percentage. Instead, my lookup table should have the starting percentage for each grade. Based on the table below, all percentages of 85 or over should receive an A grade.

### The Lookup Table

In this example, the lookup table is created on a sheet named * Grades*. To create the lookup table:

- In the first column, enter the minimum percentage for each grade.
- In the second column, enter the matching Grade for each percentage.
- Sort the percentages in Ascending order. If not sorted A-Z, the results of the VLOOKUP formula might be wrong.

### The Report Card

The scores are entered on a sheet named * Report Card*, where a VLOOKUP formula calculates the grade.

- On the Report Card sheet, in cell B4, enter the percentage — 77.
- In cell C4, enter the VLOOKUP formula:

**=VLOOKUP(B4,Grades!$B$4:$C$8,2,TRUE)**

- Press the Enter key, and the grade for English — B — is returned.
- Copy the formula down to the remaining cells in the report card.

### How It Works

- In the VLOOKUP formula, the first argument,
**B4**, is the value that we want to look up in the Grades table. - The second argument,
**Grades!$B$4:$C$8**, is the location of the lookup table. The reference is absolute $B$4:$C$8, instead of relative, so that the VLOOKUP formula can be copied to other cells, and the table reference won’t change. - The third argument, 2, is the column number in the lookup table, where the Grades are located. When a percentage match is found in the first column, a letter grade from the 2nd column will be returned.
- The fourth argument,
**TRUE**, means that an approximate match is okay. This is the default setting, and if you omit TRUE, you’ll get the same result from the formula.

### Watch the Video

If you’d like to see the steps in action, you can watch the short video below.

____________________

Always use Index/Match

That way the Marks can be sorted both Ascending or Descending

Sam,

Even with the INDEX/MATCH approach, the array has to be sorted ascending or descending in order to return a value when the lookup_value is not found.

Jason

does not work

@dan, what did you try that didn’t work?

Awesome tutorial. I teach high school students and i always use excel to do the calculations of their grades. Keep it up!

I made it! thanks for the tutorial!

yes, its work, thanks…i finished my work today is about only 1 hours for 25 classes…tq so much!!

Super helpful! Thank you SO much!

I have 64 students doing 11 subjects and I need to generate report card for them automatically. they are all in one sheet. how can I do it

In case anyone has trouble with this, it might be because your results sheet has a % on it and not a whole number. Either fill the lookup table with % numbers or change the results sheet.