sullisnyc44
asked on
Help making this formula case sensitive
How can I make this formula case sensitive?
=VLOOKUP(A2,AllData,MATCH( B2,Heading s,0),TRUE)
The issue is with the data in A2 - needs to be a case sensitive lookup
many thanks
=VLOOKUP(A2,AllData,MATCH(
The issue is with the data in A2 - needs to be a case sensitive lookup
many thanks
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/
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,I NDEX(AllDa ta,0,1)),0 ),MATCH(B2 ,Headings, 0))
Needs to be coonfirmed with CTRL+SHIFT+ENTER
regards, barry
=INDEX(Alldata,MATCH(TRUE,
Needs to be coonfirmed with CTRL+SHIFT+ENTER
regards, barry
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
If there are matches which aren't case sensitive before the actual case-sensitive match it incorrectly returns "no exact match"
regards, barry
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
ASKER
my solution works
Open in new window