[Webinar] Streamline your web hosting managementRegister Today

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 195

# Help making this formula case sensitive

How can I make this formula case sensitive?

The issue is with the data in A2 - needs to be a case sensitive lookup

many thanks
0
sullisnyc44
• 4
• 4
2 Solutions

Commented:
Try this:
``````=IF(EXACT(A2,VLOOKUP(A2,AllData,MATCH(B2,Headings,0),TRUE))=TRUE,VLOOKUP(A2,AllData,MATCH(B2,Headings,0),FALSE),"No exact match")
``````
0

Author Commented:
thanks for the fast response but I'm getting 'no match' for everything

I found this but am having difficulties applying it to my formula:
http://blog.contextures.com/archives/2009/10/16/case-sensitive-lookup-in-excel/
0

Commented:
It doesn't make much sense for the VLOOKUP to have a 4th argument of TRUE (which implies a "nearest match") when you want a case-sensitive match. I assume that A2 has to match exactly as well as case-sensitive in which case you can use this version:

Needs to be coonfirmed with CTRL+SHIFT+ENTER

regards, barry
0

Commented:
You could also add in another INDEX function to avoid having to "array enter", like this

see attached example

regards, barry

27398485.xls
0

Commented:
Note: X layer's suggestion is along the lines suggested by Microsoft but as pointed out on the Contexture blog....it doesn't work!

If there are matches which aren't case sensitive before the actual case-sensitive match it incorrectly returns "no exact match"

regards, barry
0

Author Commented:
this works for now - though not ideal

3 columns
[F2]    =INDEX(AllData,H2,G2)
[H2]    {=MATCH(1,--EXACT(SFAcctID,A2),0)}

didn't have time to combine it... but how much does it stink that VLOOKUP is not Case sensitive???
I have excel 2010 mind you!!
0

Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for sullisnyc44's comment http:/Q_27398485.html#36973933

for the following reason:

it works
0

Commented:
Hello sullisnyc44,

It works in a similar way to your solution but without needing a new named range and with only a single formula (with an option for a non-array version too)

regards, barry
0

Author Commented:
my solution works
0

## Featured Post

• 4
• 4
Tackle projects and never again get stuck behind a technical roadblock.