+ Reply to Thread
Results 1 to 4 of 4

Index-Match, Reference Range Jumps 8 Rows if Dragged Down

  1. #1
    Registered User
    Join Date
    10-01-2012
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    34

    Index-Match, Reference Range Jumps 8 Rows if Dragged Down

    Hi members of excel forum..
    I have an index-match formula in a cell of one table.
    I want to fill the rows below that cell by using the formula of that cell (which is the index-match formula).
    By dragging down the cell, as we know the below cells will be filled automatically jumping one row respectively.
    However, my goal is to fill the below cells by a reference range that jumped several number of rows (8 to be precise).
    For easier understanding, I attach the example sheet (pls see the comment box).
    Thank you very much for your attention and assistance.
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Index-Match, Reference Range Jumps 8 Rows if Dragged Down

    hi there. 2 possible methods:
    =INDEX(D$6:D$45,MATCH($C49,$B$6:$B$45,0)+7)

    or:
    =INDEX(D$6:D$45,(ROWS(D$48:D49)-1)*8)

    Edit: didn't realize it was for "LOB3". Phuocam's solution would work by adding a MATCH formula of "LOB3". but that is assuming the sequence is always the same as Dept 1. the same assumption can be used with this:
    =INDEX(D$6:D$45,3+(ROWS(E$49:E49)-1)*8)
    Last edited by benishiryo; 09-22-2017 at 12:51 AM. Reason: addition

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,908

    Re: Index-Match, Reference Range Jumps 8 Rows if Dragged Down

    Try:

    =INDEX(D$6:D$45,MATCH($C49,$B$6:$B$45,0)+MATCH("LOB 3",$C$6:$C$45,0)-1)

  4. #4
    Registered User
    Join Date
    10-01-2012
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Office Professional Plus 2019
    Posts
    34

    Re: Index-Match, Reference Range Jumps 8 Rows if Dragged Down

    Quote Originally Posted by Phuocam View Post
    Try:

    =INDEX(D$6:D$45,MATCH($C49,$B$6:$B$45,0)+MATCH("LOB 3",$C$6:$C$45,0)-1)
    Really sorry not replying to the replies since I re-arrange my table.
    However, I've tried each solution given and formula from Phuocam works really well.
    Thank you very much for the help!

+ 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. [SOLVED] Copying a Formula over multiple rows without changing the INDEX MATCH cell reference.
    By nathandavies9 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-16-2017, 07:57 AM
  2. Finding cell reference in range (opposite to index match)
    By kennydies in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-08-2016, 09:31 AM
  3. [SOLVED] Formula to reference a list skipping 4 rows when dragged down
    By jstk886 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-10-2015, 07:55 PM
  4. INDEX MATCH with Rows and Columns as reference
    By Locust in forum Excel General
    Replies: 4
    Last Post: 06-11-2014, 02:35 PM
  5. Dynamic Named Range + Index/Match Returning Circular Reference
    By jennarenae in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-29-2014, 06:23 PM
  6. Replies: 4
    Last Post: 01-13-2014, 04:16 AM
  7. [SOLVED] Reference to named range to be used in index match formula!! Help Please!!
    By Optimum in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-24-2013, 06:54 AM

Tags for this Thread

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