+ Reply to Thread
Results 1 to 3 of 3

Limitations when Combining OFFSET and MATCH to return a cell reference

  1. #1
    Registered User
    Join Date

    Limitations when Combining OFFSET and MATCH to return a cell reference

    Hello. I'm attempting to retrieve multiple row numbers for a range of data
    using MATCH and OFFSET. The file works perfectly for the first few items,
    but then it stops and leaves off the remaining references.

    The following formula is placed in cell A9:
    =IFERROR(A8+MATCH(L$5,OFFSET('Collector Historical'!AK1,A8,0,10000,1),0),"")

    Collector Historical is the tab where all my detail resides. G1, is the
    first cell in the column that will be searched.

    L$5 is a date and name concatenation that is used to lookup a supervisor's
    name and the date of the reporting. For example, 39692CF, means I want
    the formula to return the first reference for CF's September 2008 data.

    In my example, CF has 13 collectors reporting to him, the first cell
    reference is 1107. I drag the formula down and expect to see numbers 1029
    through 1041. Instead, I only get numbers 1029 through 1035 (7 items)...

    I changed the supervisor name to obtain a different lookup, picking a
    supervisor with less subordinates to see if it gives me all of the
    subordinate references. No luck...I picked a person with only 7 subordinates
    and it returns the first 4 references (i.e., SM) .

    I'm attaching a sample worksheet....

    Can someone tell me what I'm doing wrong? Thanks in advance....
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac

    I think you've a typo above, your formula in A9 actually reads

    Please Login or Register  to view this content.
    Change that G1 reference to G$1, copy down and everything will be OK


  3. #3
    Registered User
    Join Date
    Y O U T H E B O M B!!!!!

    You have saved my life!! I've been looking at this formula for hours!! Boy do I feel stupid....geeezz....

    Thank you....Thank you....Thank you....

    Let me know if you need your car washed, boy do I owe you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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


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