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