Great question re case sensitive formulas

[Sprackers]

Hi all

Here is something that is going to be tricky.

Im using the below formula to compare two columns of account ID's and highlight matches.

=IF(ISNA(MATCH(A111,F:F,0)),"",INDEX(F:F,MATCH(A111,F:F,0)))

this formula works very well ....except

The Account ID's are in this type:

[TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl65, width: 146, align: left"]
a3P580000005nNH


[/TD]
[/TR]
</tbody>[/TABLE]

Now here is where the formula falls down: It is highlighting a3P580000005nNH as the same as a3P580000005nNh


The difference is that last letter is in lower case on one Account and upper on another.

But they are two completely different Accounts.

Is there an addition to the above formula to match case sensitive also?

Regards

Sprackers

 

[FormR]

Hi, you can try like this:

=IF(ISNUMBER(MATCH(TRUE,INDEX(EXACT(A111,$F$1:$F$1000),0),0)),A111,"")

For performance reasons avoid full using full column references.

 

[Stiuart_W]

I really think you should make great strides to make the account number NOT case sensitive.

There is a whole world of pain just waiting to happen forcing EVERY system that interacts with those numbers, including humans to differentiate between two virtually identical numbers and call them different.

 

[Sprackers]

totally agree and I will put that forward. in the meantime thanks for the formula update I will implement it.

Thanks again