Vlookup / index over multiple tables

[AceFI]

hey everyone!

Having trouble wraping my head around a formula..
i have 3 tables;

[TABLE="width: 1497"]
<colgroup><col><col><col><col><col><col><col><col span="6"><col><col span="6"></colgroup><tbody>[TR]
[TD="colspan: 6"]Best[/TD]
[TD][/TD]
[TD="colspan: 6"]Great[/TD]
[TD][/TD]
[TD="colspan: 6"]Good[/TD]
[/TR]
[TR]
[TD="colspan: 2"]1 Year[/TD]
[TD="colspan: 2"]3 - 6 Years[/TD]
[TD="colspan: 2"]7 Years[/TD]
[TD][/TD]
[TD="colspan: 2"]1 Year[/TD]
[TD="colspan: 2"]3 - 6 Years[/TD]
[TD="colspan: 2"]7 Years[/TD]
[TD][/TD]
[TD="colspan: 2"]1 Year[/TD]
[TD="colspan: 2"]3 - 6 Years[/TD]
[TD="colspan: 2"]7 Years[/TD]
[/TR]
[TR]
[TD]Quote[/TD]
[TD]Commission[/TD]
[TD]Quote[/TD]
[TD]Commission[/TD]
[TD]APR[/TD]
[TD]Commission[/TD]
[TD][/TD]
[TD]Quote[/TD]
[TD]Commission[/TD]
[TD]Quote[/TD]
[TD]Commission[/TD]
[TD]APR[/TD]
[TD]Commission[/TD]
[TD][/TD]
[TD]Quote[/TD]
[TD]Commission[/TD]
[TD]Quote[/TD]
[TD]Commission[/TD]
[TD]APR[/TD]
[TD]Commission[/TD]
[/TR]
[TR]
[TD="align: right"]1.00%[/TD]
[TD="align: right"]3.00%[/TD]
[TD="align: right"]1.00%[/TD]
[TD="align: right"]4.00%[/TD]
[TD="align: right"]1.00%[/TD]
[TD="align: right"]5.00%[/TD]
[TD][/TD]
[TD="align: right"]2.00%[/TD]
[TD="align: right"]3.00%[/TD]
[TD="align: right"]2.00%[/TD]
[TD="align: right"]4.00%[/TD]
[TD="align: right"]2.00%[/TD]
[TD="align: right"]5.00%[/TD]
[TD][/TD]
[TD="align: right"]6.50%[/TD]
[TD="align: right"]3.00%[/TD]
[TD="align: right"]6.50%[/TD]
[TD="align: right"]4.00%[/TD]
[TD="align: right"]6.50%[/TD]
[TD="align: right"]5.00%[/TD]
[/TR]
[TR]
[TD="align: right"]1.25%[/TD]
[TD="align: right"]2.70%[/TD]
[TD="align: right"]1.25%[/TD]
[TD="align: right"]3.60%[/TD]
[TD="align: right"]1.25%[/TD]
[TD="align: right"]4.45%[/TD]
[TD][/TD]
[TD="align: right"]2.85%[/TD]
[TD="align: right"]2.70%[/TD]
[TD="align: right"]2.85%[/TD]
[TD="align: right"]3.60%[/TD]
[TD="align: right"]2.85%[/TD]
[TD="align: right"]4.45%[/TD]
[TD][/TD]
[TD="align: right"]7.50%[/TD]
[TD="align: right"]2.70%[/TD]
[TD="align: right"]7.50%[/TD]
[TD="align: right"]3.60%[/TD]
[TD="align: right"]7.50%[/TD]
[TD="align: right"]4.45%[/TD]
[/TR]
[TR]
[TD="align: right"]3.00%[/TD]
[TD="align: right"]2.40%[/TD]
[TD="align: right"]3.00%[/TD]
[TD="align: right"]3.20%[/TD]
[TD="align: right"]3.00%[/TD]
[TD="align: right"]3.90%[/TD]
[TD][/TD]
[TD="align: right"]3.00%[/TD]
[TD="align: right"]2.40%[/TD]
[TD="align: right"]3.00%[/TD]
[TD="align: right"]3.20%[/TD]
[TD="align: right"]3.00%[/TD]
[TD="align: right"]3.90%[/TD]
[TD][/TD]
[TD="align: right"]8.50%[/TD]
[TD="align: right"]2.40%[/TD]
[TD="align: right"]8.50%[/TD]
[TD="align: right"]3.20%[/TD]
[TD="align: right"]8.50%[/TD]
[TD="align: right"]3.90%[/TD]
[/TR]
[TR]
[TD="align: right"]3.70%[/TD]
[TD="align: right"]2.10%[/TD]
[TD="align: right"]3.70%[/TD]
[TD="align: right"]2.75%[/TD]
[TD="align: right"]3.70%[/TD]
[TD="align: right"]3.35%[/TD]
[TD][/TD]
[TD="align: right"]3.24%[/TD]
[TD="align: right"]2.10%[/TD]
[TD="align: right"]3.24%[/TD]
[TD="align: right"]2.75%[/TD]
[TD="align: right"]3.24%[/TD]
[TD="align: right"]3.35%[/TD]
[TD][/TD]
[TD="align: right"]8.65%[/TD]
[TD="align: right"]2.10%[/TD]
[TD="align: right"]8.65%[/TD]
[TD="align: right"]2.75%[/TD]
[TD="align: right"]8.65%[/TD]
[TD="align: right"]3.35%[/TD]
[/TR]
[TR]
[TD="align: right"]6.00%[/TD]
[TD="align: right"]1.80%[/TD]
[TD="align: right"]6.00%[/TD]
[TD="align: right"]2.30%[/TD]
[TD="align: right"]6.00%[/TD]
[TD="align: right"]2.80%[/TD]
[TD][/TD]
[TD="align: right"]6.00%[/TD]
[TD="align: right"]1.80%[/TD]
[TD="align: right"]6.00%[/TD]
[TD="align: right"]2.30%[/TD]
[TD="align: right"]6.00%[/TD]
[TD="align: right"]2.80%[/TD]
[TD][/TD]
[TD="align: right"]9.00%[/TD]
[TD="align: right"]1.80%[/TD]
[TD="align: right"]9.00%[/TD]
[TD="align: right"]2.30%[/TD]
[TD="align: right"]9.00%[/TD]
[TD="align: right"]2.80%[/TD]
[/TR]
[TR]
[TD="align: right"]6.74%[/TD]
[TD="align: right"]1.50%[/TD]
[TD="align: right"]6.74%[/TD]
[TD="align: right"]1.85%[/TD]
[TD="align: right"]6.74%[/TD]
[TD="align: right"]2.25%[/TD]
[TD][/TD]
[TD="align: right"]6.74%[/TD]
[TD="align: right"]1.50%[/TD]
[TD="align: right"]6.74%[/TD]
[TD="align: right"]1.85%[/TD]
[TD="align: right"]6.74%[/TD]
[TD="align: right"]2.25%[/TD]
[TD][/TD]
[TD="align: right"]10.00%[/TD]
[TD="align: right"]1.50%[/TD]
[TD="align: right"]10.00%[/TD]
[TD="align: right"]1.85%[/TD]
[TD="align: right"]10.00%[/TD]
[TD="align: right"]2.25%[/TD]
[/TR]
</tbody>[/TABLE]


