I had a tried and true indirect lookup pattern that would enable me to pull a key value from a using INDEX and use VLOOKUP for the indirection. an example spreadsheet is attached. Now when formulas contain an INDEX function they will automatically SPILL down and over into other cells to match the size of the Key lookup table. I don't want that feature enabled at all in this circumstance. I have tried to understand the difference between dynamic formulas and "CSE" formulas, but testing variants have not yielded a successful lookup. The issue is that the row index that I am using can be greater than the size of the table (which may only be 12 rows tall), and I use the MOD function to wrap the indices around to values between 0 and 11.
The formula that fails is "=VLOOKUP(INDEX(I2:N13,MOD($A4,12),B$3),$P$2:$Q$13,2,FALSE)"
The VLOOKUP works fine if provided with a default index "=VLOOKUP(4,$P$1:$Q$13,2,FALSE)" on the table returns "Echo".
The part of the formula that forces a dynamic replication to a 6 x 12 table (when I want a 6 x 23 table) is:
INDEX(I2:N13,MOD($A4,12),B$3)
Key Index Table
2 5 10 3 7 6
3 6 11 4 8 7
4 7 0 5 9 8
5 8 1 6 10 9
6 9 2 7 11 10
7 10 3 8 0 11
8 11 4 9 1 0
9 0 5 10 2 1
10 1 6 11 3 2
11 2 7 0 4 3
0 3 8 1 5 4
1 4 9 2 6 5
Key Value Table
Key Value
0 Alpha
1 Bravo
2 Charlie
3 Delta
4 Echo
5 Foxtrot
6 Golf
7 Hotel
8 India
9 Juliet
10 Kilo
11 Lima
[Edited - fixed formula error in spreadsheet. updated spreadsheet is now attached]
Bookmarks