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
4
Medium Priority
?
1,252 Views
Last Modified: 2008-03-06
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
Comment
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
  • Learn & ask questions
  • 2
4 Comments
 

Expert Comment

by:Flake
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

by:
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
Karlstad, Sweden
0
 
LVL 3

Author Comment

by:DanR
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

by:ture
ID: 6435179
Yep! And thanks for the points!

/Ture
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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: …

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question