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.