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

vlookup with odd characters

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


Joined: 07 Jun 2008
Posts: 4

PostPosted: Sat Jun 07, 2008 8:40 am    Post subject: vlookup with odd characters Reply with quote

I am using vlookup to compare current values in a windows services dump vs older values, so my lookup table looks something like this...

COM Host;Started
COM+ Event System;Started
COM+ System Application;Started

vlookup has no problem finding the value "Started" for "COM Host", but returns #NA for any of the values with embedded "+" characters. If there a way to escape the string in the lookup , ie instead of "=VLOOKUP(A2;servs0.$A$2:$E$155;2;0)", I'd like "=VLOOKUP(actualstring(A2);servs0.$A$2:$E$155;2;0)". And how would that apply in the range itself?
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sat Jun 07, 2008 9:14 am    Post subject: Reply with quote

I don't know a direct solution, but perhaps create a helper column replacing the + with something more amenable to VLOOKUP

=LEFT(A1;FIND("+";A1)-1)&" "&RIGHT(A1;LEN(A1)-FIND("+";A1))

but perhaps using a database might be more effective (not that I know much about databases Embarassed ).
Back to top
View user's profile Send private message
tfwil
Newbie
Newbie


Joined: 07 Jun 2008
Posts: 4

PostPosted: Sat Jun 07, 2008 10:57 am    Post subject: Reply with quote

Thanks for the quick reply. I tried inserting a new column A in both sheets, and then:
IF(ISERR(FIND("+";B2));B2;LEFT(B2;FIND("+";B2)-1)&" "&RIGHT(B2;LEN(B2)-FIND("+";B2)))

But now I see it doesn't like parends either. Seems like the parser for vlookup should not be interpreting the resolved content of a cell. Guess I'll need to write a macro, but it seems like I shouldn't have to.
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sat Jun 07, 2008 2:29 pm    Post subject: Reply with quote

tfwil wrote:
But now I see it doesn't like parends either.
Sorry, what are they?
Back to top
View user's profile Send private message
tfwil
Newbie
Newbie


Joined: 07 Jun 2008
Posts: 4

PostPosted: Sun Jun 08, 2008 8:45 am    Post subject: Reply with quote

Sorry, I mean parens, like left parenthesis and right parenthesis, as in

Intel(R) Alert Service

Ended up with this, after creating a new column, eliminating "+","(" and ")" and replacing with ":" which it seems to like.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2;"(";":");")";":");"+";":")

Thanks for all your help
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