Microsoft Office Tutorials and References
In Depth Information
Specialized Lookup Formulas
Performing a case-sensitive lookup
Excel’s lookup functions (LOOKUP, VLOOKUP, and HLOOKUP) are not case sensitive. For
example, if you write a lookup formula to look up the text budget, the formula considers any of the
following a match: BUDGET, Budget, or BuDgEt.
Figure 8-8 shows a simple example. Range D2:D7 is named Range1, and range E2:E7 is named
Range2. The word to be looked up appears in cell B1 (named Value ).
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 300.
When entering an array formula, remember to use Ctrl+Shift+Enter.
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.