I would have 3 selection criteria with the ultimate goal to see the 'commission rate'

criteria 1 - Best or Great or Good
Criteria 2 - anything ranging from 1 to 7 'Years'
Criteria 3 - Quote % charged
Outcome - % commission

any idea on this one? thanks

 

[Special-K99]

With your tables covering in A1:T9

and your criteria in A11 A12 A13

=INDEX(A4:T9,MATCH(A13,INDIRECT(LOOKUP(A11,{"Best","Good","Great"},{"A4:A9","O4:O9","H4:H9"}),1)),LOOKUP(A11,{"Best","Good","Great"},{1,15,8})+LOOKUP(A12,{1,3,7},{1,3,5}))

Adjust formula as necessary

 

[AceFI]

Thanks K99
can you please explain {1,15,8})+LOOKUP(A12,{1,3,7},{1,3,5})) to me?

 

[Special-K99]

Pasting your tables into a blank spreadsheet at A1, all the tables are in the same format 6 columns + a blank column by 9 rows.

This part determines the column number to retrieve from the overall range A4:T9.

LOOKUP(A11,{"Best","Good","Great"},{1,15,8})+LOOKUP(A12,{1,3,7},{1,3,5})

The tables start at columns 1 15 and 8 so

LOOKUP(A11,{"Best","Good","Great"},{1,15,8}) returns the start column of each table, 1, 15 or 8 dependent on the name of the table in A11. However LOOKUP() needs the input "Best" "Great" "Goodt" in alphabetical order so I've had to reverse the order of both the input and return values so its Best Good Great and 1, 15, 8 rather than 1, 8, 15.
Having calculated the start column we need to add an offset to retrieve the commission column. So we use LOOKUP(A12,{1,3,7},{1,3,5}) to return a column offset of 1, 3 or 5 dependent on the number of years 1,3, 7, the value being in A12.

So if we have "Good" 4 years and 8.6%
MATCH would return values from O4:O9 ("Good"), the row being determined by the value in A13, ie 8.6%, nearest without going over is 8.5% so row number 3 in the %s (row number 6 in the overall 3 tables).
First LOOKUP returns 15 (Start column of 3rd table), second LOOKUP returns 3 (4 years). Added together is 18 (15 + 3) so we are looking at row 6, column 18 in the range A4:T9 which should return the cell at R9 retrieving 3.2%.

NOTE: The number of years is hard coded into the second LOOKUP so if you change the number of years in the tables you'll have to change it in the second LOOKUP.