Limitations when Combining OFFSET and MATCH to return a cell refer
 

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. AK1, 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, 39692John Doe, means I want
the formula to return the first reference for John Doe's September 2008 data.

In my example, John Doe has 11 collectors reporting to him, the first cell
reference is 1107. I drag the formula down and expect to see numbers 1107
through 1119. Instead, I only get numbers 1107 through 1113 (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 5 subordinates
and it returns the first 3 references.

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

--
Danger Mouse

Limitations when Combining OFFSET and MATCH to return a cell refer
 

Two suggestions... first, change the final argument of the MATCH function to
0 to require an exact match (probably not the problem here, but might be as
your list on the Collector Historical sheet grows). Second, I think you've
got the asolute v relative row references mixed up. Based on my
understanding of your data's organization, I'd try:
=IFERROR(A$8+MATCH(L5,OFFSET('Collector Historical'!AK$1,A$8,0,10000,1),0),"")

If that doesn't get it, please also explain what you have in cell A8.


"Danger Mouse" wrote:

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. AK1, 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, 39692John Doe, means I want
the formula to return the first reference for John Doe's September 2008 data.

In my example, John Doe has 11 collectors reporting to him, the first cell
reference is 1107. I drag the formula down and expect to see numbers 1107
through 1119. Instead, I only get numbers 1107 through 1113 (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 5 subordinates
and it returns the first 3 references.

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

--
Danger Mouse