Wow! Thanks i'll look into that and maybe try to adapt it. The strings are of variable length from
1 to 3 characters. What i came up with (bypassing VLOOKUP altogether) was
=CONCATENATE (MID ($J$2, FIND(LEFT (A2,1), $J $3),1),IFERROR(MID($J$2, FIND (MID (A2,2,1), $J
$3),1," "),IFERROR (MID ($J $2,FIND (MID (A2,3,1), $J $3,1),1," "))
(I think); which takes each character from the string in A2, gets the position of that character in
the string held in J2, then extracts the corresponding character from the string held in J3 and
concatenates it to the result. In the case of the string being less than 3 characters, MID () will
return an error which will result in " " concatenated instead. Null string would have been better
but in my case space works just as well.
Its very cumbersome (and more so because in my actual file the strings J2 and J3 are in fact
located on a different sheet); but it does what i need and thats all i demand of it!
Best
Gary
--------------------------------------------
On Fri, 10/3/17, Michael D. Setzer II <mikes@kuentos.guam.net> wrote:
Subject: Re: [libreoffice-users] vlookup case
To: "Gary Collins" <gcatlast@yahoo.co.uk>, users@global.libreoffice.org, "Brian Barker"
<b.m.barker@btinternet.com>
Date: Friday, 10 March, 2017, 11:25
I was doing some testing
with single characters, but then saw it was three
characters. Originally, just used the code of
the character, changed code to
ascii, but
with 3 multiple each value to get a number that matched.
In both put the row number in next column.
Put the match field in C15
both the formulas work, both formulas give the
row.
abc
1
97098099
1
aBc
2
97066099
2
aBC
3
97066067
3
Abc
4
65098099
4
AbC
5
65098067
5
Abc
6
65098099
6
Ab
7
65098000
7
aCb
8
97067098
8
AcB
9
65099066
9
aBC
10
97066067
10
AAC
11
65065067
11
ss
12
115115000
12
aBC
97066067
3
“=VLOOKUP($C$15,C1:D12,2,0)
3
“{=INDEX(B1:B12,MATCH(1,EXACT($C$15,A1:A12),0))}
Formula in
C
“=CODE(MID(A15,1,1))*1000000+CODE(MID(A15,2,1))*1000+CODE(MID(A15,3,1))
On 10 Mar
2017 at 9:46, Gary Collins wrote:
Date sent: Fri, 10 Mar 2017
09:46:22 +0000 (UTC)
From:
Gary Collins <gcatlast@yahoo.co.uk>
Send reply to: Gary Collins <gcatlast@yahoo.co.uk>
To: <users@global.libreoffice.org>,
Brian Barker
<b.m.barker@btinternet.com>
Copies to: Gary Collins <gcatlast@yahoo.co.uk>
Subject: Re:
[libreoffice-users] vlookup case
Thanks for very helpful reply. It does
seem to be a big drawback with these functions. I'll
certainly try it but ive found a workaround that im using at
the mo: ive defined two strings in adjacent cells and using
string functions to find the position of a character in one
string then extract the corresponding character from the
other. Much less elegant and it was quite tiresome to
implement but ive at least got it to work, in the limited
cases that im using it so far. If i extend it to
transliterate strings of arbitrary length (for now the max
length ive needed is 3 chars) then i guess i'll probably
have to get into macros.
Thanks
G.
--------------------------------------------
On Thu, 9/3/17, Brian Barker <b.m.barker@btinternet.com>
wrote:
Subject:
Re: [libreoffice-users] vlookup case
To: users@global.libreoffice.org
Cc: "Gary Collins" <gcatlast@yahoo.co.uk>
Date: Thursday, 9 March, 2017, 23:17
At 11:50 09/03/2017
+0000, Gary
Collins wrote:
>Is it possible to make the search
performed by vlookup
to be case
sensitive?
Apparently not!
I need to be able to distinguish between eg 'd'
and 'D'
but at the
>moment i cant work out how to do it
(if it's possible)
Suppose your array has the values to be
searched in column A
and the
values to be returned in column B. Then
try:
=INDEX(B1:Bn;MATCH(1;EXACT("text";A1:An);0))
Note that this is an array formula, so
when you have entered
it you
must complete the process by pressing
Ctrl+Shift+Enter. If
you do
this successfully, the entire formula
will appear in the
Input line
surrounded by braces, but you cannot
simply type these
braces yourself.
This relies on
EXACT() being the one function that *is*
case-sensitive.
I trust this helps.
Brian Barker
--
To unsubscribe
e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be
publicly archived and
cannot be
deleted
--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be
publicly archived and cannot be deleted
--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
Context
- Re: [libreoffice-users] vlookup case · Gary Collins
Privacy Policy |
Impressum (Legal Info) |
Copyright information: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License.
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (
MPLv2).
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our
trademark policy.