Forum Home
Press F1
Thread ID: 52227	2004-12-13 22:22:00	Excel Vlookup problem	robo (205)	Press F1

Post ID	Timestamp	Content	User
302768	2004-12-13 22:22:00	I have a worksheet with some large lookup tables. It has worked for many years but recently malfunctioned. The malfunction is this: Nothing looked up below the 6999th entry is found or returned. The range is correct, have changed it to a named range from a manual range. Weird thing is some other formulae in the workshop will work beyond 7000 (but a bunch don't). Works perfectly for any items within the 6999. For an item that crosses the boundary, picks up first four lines and not the rest. Details: The lookup tables are on separate pages inside the same workbook. There are two lookup tables. I deleted some unneeded columns in one table to reduce memory requirement, made no obvious difference. Similar issue with the same thing in the past where it stopped handling beyond about 21,000 (might have been 20,000) rows. We cut up the data into separate worksheets. Dropping to 14,000 was livable, but dropping to 7,000 is going to require about ten versions of the worksheet and is not an option. Things that it can't be (IMHO): It can't be viral, been tried on four different PCs. Os's tried include Win 98, 2k, and XP. It can't be the version of Excel, it's a problem in 97 and 2003. It can't be the version of the saved file, saved it in 97 and 2003 formats. I've looked at using a combination of the Match and Index functions but they don't seem to work for me. It's got me beat. Would welcome suggestions. robo.	robo (205)
302769	2004-12-13 23:49:00	Hey robo, Using Excel 2000 I can't reproduce your problem. This is on a 2.8GH P4 with 512mb RAM. Vlookup works for me down to the last row 65536, ie cell D1 contains =vlookup(C1,A:B,2) , with entries down to A65536 & B65536 and a reference to any cell in column A in cell C1, returns a value in D1 Maybe it's a memory limitation :-(, pirate copy ]:) ,just kidding.... Strange	Russell D (18)
302770	2004-12-14 00:24:00	Hi Robo, Vlookup requires that the data be in ascending order plus Ive had odd results at times when there are gaps between the data. in these instances I use an Index/Match combination which I find is more robust. Example You have data in D1:F8 and you want to match the value in cell B1 and return the result from th 3rd column (F1:F8) A Vlookup formula will look like this... =VLOOKUP(B1,D1:F8,3,FALSE) An Index/Match lookup will like this... =INDEX(F1:F8,MATCH(B1,D1: D8)) Syntax: Index(Result Column,(Match(Lookfor cell, Col that contains Lookfor value)) See if this makes any difference. You can go to www.mrexcel.com and post your question and you may get a better response as it dedicated to questions about Excel. In there you can download the HTML Maker which is a free add-in that allows you to take a snapshot of your sheet to assist in explaining the issues. If you want I can email you and give you my email address so you can send the offending book and I'll take a look at it tonight. Place a post here if you want me to do that. cheers Parry.	parry (27)
302771	2004-12-14 00:26:00	It's very strange. I have copied the data across to another worksheet a page at a time in case there is something funny in the original. I have split the lookup arbitrarily in half and used two formulae and the first one works and the second does not. Data comes from an ODBC query, but on my machine I don't even refresh it. Hitting another issue now, where the named range for another list in the worksheet keeps changing. I enter a range like: =Stock_Items!a$2:H$6000 and it changes to =Stock_Items!IO$2:H$6000 It's very strange. (it's not a dodgy copy, anywhere) robo.	robo (205)
302772	2004-12-14 00:46:00	Where you say it doesnt work your meaning it returns #N/A because it cant find it when its actually in the list? Could you expand a bit more on what doesnt work means. I wonder if the data being pulled in has some erroneous characters such as a space or carriage return. Vlookup with a False argument is looking for an exact match so if theres a space or something in the data then this may be the problem. Try my MegaClean function in this thread (www.mrexcel.com) to kill non alphanumeric characters.	parry (27)
302773	2004-12-14 00:50:00	It's the INDEX function which requires the data to be in ascending order, not the VLOOKUP. Have you tried adding FALSE to your vlookup function? This finds an exact match....	Marlboro (4607)
302774	2004-12-14 01:41:00	> =Stock_Items!a$2:H$6000 and it changes to > =Stock_Items!IO$2:H$6000 This is extremely dodgy, unless perhaps you have range names called "a" or "IO" - because if you try to enter a range into a formula with the highest row column number first ie (cs2:a6000), Excel will swap the formula to show the lowest column number (a2:cs6000). Time for a reinstall methinks.	Russell D (18)
302775	2004-12-14 02:20:00	> It's the INDEX function which requires the data to be > in ascending order, not the VLOOKUP. > > Have you tried adding FALSE to your vlookup function? > This finds an exact match.... Doh, I think your right. :8}	parry (27)
302776	2004-12-14 02:36:00	I added a ,0) in the end of the vlookup and it came right. Bizarre. Worked fine then just stopped. Works up to line 7000 and not beyond. Makes no sense. Just couldn't get that index/match thing to function - it had the same problem I think. robo.	robo (205)
302777	2004-12-14 03:47:00	Inset a few new rows starting at row 7000 and enter some dummy data in and see if Vlookup locates it. If so, theres something odd about the data after that point like some errant characters as I mentioned above. You could also try a Len formula and if the formula returns more characters than what you can see on your screen you know somethings wrong. This seems more likely than a problem with the workbook itself but you never know. If you have some extra characters then use the function I created by adding the code (Alt+F11 to open VBE, Insert|Module, paste code). Use this formula in another column then paste special values the result over existing data.	parry (27)
1 2