Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Excel VLOOKUP ignoring case

Posted on 2001-08-28
Medium Priority
1,252 Views
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?
0
Question by:DanR
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

Expert Comment

ID: 6432700
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.
0

LVL 22

Accepted Solution

ture earned 400 total points
ID: 6432729
DanR,

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.

=SUM(EXACT(A1:A100,F1)*ROW(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
0

LVL 3

Author Comment

ID: 6433621
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?
0

LVL 22

Expert Comment

ID: 6435179
Yep! And thanks for the points!

/Ture
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
###### Suggested Courses
Course of the Month6 days, 20 hours left to enroll