Text in quotes not case sensitive anymore?

Just finished up some testing, and...

It isn't even just text within quotes. It also includes text within cell references.

This does NOT work for me. Anyone else?

.

Basic layout of sheet...

Primary        Column2             Column3             Column4

    a                     1                         a                          f1

    A                     2                         A                          f2

    b                     3                                                     f3

    B                     4                                                     f4

.

Tests run...

f1:

=INDEX([Column2]:[Column2], MATCH([Column3]@row, Primary:Primary, 0))

result: 1 (expected)

.

f2:

=INDEX([Column2]:[Column2], MATCH([Column3]@row, Primary:Primary, 0))

result: 1 (based on upper v lower case, this should have been a 2)

.

f3:

=INDEX([Column2]:[Column2], MATCH("a", Primary:Primary, 0))

result: 1 (expected)

.

f4

=INDEX([Column2]:[Column2], MATCH("A", Primary:Primary, 0))

result: 1 (based on upper v lower case, this should have been a 2)

I put in a product enhancement. Didn't think a support ticket would amount to much as it is probably an intentional change.

It's not even just in MATCH functions either. It acts exactly the same way in an IF statement checking a box.

=IF([Column Name]@row = "a", 1)

provides the same result as 

=IF([Column Name]@row = "A", 1)

.

I accidentally stumbled across this change this morning when testing something out for a different community post.

I was checking a box if the cell had "Yes" in it. I populated a few more rows with junk data including "yes", and the box was checked for that row as well.

That's what prompted the further testing and discovery which led to the larger implications of the INDEX/MATCH not pulling the correct data.

I have a rather complicated sheet that is essentially the main driving factor for an entire project that a lot of people rely on for it's accuracy as a huge time saver. This sheet NEEDS to be able to differentiate between capital vs lower case letters for conversions.

Without that differentiation, the entire project may as well be tossed in the trash which means I wasted MONTHS of time building this calculator, and everyone is going to have to go back to doing it by hand which will increase the time spent on one calculation from a few seconds to a few minutes. 

When I do an easy one by hand, it only takes maybe 4 minutes. Lets say I have 15 of those. There's an hour spent doing these calculations.

Those same 15 in the calculator I built would only take about 2 minutes TOPS.

That's increasing the time spent by what... About 3,000%?!? And that's for the easy ones.

The more complicated ones can take even longer by hand, but they would take the same amount of time using my calculator. That all depends on how fast someone can type (or copy/paste).

.

Sorry... It's just frustrating to see so much work go to waste. Rant end...

Haha. Thanks, Mike.

I am not even sure what the latest update was. I know that the Sheet Summary was released recently (yesterday morning I believe), but I am not sure when the case specific requirements changed. I just happened to notice it for the first time yesterday, but it could have happened a week ago for all I know.

I have heard back from Support. 

Apparently case insensitivity is something that has been around for a while now. I just never noticed it because most of my builds that required case sensitivity used the only case sensitive function. FIND.

So here is a summary of what I found out.

Using an = as a comparison in not case sensitive. CONTAINS is also not case sensitive.

The only way to ensure case sensitivity is to use the FIND function. Other than that... Case doesn't matter.

I also confirmed that this has been the case (ba-dum-bum) since 2016 for the majority of function, Feb of 2018 for the MATCH function, and of course July 2019 (release of) for the CONTAINS function.

Apparently the tests I ran that I relied on case sensitivity in the past that did not use the FIND function, just so happened to work out perfectly so that I got the expected result even though the actual process was wrong.

.

Long story short I was converting letters to numbers using an INDEX/MATCH. Instead of testing individual letters (like I should have), I was using entire strings of letter/number combinations that just so happened to coincidentally work out. Of course it did. This is me we're talking about here. Haha. I take one little tiny shortcut, and I end up paying for it every time.

.

.

So now that I have been "schooled" on my mistake (thanks Kara ) I will be submitting a Product Enhancement Request to ask for more case sensitivity. It was mentioned that (at least with the MATCH function's change in 2018) it was as a fix to a bug, so I won't hold my breath, but hey... It can't hurt to ask. Haha.

.

Side note: I am still going to save this thread as a bookmark. Andree's meme is from one of my favorite scenes in one of my favorite movies.

"shhhshushhubalaflubalubahflaablhlaulblaheehe" (I think that's the general idea)

Always trying to help share knowledge.

And I swear more than just his face is made out of rubber. I've seen some of the crazy shapes he's contorted himself into just for a good laugh. Between his other movies and some of his standup/talk show skits (not to mention Saturday Night Live when it was actually good)... I didn't think some of what he did was even physically possible. Hahaha

I didn't! I guess I know what I'm doing on my lunch break now! Hahaha