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.
{=INDEX(Range2,MATCH(TRUE,EXACT(Value,Range1),0))}
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.
 
Search JabSto ::




Custom Search