CODEWITHSUNDEEP
excelfindmatchformulavlookup
Nivi
Nivi

Reputation: 89

Case Sensitive VLookup in Excel

I've tried multiple workarounds for trying to "Vlookup" aka 'match' 'LegacyID' to 'Account ID' , both of which are case sensitive. I've followed multiple tutorials online about using various formulas like 'FIND', 'EXACT', 'MATCH', etc.

SAMPLE

I am trying to match 'Legacy ID' to 'Account ID' (both in orange) and return 'Enriched Street Address'. Formula's used:

In Blue: =LOOKUP(1,1/EXACT(AG:AG,C2),BD:BD)
In Green: { =INDEX(BD:BD,MATCH(1,--EXACT(AG:AG,C2),0))}

The formulas are technically working, it's just not abiding to case sensitivity for the two duplicate values (Seen in RED). Notice how their respective 'Legacy ID's' in column 'C' are different. They should be pulling two different 'Addresses' from 'Enriched Company Street'.

As you can see, both formulas are doing the same thing. Are the formulas not working correctly because the Legacy and Account ID columns are both Text AND Numbers?

Any suggestions/help would be greatly appreciated!

Thanks, M

Upvotes: 1

Views: 236

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40204

Using a version of the green formula works for me:

{=INDEX(E:E,MATCH(TRUE,EXACT(A3,D:D),0))}

Exact Match

Can you verify that those should actually have distinct addresses? Just because the IDs are different doesn't mean they must have different addresses since they could be associated with the same company.

Upvotes: 1

Related Questions