+ Reply to Thread
Results 1 to 17 of 17

Drag Vlookup Col Index

  1. #1
    Registered User
    Join Date
    10-18-2016
    Location
    Frankfurt, Germany
    MS-Off Ver
    2010
    Posts
    82

    Exclamation Drag Vlookup Col Index

    How can I drag a vlookup formula to look at the same range but change the colom index by 1 increment to refer to the corresponding colom? --- I tried Index Match too

    Example; vlookup(A2, C2:G5, 2, false) should change to vlookup(A2, C2:G5, 3, false) for the next colom I drag it to etc.

    I have attached example file. Tracker tab is pulling shift time according to date with one minute leeway to make way for lateness.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Drag Vlookup Col Index

    Use this:

    COLUMNS($A:B) in place of the 2

    This will increment from 2 in steps of 1 as you drag to the right. Incidentally, you shouldn't use whole column references (A:D), unless you really do have more than 1,000,000 rows of data!! It'll just slow things up.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

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

    Re: Drag Vlookup Col Index

    Try this ...

    =VLOOKUP($A3,Shifts!$A:$D,COLUMNS($A$2:B$2),0)

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Drag Vlookup Col Index

    maybe: =(((LEFT(VLOOKUP($A3,Shifts!$A:$D,COLUMN(B2),FALSE),5))+0.2)-0.2)+0.000694444444444444

  5. #5
    Registered User
    Join Date
    10-18-2016
    Location
    Frankfurt, Germany
    MS-Off Ver
    2010
    Posts
    82

    Re: Drag Vlookup Col Index

    Quote Originally Posted by sandy666 View Post
    maybe: =(((LEFT(VLOOKUP($A3,Shifts!$A:$D,COLUMN(B2),FALSE),5))+0.2)-0.2)+0.000694444444444444
    This is the one that looks to have worked, however on the revised file, it does Not work?

    I am getting #N/A and #REF errors. New tab Check is when users log in reflected in "login" and "status" coloms on Tracker tab.

    Weekday coloms should pick up the shift times from Shift tab by increment....
    Attached Files Attached Files

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Drag Vlookup Col Index

    I can sssure you that the solutions provided by Phuocam and myself DO work. Check that you copied them correctly.

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Drag Vlookup Col Index

    Quote Originally Posted by manny88 View Post
    This is the one that looks to have worked, however on the revised file, it does Not work?

    I am getting #N/A and #REF errors. New tab Check is when users log in reflected in "login" and "status" coloms on Tracker tab.

    Weekday coloms should pick up the shift times from Shift tab by increment....
    It works just in this case. Not everywhere.
    See Phuocam and Gleen solutions

  8. #8
    Registered User
    Join Date
    10-18-2016
    Location
    Frankfurt, Germany
    MS-Off Ver
    2010
    Posts
    82

    Re: Drag Vlookup Col Index

    Quote Originally Posted by Glenn Kennedy View Post
    I can sssure you that the solutions provided by Phuocam and myself DO work. Check that you copied them correctly.
    Yes, it works for *that* case but when I highlight B3:D3 and drag it across, it shows me errors in return. Am I doing something wrong here?

  9. #9
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Drag Vlookup Col Index

    Could you attach example xlsx file which reflect your data structure?

    or try this: =(((LEFT(VLOOKUP($A3,Shifts!$A:$D,COLUMNS($A2:B2),0),5))+0.2)-0.2)+0.000694444444444444
    if you want use another range you need change references suitable but you should remeber about $ signs there where it is required.

    Quote Originally Posted by manny88 View Post
    Yes, it works for *that* case but when I highlight B3:D3 and drag it across, it shows me errors in return. Am I doing something wrong here?
    Select B3 only and drag to the right and next down as far as you need.
    Attached Files Attached Files
    Last edited by sandy666; 03-01-2017 at 11:01 AM. Reason: typo :)

  10. #10
    Registered User
    Join Date
    10-18-2016
    Location
    Frankfurt, Germany
    MS-Off Ver
    2010
    Posts
    82

    Re: Drag Vlookup Col Index

    Quote Originally Posted by sandy666 View Post
    Could you attach example xlsx file which reflect your data structure?

    or try this: =(((LEFT(VLOOKUP($A3,Shifts!$A:$D,COLUMNS($A2:B2),0),5))+0.2)-0.2)+0.000694444444444444
    if you want use another range you need change references suitable but you should remeber about $ signs there where it is required.


    Select B3 only and drag to the right and next down as far as you need.
    Still not happening for me. I have attached file where I applied your ammended formula but receieved errors when dragging B3:D3 (Monday, login, status) to E3:G3 (Tuesday, login, status)
    Attached Files Attached Files

  11. #11
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Drag Vlookup Col Index

    =IFERROR((((LEFT(VLOOKUP($A3,Shifts!$A:$G,COLUMNS($A2:B2),0),5))+0.2)-0.2)+0.000694444444444444,"No data in source")

    Like I said before: change range suitable. In your 2nd example you have different range than the 1st.

    1st: Shifts!$A:$D
    2nd: Shifts!$A:$G

    edit:
    ups... I c there is different request
    Last edited by sandy666; 03-01-2017 at 11:34 AM.

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Drag Vlookup Col Index

    Try

    =(((LEFT(VLOOKUP($A3,Shifts!$A:$G,INT(COLUMN(B2)/3)+2,0),5))+0.2)-0.2)+0.000694444444444444

  13. #13
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Drag Vlookup Col Index

    I wonder why +0.2 and next -0.2? Maybe enough: +0.000694444444444444 ?

  14. #14
    Registered User
    Join Date
    10-18-2016
    Location
    Frankfurt, Germany
    MS-Off Ver
    2010
    Posts
    82

    Re: Drag Vlookup Col Index

    Quote Originally Posted by JohnTopley View Post
    Try

    =(((LEFT(VLOOKUP($A3,Shifts!$A:$G,INT(COLUMN(B2)/3)+2,0),5))+0.2)-0.2)+0.000694444444444444
    This works! But it stops picking up if I drag past Friday. Can you suggest how I can have this to work regardless how far I drag it across.

    I tried dropping one of the $ in the array =(((LEFT(VLOOKUP($A3,Shifts!$A:$G,INT(COLUMN(B2)/3)+2,0),5))+0.2)-0.2)+0.000694444444444444

    But that didn't work

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,423

    Re: Drag Vlookup Col Index

    When you say drag I assume that you mean you copied the formula from column B to columns E, H... Q as those are the columns to which the formula applies. When I copy the formula to column Q (Saturday) it displays the following values in Q3:Q5 respectively 10:01:00 AM, 8:01:00 AM, 8:01:00 AM Those values correspond to values (+ the specified one minute) on the shift sheet for the respective employees. If those are not the values you expect in that column then please let us know what values are expected.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Drag Vlookup Col Index

    See attached: is this what you mean?

    =(((LEFT(VLOOKUP($A3,Shifts!$A:$ZZ,INT(COLUMN(W2)/3)+2,0),5))+0.2)-0.2)+0.000694444444444444
    Attached Files Attached Files

  17. #17
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Drag Vlookup Col Index

    John, I think he needs something like this.
    I don't have vlookup or index/match there but it works (I hope)
    Attached Files Attached Files

+ 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] 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
  2. Cant drag down index match formula
    By Sk8ect in forum Excel General
    Replies: 2
    Last Post: 04-18-2016, 02:25 AM
  3. [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
  4. [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
  5. 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
  6. Vlookup but change column index number as you drag to next column
    By yankeekid86 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-29-2013, 09:46 AM
  7. vlookup help:When I drag the formula
    By Stretch617 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-21-2008, 01:27 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