Microsoft Office Tutorials and References
In Depth Information
Performing a Lookup by Using an Array
Performing a Lookup by Using an Array
If your lookup table is small, you might be able to avoid using a table altogether and store the
lookup information in an array. This tip describes a typical lookup problem that uses a standard
lookup table — and an alternative method that uses an array.
Using a lookup table
Figure 123-1 shows a worksheet with student test scores. The range E2:F6 (named GradeList)
displays a lookup table used to assign a letter grade to a test score.
Figure 123-1: Looking up letter grades for test scores.
Column C contains formulas that use the VLOOKUP function and the lookup table to assign a
grade based on the score in column B. The formula in C2, for example, is
Using an array
When the lookup table is small (as in this example), you can use a literal array in place of the
lookup table. Using an array in lieu of a table can remove a bit of clutter on your worksheet. The
following formula, for example, returns a letter grade without using a lookup table. Rather, the
information in the lookup table is hard-coded into an array constant. Note the use of curly braces
to indicate the array and note the use of semicolons to separate rows:
Search JabSto ::

Custom Search