+ Reply to Thread
Results 1 to 4 of 4

return row content and expand Horizontal Offset

  1. #1
    Registered User
    Join Date
    03-03-2016
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    28

    return row content and expand Horizontal Offset

    1. Row Return: Index Match query
    I am trying to return the date (Column A3:A12) based on the name E.g. D2 being found in the table (M3:AD12). I can't get the index matching to work as i have for the time (e.g. D13) corresponding to the name D2.

    2. I have a Horizontal offset drop down in M3:AD12 however it will only find the first 5 (D2:H2) of D2:L2. I am currently using the following formula as for some reason it only works with i extend it to Q2 albeit doesn't return anything beyond H2
    =OFFSET($D$2,0,0,1, COUNT($D$2:$Q$2))

    I have attached the spreadsheet for ease of explanation!

    Thanks to any and all for help.
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    01-24-2020
    Location
    Belgium
    MS-Off Ver
    2013
    Posts
    95

    Re: return row content and expand Horizontal Offset

    Hi, there

    1. with this array formula, you'll find the R1C1 style address of the cell corresponding a candidate's name in D2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    it's the RIGHTMOST match in the LAST ROW supposing there are more than one occurrences at the same name, eg AA3, U4 and Z4; it returns Z4.
    Then, do whatever you want with this matching address.

    2. this formua would do the trick but make sure that no blank cells in between names
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by ThxAlot; 02-19-2020 at 08:50 AM.

  3. #3
    Registered User
    Join Date
    01-24-2020
    Location
    Belgium
    MS-Off Ver
    2013
    Posts
    95

    Re: return row content and expand Horizontal Offset

    1. this array formula does the trick if you want only a match in the timetable:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    cheers!
    Last edited by ThxAlot; 02-19-2020 at 08:59 AM.

  4. #4
    Registered User
    Join Date
    03-03-2016
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    28

    Re: return row content and expand Horizontal Offset

    thank you for your response and answering the Horizontal offset.

    1. I may not have been clear here. I am looking for the formula to return the corresponding content from Column A. Essentially if Tiny Tim is entered on row 5 , Column AA it returns A5 content, likewise, if entered on Row 6, column , it returns A6 content

+ 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] Offset VBA to expand horizontally and vertically
    By FredFitzgerald in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-20-2019, 07:55 PM
  2. Replies: 6
    Last Post: 11-13-2018, 04:54 AM
  3. [SOLVED] Horizontal Dynamice Range using OFFSET
    By kelseygueldalewis in forum Excel General
    Replies: 2
    Last Post: 10-25-2017, 02:43 PM
  4. Replies: 3
    Last Post: 06-01-2014, 10:26 AM
  5. OFFSET formula in horizontal way
    By Mariku in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-27-2014, 12:31 PM
  6. Replies: 2
    Last Post: 06-14-2012, 01:44 PM
  7. Vertical & Horizontal Offset / match row and column
    By David Brown in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-30-2010, 12:43 PM

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