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
Search JabSto ::




Custom Search