Excel VLOOKUP ignoring case

I'm finding that Excel apparently sees the difference between 00011AB and 00011Ab when sorting, but when I then do a VLOOKUP, if the lookup value is 00011Ab, it returns info from the 00011AB row (which comes first).

Is VLOOKUP not case-sensitive?  Can I make it case-sensitive?
Who is Participating?
tureConnect With a Mentor Commented:

VLOOKUP is not case sensitive. Neither is the MATCH function, which could have helped us otherwise.

Let's use another method... I assume that your list is in A1:C100, with the values to search in column A.

First, enter 00011Ab (the value we look for)in cell F1

To find the row of the matching value, you can use this array formula. Type it in cell G1 and press Ctrl+Shift+Enter afterwards.

The formula will return the number of the row where the value in cell F1 is found within the range A1:A100.


To make use this row number, try the INDEX function.
This formula will get the value from the table A1:C100 where the row number is the value in cell G1 and the column is 2. Enter it in cell H1.

=INDEX(A1:C100, G1, 2)

Ture Magnusson
Karlstad, Sweden
Reviewing VLookup in the Help file states that it is NOT case sensitive.  "Uppercase and lowercase are equivalent".  I don't know of a way to override that unless one of the VBA geniuses can write a formula.
DanRAuthor Commented:
I haven't actually tested this out, but it looks good.  And it's ingenious.  The SUM formula is odd to me; care to explain what it's doing?  It looks as if it's scanning the whole table, multiplying the result of the EXACT function with the current row numbers.  Since the EXACT will be 0 for all but one row, the product of EXACT*ROW will be 0 for all other rows, so the SUM will be the number of that row.  Is that how it's working?
Yep! And thanks for the points!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.