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

Custom Search