Excel Magic Trick 1290

VLOOKUP to Multiple Dynamic Lookup Tables Listed as Single Downloaded Table

DESCRIPTION

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!!
(14:08) Summary

WORKBOOKS

Download “Excel Magic Trick 1281-1300.zip” Excel-Magic-Trick-1281-1300.zip – Downloaded 775 times – 4 MB




Be the first to comment

Leave a Reply