# Limitations when Combining OFFSET and MATCH to return a cell reference

1. ## 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....

2. Hi,

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

HTH

3. 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

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