Microsoft Office Tutorials and References

In Depth Information

**Using VLOOKUP with TRUE to Find a Value Based on a Range**

11. Because in this version of VLOOKUP you do not have to list every

possible grade, build a table showing the scores where the grading

scale changes from one grade to the next.

22. Although the published grading scale starts with the higher values,

your lookup table must be sorted in ascending sequence. This requires

a bit of translation as you set up the table. Although the grading

scale says below 65 is an F, you need to set up the table to show that

an F corresponds to any grade at 0 or higher. Therefore, in cell E2

enter 0, and in cell F2, enter F (see
Figure 12.22
)
.

Figure 12.22.

Figure 12.22.
The

The VLOOKUP

VLOOKUP formula in column C finds the correct grade from

formula in column C finds the correct grade from

the table in columns E and F.

the table in columns E and F.

33. Continue building the grading scale in successive rows of columns E

and F. Anything above a 65 is given a D. Anything above 70 is given a C.

Note that this is somewhat counterintuitive because it is the opposite

order that you would use if you were building a grading scale using

nested IF functions.

44. Ensure that the numeric values are the leftmost column in your look-

up table. In
Figure 12.22
,
the lookup table range is E2:F6. When you use

VLOOKUP, Excel searches the first column of the lookup table for

the appropriate score.