+ Reply to Thread
Results 1 to 3 of 3

Index Match formula - drag both down and right

  1. #1
    Registered User
    Join Date
    01-17-2022
    Location
    New York City
    MS-Off Ver
    16.56 (Mac)
    Posts
    1

    Index Match formula - drag both down and right

    Hi there,

    I'm an absolute noob on Excel, so apologies if this is a stupid question.

    I'm trying to merge 2 tables (on an excel I'm on and on a file called Q4Data)
    I could use a VLOOKUP but it's so annoying to manually have to update the column number e.g =VLOOKUP(C2,'[Q4Data.xlsx]Sheet 1'!A:D,4,FALSE) for 20 columns (basically go in each cell to update the 3rd item to 4,5,6,7, etc.) before I drag down everything to fill in the rows (if there's a shortcut here, I'm a taker!)


    I thought I'd have better luck with an indexmatch function.

    I have this =INDEX('[Q4Data.xlsx]Sheet 1'!$G$2:$AC$169,MATCH(D2,'[Q4Data.xlsx]Sheet 1'!$C:$C,0),MATCH(X1,'[Q4Data.xlsx]Sheet 1'!$G$1:$AC$1,0))

    - To drag down I can lock $X$1 (column header) so that D2 updates to D3, D4, etc
    - To drag right I can lock $D$2 (client name that I need to match) to that it's X1 that changes to Z1, AA1, etc.

    BUT obviously now I can't either drag down or right as the wrong cell is locked.
    Is there a shortcut I don't know? OR a better formula altogether?

    Keen to learn!
    Thank you so much.

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Index Match formula - drag both down and right

    the two formulas you show (vlookup and index/match/match) do not look equivalent so I will address the vlookup formula.
    first, $C$1 locks in C1 whether dragged up or down or left or right.
    $C1 locks in column C but not row 1 so when dragged down it will index to $C2, $C3 etc but when dragged right it will stay in column C.
    C$1 will lock in row 1 but will index to B or D etc when dragged left or right.
    now in your vlookup formula you can incorporate a column formula into the index process so as when dragged right it will index to 5 then 6 etc as far as you wish to drag it.

    so here is how something like that would look... =VLOOKUP(C2,'[Q4Data.xlsx]Sheet 1'!$A:D,COLUMN(D$1),FALSE)
    that will index cell C2 as the lookup cell to D2 or C3 when dragged right or down and Sheet 1'$A:D will keep the first lookup of whatever is in C2 (or D2 etc) looking in column A of sheet1 but will move to column E if dragged right to look like this... =VLOOKUP(C2,'[Q4Data.xlsx]Sheet 1'!$A:E,COLUMN(E$1),FALSE)
    the formula COLUMN(D1) will return the number 4, COLUMN(E1) will return 5 and so on when dragged right. So this way you don't have to keep updating to 5, 6, 7 etc as you drag right.
    Hope that helps.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index Match formula - drag both down and right

    See if this will help explain it better for you...

    $ is used to absolute a cell reference, locking it so that when copied, it doesnt change.. it comes in 3 different "flavors...

    $A$1 absolutes the entire cell reference, it will not chance when copied down or across
    $A1 absolutes only the column, "A" will not change when copied across, but "1" WILL change when copied down
    A$1 absolutes only the row, "1" will not change when copied down, but "A" will change when copied across

    So to copy D2 down and across so that D stays D, but 2 changes, it would be $D2
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. Need Help with INDEX MATCH skipping rows during drag down in filtered column
    By luduca444 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-15-2021, 04:12 PM
  2. [SOLVED] How to drag/copy an INDEX MATCH along columns and have the column move with it
    By iantix in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-25-2017, 10:36 AM
  3. Cant drag down index match formula
    By Sk8ect in forum Excel General
    Replies: 2
    Last Post: 04-18-2016, 02:25 AM
  4. [SOLVED] index match with array formula drag down problem
    By alcalina in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-23-2016, 03:59 PM
  5. [SOLVED] Index-Match drag in rows Horizontally
    By Formula-seeker in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-14-2015, 09:35 PM
  6. How do you drag down an index match function with multiple criteria?
    By larryg003 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-28-2014, 01:26 PM
  7. Replies: 3
    Last Post: 05-02-2013, 01:31 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