TEXT = text

[Thomas Hardy]

I have some data in an Excel table which uses case sensitive codes. Does
anyone know of a way to perform case sensitive VLookups.

Example.

A1 contains "TEXT", A2 contains "One".
B1 contains "text", B2 contains "Two"

If I enter "text" in cell A4 and the following VLookup in B4:

=VLOOKUP(A4;A1:B2;2;FALSE)

I get the answer "One", but I would like to see "Two".

I have found a work around by converting a number of characters to their
ASCII codes, but this is very messy.

I know that the FIND function is case sensitive whereas the SEARCH function
is not, is there a way to force a case sensitive VLookup?

Thanks for your thoughts.

Thomas

 

[T. Valko]

Try this array formula** :

=INDEX(B1:B2,MATCH(TRUE,EXACT(A1:A2,A4),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

In this case EXACT means just that!

 

[Thomas Hardy]

Biff,

That's excellent, thank you very much!

Thomas

 

[T. Valko]

You're welcome. Thanks for the feedback!