Learn how to create dynamic ranges using OFFSET or INDEX functions for an unusual multiple lookup table situation where the multiple tables are in a single table and the source data from a database can update and change the size of the lookup tables. This amazing suggestion and OFFSET formula comes from Craig.
(00:12) Problem Description
(01:44) Import Table from Access Database so that refreshes can be performed when database table changes.
(02:29) Name Imported Database Table so that our Table Formula Nomenclature (Structured References) can unambiguous.
(02:54) Convert Sales Table to an Excel Table so ranges can be dynamic.
(03:12) Formula to determine the start position of the dynamic lookup table. See the functions MATCH and IFNA (New in Excel 2013)
(05:28) Formula to determine how tall the dynamic lookup table is. See the functions COUNTIFS and INDEX.
(07:36) OFFSET function to define a dynamic lookup table range. Then we use the range in VLOOKUP to lookup a commission rate.
(10:27) INDEX function to define a dynamic lookup table range. Then we use the range in VLOOKUP to lookup a commission rate.
(13:28) Text the Dynamic Sales Table.
(13:47) Update Imported Table when database data changes and watch the dynamic lookup table ranges work perfectly!!