Using Multiple LOOKUP tables

[KG]

I have never used multiple tables, so I am hoping that you can steer me in
the right direction:

I have a list of five possible Customer names & four product classes that
apply to all of them:
Premium Products
Economy Products
Seconds
Imports

Then there would be be five lookup tables with commission rates; Table1 will
apply to Customer A, Table2 to Customer B etc. All five commission tables
would be of the same design:

Premium Products, Economy Products, Seconds and Imports will be the row
labels. The column headings would read 2005, 2006, 2007, etc. and in the
intersects there will be commission rates in percent.

So, now I want to populate a table with commission payments by product and
by year, depending on the customer name to be entered in cell B2. If the
Customer name is "A", I would want want to go to Table1, if Customer "B" to
Table2, etc.

I presume that this would require the use of MATCH and VLOOKUP, but I am not
sure if there are other steps

 

[Domenic]

First, define a name for each of your tables. For this example, we'll
name the first table CustomerA, the second table, CustomerB, etc. But
don't include the column and row headers in the reference. So, for
example, if Sheet2!A15 contains your first table...

Insert > Name > Define

Name: CustomerA

Refers to: =Sheet2!$B$2:$D$5

Click Add, and continue with the remaining tables. Notice the column
and row headers are not included.

Secondly, set up a table with the column and row headings only, let's
say B4:E8, something like this...

Product Classes..........2005.....2006.....2007
Premium Products
Economy Products
Seconds
Imports

Then, select C5:E8 (these cells should be highlighted) and enter the
following formula...

=INDEX(INDIRECT(B2),0,0)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER, and where B2
contains the customer name, such as CustomerA.

Hope this helps!

 

[Dave Peterson]

I think I would use 5 different worksheets.
Build each table on each worksheet.
Use insert|name define to name each of the tables (table1, table2, ..., table5).

Then I'd create a formula that tells me which table to use--maybe an embedded
=if() formula or even a =vlookup() formula.

=if(custname="aa","table1",if(custname="bb","table2",...etc)

In my case, I stuck that in A1.
Then I put the class that I wanted in B1.
(or a formula that returned one of those classes???)
Then I put the year in C1.
(or a formula that returned one of the years????)

Then to return the value from that table, I used this formula:

=INDEX(INDIRECT(A1),MATCH(B1,INDEX(INDIRECT(A1),,1),0),
MATCH(C1,INDEX(INDIRECT(A1),1,),0))

(One cell).

The =indirect(a1) is the pointer to the correct table.
The =index(indirect(a1),,1) points at the first column in that table--the
classes.
The =index(indirect(a1),1,) points at the first row in that table--the years.

If you want a little instruction for =vlookup() and =index(match()), you can
turn to Debra Dalgleish's site:
http://www.contextures.com/xlFunctions02.html

=============
These tables can actually be on one sheet. But I've always had better luck with
updates when I put them in different sheets--it makes inserting/deleting
rows/columns much easier.

If you think that table will grow (more rows or columns), Debra also has some
instructions on how to create a dynamic range name at:
http://www.contextures.com/xlNames01.html#Dynamic

 

[KG]

Thank you both! I think I have enough to go on...