Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP Problem (limitation)?
I am having a problem with VLOOKUP. The reporting period on the report being produced (correctly) was updated from 26 weeks to 52 weeks. The cells were copied and pasted so the VLOOKUP code that is working in the starting weeks should continue to work for the ending weeks. However, the VLOOKUP function seems to stop finding matching values after the 41st week (282 VLOOKUPs). Is there a limitation for the VLOOKUP function? I have tried the report with different data and it ends up repeating the error (no matches after 282 VLOOKUPs). I am at wit's end here. I have attached the file. Please take a look at it and any help is GREATLY appreciated. Thanks. SnotRockit +-------------------------------------------------------------------+ |Filename: VLOOKUP_PROBLEM.zip | |Download: http://www.excelforum.com/attachment.php?postid=4001 | +-------------------------------------------------------------------+ -- SnotRockit ------------------------------------------------------------------------ SnotRockit's Profile: http://www.excelforum.com/member.php...o&userid=28618 View this thread: http://www.excelforum.com/showthread...hreadid=482797 |
#2
|
|||
|
|||
VLOOKUP Problem (limitation)?
Many folks here won't open up attachments; you might try posting a
representative vlookup function. Without seeing it, this sounds as though the table_range in your vlookup is row-limited: ex =vlookup(key, $a$1:$c$282,3,false). If so, you might expand the table range to include more rows ($a$1:$c$584) or, if the table is not in the same columns as any other data, just eliminate the row restriction entirely ($a:$c). --Bruce "SnotRockit" wrote: I am having a problem with VLOOKUP. The reporting period on the report being produced (correctly) was updated from 26 weeks to 52 weeks. The cells were copied and pasted so the VLOOKUP code that is working in the starting weeks should continue to work for the ending weeks. However, the VLOOKUP function seems to stop finding matching values after the 41st week (282 VLOOKUPs). Is there a limitation for the VLOOKUP function? I have tried the report with different data and it ends up repeating the error (no matches after 282 VLOOKUPs). I am at wit's end here. I have attached the file. Please take a look at it and any help is GREATLY appreciated. Thanks. SnotRockit +-------------------------------------------------------------------+ |Filename: VLOOKUP_PROBLEM.zip | |Download: http://www.excelforum.com/attachment.php?postid=4001 | +-------------------------------------------------------------------+ -- SnotRockit ------------------------------------------------------------------------ SnotRockit's Profile: http://www.excelforum.com/member.php...o&userid=28618 View this thread: http://www.excelforum.com/showthread...hreadid=482797 |
#3
|
|||
|
|||
VLOOKUP Problem (limitation)?
I checked your file. The ajc range referred to in your VLOOKUP formulas stops at row 199. You need to redefine the ajc range to extend below row 199 in order to match the missing items. •InsertNameDefine •Select the name "ajc" •Tab once to select Refers To •Extend the range as far down as you will need. Does that help? Regards, Ron -- Ron Coderre ------------------------------------------------------------------------ Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419 View this thread: http://www.excelforum.com/showthread...hreadid=482797 |
#4
|
|||
|
|||
VLOOKUP Problem (limitation)?
That did it. :) :) :) I was unaware that I had even created a range named "ajc". I also used "ajc" as a password to protect the worksheet. I therefore thought the "ajc" in the formula just referred to the password so as to temporarilty "unprotect" the sheet while it did it's VLOOKUP. Although now that I think of it...since the "ajc" could be seen in the function, it wouldn't really be a secure password now would it? Thank you SO much for your assistance. You da man Ron! SnotRockit -- SnotRockit ------------------------------------------------------------------------ SnotRockit's Profile: http://www.excelforum.com/member.php...o&userid=28618 View this thread: http://www.excelforum.com/showthread...hreadid=482797 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Vlookup, What is correct formula for problem below? | Excel Worksheet Functions | |||
VLOOKUP Problem | Excel Worksheet Functions | |||
vlookup problem | Excel Worksheet Functions | |||
VLOOKUP, OFFSET, MATCH PROBLEM, HELP? | Excel Worksheet Functions |