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
=VLOOKUP(B2,GradeList,2)
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:
=VLOOKUP(B2,{0,”F”;40,”D”;70,”C”;80,”B”;90,”A”},2)
 
Search JabSto ::




Custom Search