Microsoft Office Tutorials and References
In Depth Information
Figure 8-8: Using an array formula to perform a case-sensitive lookup.
The array formula that follows is in cell B2. This formula does a case-sensitive lookup in Range1 and returns
the corresponding value in Range2.
The formula looks up the word DOG (uppercase) and returns 700. The following standard LOOKUP formula
(which is not case sensitive) returns 300:
When entering an array formula, remember to use Ctrl+Shift+Enter, and do not type the
Choosing among multiple lookup tables
You can, of course, have any number of lookup tables in a worksheet. In some cases, your formula may need to
decide which lookup table to use. Figure 8-9 shows an example.
Figure 8-9: This worksheet demonstrates the use of multiple lookup tables.
This workbook calculates sales commission and contains two lookup tables: G3:H9 (named Table1 ) and J3:K8
(named Table2 ). The commission rate for a particular sales representative depends on two factors: the sales