Share via

How to make Vlookup NOT case sensitive

Anonymous
2012-01-25T17:05:59+00:00

I am using Vlookup for a very large 2 Million city search the problem is, the lists I have created have city names with the proper capitalization, however I am trying to add a bit of room for error.

so in the list I have a city called "North Bay" but through the Vlookup (using the FALSE option ) if "north bay" gets entered in the search criteria then it finds nothing, obviously if its done correctly  and "North Bay" is used I see US, and Canada both with the City "North Bay"

I thought about using the "True" in Vlookup but that opens the door for to many incorrect variables.

Any suggestions?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2012-01-25T17:45:53+00:00

    Hi,

    VLOOKUP isn't case sensitive, North Bay will be matched if the lookup value is nORth BaY or any other combination of upper/lower case.

    FALSE in vlookup makes it find an exact match but NOT case sensitive. TRUE allows for an approximate match. If you use the TRUE swith then column 1 of the lookup vector must be sorted ascending or you could get incorrect results.

    However, I'm confused where you seem to be saying your VLOOKUP is returning 2 values, that can't be done with a standard VLOOKUP.

    5 people found this answer helpful.
    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest