OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Text and Numbers in VLOOKUP

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Francis
General User
General User


Joined: 26 Apr 2006
Posts: 12

PostPosted: Mon Feb 05, 2007 4:54 am    Post subject: Text and Numbers in VLOOKUP Reply with quote

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
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Mon Feb 05, 2007 5:10 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Francis
General User
General User


Joined: 26 Apr 2006
Posts: 12

PostPosted: Mon Feb 05, 2007 5:29 am    Post subject: Reply with quote

Thanks, that has worked perfectly.

Francis
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group