Re: [libreoffice-users] vlookup case

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