Link to home
Start Free TrialLog in
Avatar of sullisnyc44
sullisnyc44Flag for United States of America

asked on

Help making this formula case sensitive

How can I make this formula case sensitive?

=VLOOKUP(A2,AllData,MATCH(B2,Headings,0),TRUE)

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

many thanks
Avatar of X_layer
X_layer
Flag of Slovenia image

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")

Open in new window

Avatar of sullisnyc44

ASKER

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/
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:

=INDEX(Alldata,MATCH(TRUE,EXACT(A2,INDEX(AllData,0,1)),0),MATCH(B2,Headings,0))

Needs to be coonfirmed with CTRL+SHIFT+ENTER

regards, barry
ASKER CERTIFIED SOLUTION
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Hello sullisnyc44,

I think I answered your question with a valid solution

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
my solution works