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 <[email protected]>
Send reply to: Gary Collins <[email protected]>
To: <[email protected]>, Brian Barker
<[email protected]>
Copies to: Gary Collins <[email protected]>
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 <[email protected]> wrote:
>
> Subject: Re: [libreoffice-users] vlookup case
> To: [email protected]
> Cc: "Gary Collins" <[email protected]>
> 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: [email protected]
> 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: [email protected]
> 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: [email protected]
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
