+ Reply to Thread
Results 1 to 6 of 6

Formulas in Excel 365 that SPILL break indirect lookup using INDEX

  1. #1
    Registered User
    Join Date
    10-20-2020
    Location
    Woodbury, NY
    MS-Off Ver
    Office 365
    Posts
    3

    Formulas in Excel 365 that SPILL break indirect lookup using INDEX

    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]
    Attached Files Attached Files
    Last edited by kirby007; 10-20-2020 at 12:30 PM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Formulas in Excel 365 that SPILL break indirect lookup using INDEX

    Hi
    manually adding some results would really help validate answers

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Formulas in Excel 365 that SPILL break indirect lookup using INDEX

    Hi & welcome to the board.
    How about
    Replace the values in col A with this in A4
    =SEQUENCE(23,,0)
    remove the B3:G3 values & put this in B3
    =SEQUENCE(,6,0)

    Then you can use this in B4
    =VLOOKUP(INDEX(I3:N14,MOD(A4#,12),B3#),P4:Q15,2,FALSE)

  4. #4
    Registered User
    Join Date
    10-20-2020
    Location
    Woodbury, NY
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Formulas in Excel 365 that SPILL break indirect lookup using INDEX

    Pepe,

    I added some manual results the the shaded region on my example spreadsheet and reuploaded.

    -Kirby

  5. #5
    Registered User
    Join Date
    10-20-2020
    Location
    Woodbury, NY
    MS-Off Ver
    Office 365
    Posts
    3

    Re: Formulas in Excel 365 that SPILL break indirect lookup using INDEX

    Quote Originally Posted by Fluff13 View Post
    Hi & welcome to the board.
    Thank you!!

    Quote Originally Posted by Fluff13 View Post
    How about
    Replace the values in col A with this in A4
    =SEQUENCE(23,,0)
    remove the B3:G3 values & put this in B3
    =SEQUENCE(,6,0)

    Then you can use this in B4
    =VLOOKUP(INDEX(I3:N14,MOD(A4#,12),B3#),P4:Q15,2,FALSE)
    Your suggestion got me onto the right track. For some reason, starting the index with 0 resulted in duplicate index results because the index of 0,0; 0,1; 1,0 and 1,1 all returned a key of 2 which returned "Charlie" with the vlookup.

    You can see what happened here [I truncated the Values to 3 letters in this reply so they would post in columns:

    0 1 2 3 4 5
    0 Cha Cha Fox Kil Del Hot
    1 Cha Cha Fox Kil Del Hot
    2 Del Del Gol Lim Ech Ind
    3 Ech Ech Hot Alp Fox Jul
    4 Fox Fox Ind Bra Gol Kil
    5 Gol Gol Jul Cha Hot Lim
    6 Hot Hot Kil Del Ind Alp
    7 Ind Ind Lim Ech Jul Bra
    8 Jul Jul Alp Fox Kil Cha
    9 Kil Kil Bra Gol Lim Del
    10 Lim Lim Cha Hot Alp Ech
    11 Alp Alp Del Ind Bra Fox
    12 Cha Cha Fox Kil Del Hot
    13 Cha Cha Fox Kil Del Hot
    14 Del Del Gol Lim Ech Ind
    15 Ech Ech Hot Alp Fox Jul
    16 Fox Fox Ind Bra Gol Kil
    17 Gol Gol Jul Cha Hot Lim
    18 Hot Hot Kil Del Ind Alp
    19 Ind Ind Lim Ech Jul Bra
    20 Jul Jul Alp Fox Kil Cha
    21 Kil Kil Bra Gol Lim Del
    22 Lim Lim Cha Hot Alp Ech



    Modifying the formula you provided to offset +1 on row and column generated the results needed, for all 23 rows.

    =VLOOKUP(INDEX(I3:N14,MOD(A4#,12)+1,B3#+1),$P$4:$Q$15,2,FALSE)

    0 1 2 3 4 5
    0 Cha Fox Kil Del Hot Gol
    1 Del Gol Lim Ech Ind Hot
    2 Ech Hot Alp Fox Jul Ind
    3 Fox Ind Bra Gol Kil Jul
    4 Gol Jul Cha Hot Lim Kil
    5 Hot Kil Del Ind Alp Lim
    6 Ind Lim Ech Jul Bra Alp
    7 Jul Alp Fox Kil Cha Bra
    8 Kil Bra Gol Lim Del Cha
    9 Lim Cha Hot Alp Ech Del
    10 Alp Del Ind Bra Fox Ech
    11 Bra Ech Jul Cha Gol Fox
    12 Cha Fox Kil Del Hot Gol
    13 Del Gol Lim Ech Ind Hot
    14 Ech Hot Alp Fox Jul Ind
    15 Fox Ind Bra Gol Kil Jul
    16 Gol Jul Cha Hot Lim Kil
    17 Hot Kil Del Ind Alp Lim
    18 Ind Lim Ech Jul Bra Alp
    19 Jul Alp Fox Kil Cha Bra
    20 Kil Bra Gol Lim Del Cha
    21 Lim Cha Hot Alp Ech Del
    22 Alp Del Ind Bra Fox Ech


    I can post the full revised spreadsheet if you think it will benefit the forum.
    Last edited by kirby007; 10-20-2020 at 01:03 PM.

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Formulas in Excel 365 that SPILL break indirect lookup using INDEX

    Glad to help & thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 01-27-2021, 12:07 PM
  2. [SOLVED] Combining formulas INDIRECT & LOOKUP & VLOOKUP
    By AllanSE in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-09-2020, 03:07 PM
  3. Spill Error with index match formula
    By Daniel_ISS in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-10-2020, 09:36 AM
  4. Some formulas break when Lookup data is moved?
    By CWatsonJr in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-13-2018, 02:15 PM
  5. [SOLVED] Adding Indirect with ROW , Index formulas
    By sooghtac in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-23-2017, 07:42 AM
  6. [SOLVED] Code for index, indirect, lookup etc
    By moonbreakker in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-03-2015, 04:47 AM
  7. [SOLVED] Double Index Match by Indirect Lookup Arrays
    By Erickson28 in forum Excel General
    Replies: 18
    Last Post: 07-30-2015, 12:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1