digest
Short Summary
Main recommendation: To VLOOKUP a value from one of 36 separate regional and product-group tables, build the table reference dynamically from each transaction's region and product group, using CHOOSE with MATCH, OFFSET with MATCH, or INDIRECT with defined names. Each method returns the correct table to the VLOOKUP, which then pulls the discount rate from the second column. The video notes the three approaches differ mainly in how much manual setup they require.
Key Points
- The 36 tables each represent one region and one product group, with table names like "West_freestyle" and "West_Belling_group" that do not exactly match the transaction labels, so the formula must join region and product group with an underscore.
- Method 1, CHOOSE: MATCH with a wildcard lookup value returns a position from 1 to 36, which CHOOSE uses to select one of 36 manually listed tables. The downside is the heavy manual clicking, and the advantage is no need to know OFFSET or INDIRECT.
- Method 2, OFFSET: MATCH returns the starting row, OFFSET builds a variable range four rows tall and two columns wide (with the height always set to 4 even though some tables have only 3 rows or as many as 4 products), and that range feeds VLOOKUP.
- Method 3, INDIRECT with defined names: each table is named (for example West_freestyle), the joined text is converted into a reference by INDIRECT, and the result feeds VLOOKUP. The presenter says he almost thinks he likes this solution best.
- All three methods are checked against each other with a COUNTIFS for FALSE values, and the check returned zero mismatches.
Details Worth Noting
- Concrete instructions:
- For the CHOOSE method, use =CHOOSE(MATCH(region&"_*"&product, table list, 0), table1, table2, ..., table36), lock each table reference with F4, then wrap the whole thing in VLOOKUP(product, CHOOSE(...), 2, 0).
- Build the MATCH lookup value by joining region and product with an underscore and a wildcard in quotes, for example region&"_*"&product, so the wildcard absorbs the varying prefix.
- For the OFFSET method, use OFFSET(A7, MATCH(...)-1, , 4, 2) with the starting cell locked with F4, the height set to 4 (three-row tables still work because the extra zeros do not affect results), and the width set to 2, then wrap it in VLOOKUP(product, OFFSET(...), 2, 0).
- For the INDIRECT method, create a defined name for each of the 36 table ranges via the Name Box, then build the name as text with region&""&product and wrap it in INDIRECT, e.g., VLOOKUP(product, INDIRECT(region&""&product), 2, 0).
- To verify any method, compare its results with another method using =product_cell_check, then use COUNTIFS(range, FALSE) to count mismatches; zero means the formulas agree.
- The presenter notes the OFFSET method was chosen to handle tables with different numbers of rows, and the INDIRECT method depends on properly defined names for every table.
Bottom Line
Dynamic table selection lets one VLOOKUP handle many separate tables, and the choice among CHOOSE, OFFSET, and INDIRECT comes down to how much manual setup you want versus how comfortable you are with those functions.
