Solved

Excel VLOOKUP ignoring case

Posted on 2001-08-28
4
1,230 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
  • 2
4 Comments
 

Expert Comment

by:Flake
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Yep! And thanks for the points!

/Ture
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now