Quantcast

vlookup case

classic Classic list List threaded Threaded
5 messages Options
Gary Collins Gary Collins
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

vlookup case

Hello,
Is it possible to make the search performed by vlookup to be case sensitive? 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 its possible)
Thanks
Gary

--
To unsubscribe e-mail to: [hidden email]
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
Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: vlookup case

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: [hidden email]
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

Gary Collins Gary Collins
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: vlookup case

In reply to this post by Gary Collins
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 <[hidden email]> wrote:

 Subject: Re: [libreoffice-users] vlookup case
 To: [hidden email]
 Cc: "Gary Collins" <[hidden email]>
 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: [hidden email]
 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: [hidden email]
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
msetzerii msetzerii
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]>
Send reply to:   Gary Collins <[hidden email]>
To:             <[hidden email]>, Brian Barker
<[hidden email]>
Copies to:       Gary Collins <[hidden email]>
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 <[hidden email]> wrote:
>
>  Subject: Re: [libreoffice-users] vlookup case
>  To: [hidden email]
>  Cc: "Gary Collins" <[hidden email]>
>  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: [hidden email]
>  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: [hidden email]
> 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: [hidden email]
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
Gary Collins Gary Collins
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: vlookup case

In reply to this post by Gary Collins
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 <[hidden email]> wrote:

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