Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to return the value of a cell based on two colums match criter | Excel Discussion (Misc queries) | |||
Combining Average, Offset & Vlookup | Excel Worksheet Functions | |||
Match a cell to something in a column and return a YES | Excel Worksheet Functions | |||
MATCH Multiple Criteria & Return Previous / Penultimate Match | Excel Worksheet Functions | |||
Compare Value in Cell 1 to a List, Return Value if Match otherwise Return Null | Excel Discussion (Misc queries) |