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.

{=INDEX(Range2,MATCH(TRUE,EXACT(Value,Range1),0))}

The formula looks up the word
DOG
(uppercase) and returns 700. The following standard LOOKUP formula

(which is not case sensitive) returns 300:

=LOOKUP(Value,Range1,Range2)

When entering an array formula, remember to use Ctrl+Shift+Enter, and do not type the

curly brackets.

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