Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Using Multiple LOOKUP tables
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 |
#2
|
|||
|
|||
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!A1:D5 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! In article , "KG" wrote: 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 |
#3
|
|||
|
|||
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","table 2",...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 wrote: 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 -- Dave Peterson |
#4
|
|||
|
|||
Thank you both! I think I have enough to go on...
"Domenic" wrote: 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!A1:D5 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! In article , "KG" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple lookup value's | Excel Worksheet Functions | |||
lookup tables in cells | Excel Worksheet Functions | |||
Multiple Criteria Lookup Question | Excel Discussion (Misc queries) | |||
Return Multiple Results with Lookup | Excel Worksheet Functions | |||
lookup multiple occurrences of a value excel | Excel Worksheet Functions |