[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
Francis
General User

Joined: 26 Apr 2006
Posts: 12

 Posted: Mon Feb 05, 2007 4:54 am    Post subject: Text and Numbers in VLOOKUP I've set up a VLOOKUP formula to search for entries containing text and numbers (eg. ABC12345) within an IF statement. The data to be searched occasionally contains a suffix which needed to be removed so I created a column of LEFT formula to reduce the data down to the required number of digits. Using this column as the search area, or the original column, the VLOOKUP only finds the first entry containing the initial letters of the data (eg. ABC) and ignores the numbers, therefore not producing the correct answer where there is more than one entry starting with the same letters. I've tried formatting the cells as numbers, text and all, but nothing works. The full formula is =IF(VLOOKUP(K5;P5:P300;1)=0;I2;H2) K5 = the ABC12345 reference number to search for P5:P300 = the range produced by the use of the LEFT formula I2 = "not found" H2 = "found" I get the feeling I'm doing something really silly, but I just can't spot it. The only unusual element about this spreadsheet is that it has been imported from an outside source as a csv. Any help would be most appreciated. Francis
noranthon
Super User

Joined: 07 Jul 2005
Posts: 3318

 Posted: Mon Feb 05, 2007 5:10 am    Post subject: Add the fourth parameter to VLOOKUP. Presumably, you want an exact match, so use 0 (=FALSE). A new Help section was to be included, IIRC, in 2.1. I was not favourably impressed with the new section but it was an improvement on the old one. My best effort at rationalising the apparent rationale for the fourth parameter is that TRUE(1) means you are willing to accept the first near match. You can only use TRUE if the lookup column is sorted in ascending order. You could look at the issue I filed, not knowing rewriting had already occurred._________________ search forum by month
Francis
General User

Joined: 26 Apr 2006
Posts: 12

 Posted: Mon Feb 05, 2007 5:29 am    Post subject: Thanks, that has worked perfectly. Francis
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum