Case Sensitivity in Lookup Functions

[TISI-84601]

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?

 

[Jan Bolluyt]

There are three ways to check the value and case. See
http://office.microsoft.com/en-us/excel/HP030561301033.aspx

Hope this helps

JB

 

[Lars-Åke Aspelin]

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

 

[T. Valko]

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)

 

[TISI-84601]

Thanks Jan, this was most helpful!

There are three ways to check the value and case. See
http://office.microsoft.com/en-us/excel/HP030561301033.aspx

Hope this helps

JB