Multiple table lookup

[Guest]

How do I set the LOOKUP formulas for this situation:

TABLE 1:
* Column A displays the names of salesmen (10)
* Cells B1:F1 display the names of Products (5)
* In cells B2:F11 are displayed the % of budget achieved by each salesman
for each of the 5 products

TABLE 2:
In a separate table I set up a commission matrix showing the % commission
due for each product according to the % of budget achieved; the names of the
products run across the top, the % achieved vs. budget in column A, and the %
commission payable in the intersecting cells

TABLE 3:
Contains the lookup formulas that calculate how much commission (in $) is
due to each salesman for each product according to the sales results achieved

 

[Biff]

Hi!

Table 2 would need to be in ascending order based on column A. Do these
values represent a range? For instance:

0 - 10
11 - 20
21 - 30

If so, the limits need to be in separate columns. Then you would do a lookup
on the lower limit.

Assume Table 3 is in the range H1:M11. I1:M1 are the product headers. H2:H11
are the sales names.

Assume Table 2 is in the range A14:G23. A14:A23 are the lower limits of the
commision range:

0
11
21
31
41
...
...
91

The basic formula would be something like this entered in I2:

=VLOOKUP(B2,$A$14:$G$23,COLUMN(C1),1)

Copied across then down. A more robust formula would be:

=VLOOKUP(B2,$A$14:$G$23,MATCH(I$1,$A$13:$G$13,0),1)

Where the MATCH function is used to determine which column index to use.

Biff