[Webinar] Streamline your web hosting managementRegister Today

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

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
0
sullisnyc44
Asked:
sullisnyc44
  • 4
  • 4
2 Solutions
 
X_layerCommented:
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

0
 
sullisnyc44Author 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
 
barry houdiniCommented:
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
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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

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

see attached example

regards, barry

27398485.xls
0
 
barry houdiniCommented:
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
 
sullisnyc44Author Commented:
this works for now - though not ideal

3 columns
[F2]    =INDEX(AllData,H2,G2)
[G2]    =MATCH(B2,Headings,0)
[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
 
sullisnyc44Author 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
 
barry houdiniCommented:
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
0
 
sullisnyc44Author Commented:
my solution works
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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