Solved

Excel VLOOKUP ignoring case

Posted on 2001-08-28
4
1,244 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 100 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

752 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