Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Case Sensitivity in Lookup Functions
Is there a way to perform case sensitive lookup functions? For example...I
am trying to perform a vlookup on a cell with a value of 'dDqf'. Unfortunately my table array contains cells in the lookup column that have values of both 'dDqF' and 'dDqf' and the lookup function doesn't seem to differentiate based on upper/lower case and therefore returns the 'dDqF' value instead of the 'dDqf' value that I need it to. Any ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Case Sensitivity in Lookup Functions
There are three ways to check the value and case. See
http://office.microsoft.com/en-us/ex...561301033.aspx Hope this helps JB "TISI-84601" wrote in message ... Is there a way to perform case sensitive lookup functions? For example...I am trying to perform a vlookup on a cell with a value of 'dDqf'. Unfortunately my table array contains cells in the lookup column that have values of both 'dDqF' and 'dDqf' and the lookup function doesn't seem to differentiate based on upper/lower case and therefore returns the 'dDqF' value instead of the 'dDqf' value that I need it to. Any ideas? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Case Sensitivity in Lookup Functions
On Tue, 22 Jul 2008 11:22:03 -0700, TISI-84601
wrote: Is there a way to perform case sensitive lookup functions? For example...I am trying to perform a vlookup on a cell with a value of 'dDqf'. Unfortunately my table array contains cells in the lookup column that have values of both 'dDqF' and 'dDqf' and the lookup function doesn't seem to differentiate based on upper/lower case and therefore returns the 'dDqF' value instead of the 'dDqf' value that I need it to. Any ideas? As LOOKUP is not case sensitive, here is a way around this problem Assuming that your lookup vector is in A1:A10 your result vector is in B1:B10 your lookup value is in C1 you want your result in D1 and that the lookup value is always present in the lookup vector then you may try the following formula in cell D1: =INDEX(B1:B10,MAX(EXACT(C1,A1:A10)*ROW(A1:A10))) This is an array formula that must be confirmed with CTRL+SHIFT+ENTER rather than just ENTER. Hope this helps / Lars-Åke |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Case Sensitivity in Lookup Functions
One way:
Array entered** : =INDEX(G1:G6,MATCH(TRUE,EXACT(A1,F1:F6),0)) Where A1 = lookup_value F1:G16 = lookup_table ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "TISI-84601" wrote in message ... Is there a way to perform case sensitive lookup functions? For example...I am trying to perform a vlookup on a cell with a value of 'dDqf'. Unfortunately my table array contains cells in the lookup column that have values of both 'dDqF' and 'dDqf' and the lookup function doesn't seem to differentiate based on upper/lower case and therefore returns the 'dDqF' value instead of the 'dDqf' value that I need it to. Any ideas? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Case Sensitivity in Lookup Functions
Thanks Jan, this was most helpful!
"Jan Bolluyt" wrote: There are three ways to check the value and case. See http://office.microsoft.com/en-us/ex...561301033.aspx Hope this helps JB "TISI-84601" wrote in message ... Is there a way to perform case sensitive lookup functions? For example...I am trying to perform a vlookup on a cell with a value of 'dDqf'. Unfortunately my table array contains cells in the lookup column that have values of both 'dDqF' and 'dDqf' and the lookup function doesn't seem to differentiate based on upper/lower case and therefore returns the 'dDqF' value instead of the 'dDqf' value that I need it to. Any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to perform caculations using database functions in this case | Excel Discussion (Misc queries) | |||
How to change case of text without using functions? | Excel Discussion (Misc queries) | |||
VLookup & Case Sensitivity | Excel Worksheet Functions | |||
Pivot Table Heading Fields - case sensitivity issue | Excel Discussion (Misc queries) | |||
Case Sensitivity problem with data validation | Excel Worksheet Functions |