We all know that VLOOKUP (and its cousins MATCH, HLOOKUP and LOOKUP) are great for finding information you want. But they are helpless when you want to do a case-sensitive lookup.
So how do we write case sensitive VLOOKUP formulas?
Simple. We can use EXACT formula.
What exactly is the EXACT formula?
EXACT formula checks if 2 cells have exactly the same value. And it is very SenSITive.
For example, =EXACT("this","THIS") will be false , where as =”this”=”THIS” will be true.
Using EXACT formula to do case sensitive lookups
Let’s say the value you are looking up is in cell F4, the lookup range is B5:C11 (column B has lookup value and column C has value you want).
You can use EXACT formula along with INDEX + MATCH or SUMPRODUCT to do case sensitive lookup. Let’s look at each of these variations:
Using EXACT & INDEX + MATCH formulas to do case sensitive lookups:
Formula: {=INDEX($C$5:$C$11,MATCH(TRUE,EXACT($F$4,$B$5:$B$11),0))}
How it works?
Let’s go from inside out.
EXACT(F4, B5:B11) portion: This will return an array of TRUE & FALSE values. Something like this:
{FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}
MATCH(TRUE, EXACT(...), 0) portion: Now we look for TRUE in all the values EXACT has returned. This will be 3 (since 3rd value in the array is true).
INDEX(C5:C11, MATCH(...)) portion: This will simply return the 3rd value in the column C, ie an exact match.
{INDEX(...)}: Because this is an array formula, you must press CTRL+Shift+Enter after typing it. The {} indicates this.
Related: Learn about INDEX+MATCH combination.
Using EXACT + SUMPRODUCT formula:
If the lookup result is a number (or date) and there is only matching value, you can use SUMPRODUCT to do case sensitive lookups.
Related: Introduction Excel SUMPRODUCT formula.
Formula:=SUMPRODUCT(EXACT($F$4,$B$5:$B$11) * ($C$5:$C$11))
How it works?
The EXACT(F4, B5:B11) portion returns a bunch of TRUE & FALSE values.
When you multiply these TRUE & FALSE values with column C (which contains numbers), the end result will be the value you are looking for.
This is possible because in Excel, TRUE is 1 and FALSE is 0. So when you multiply a list of logical values (true / false) with a list of numbers, everything that corresponds to false becomes 0.
So we get,
{0;0;30;0;0;0;0}
SUMPRODUCT simply adds up these numbers and returns 30 as result.
Note: This formula won’t work if you have text values in column C or more than one TRUE in EXACT result (ie multiple values match the lookup criteria).
For advanced users: SUMPRODUCT – Advanced scenarios
Download case sensitive lookup – example workbook
Please click here to download case sensitive lookup example workbook. Examine the formulas to learn more about this technique.
More ways to lookup:
- 2 Way lookups – lookup in top row & left column and find matching value.
- Wild lookups – lookup a value that starts with Som & ends with ne.
- Range lookup – find a value inside the lower & upper boundary
- Last lookup – find the last value in a list of multiple matches.
- Multi-condition lookups – lookup based on multiple conditions
- Take our VLOOKUP Quiz – how well do you know VLOOKUP?
Get The VLOOKUP Book: If you are always looking for help about VLOOKUP, look no further. Get my book, it’s going to make you awesome in VLOOKUP, INDEX+MATCH, multi-condition lookups, 2 way lookups and more. Click here to order your copy.
How do you write case sensitive lookups?
Let me be honest. I haven’t had a single case sensitive lookup scenario in last year. But email from a reader prompted me to research this problem.
What about you? Do you often deal with case-sensitive data? How do you write case sensitive lookups? Please share your tips & formulas in comments section.
39 Responses to “How to remove all cells containing John (or anything else) [Quick tip]”
There's a quicker way for this. Use the Autofilter, filter for John. Select all Rows, then press [ALT]+[;], which will only select those that are viewable. Now delete the rows with right mouse click (click on row number).
If you need it to reoccur programmatically, then assuming the data is in table format you can use something like this:
Sub delete_john()
Dim tbl As ListObject, fld As Range, c As Range, nm As String, i As Long, rws As Long
Set tbl = ActiveSheet.ListObjects(1)
Set fld = tbl.ListColumns(1).DataBodyRange
rws = fld.Rows.Count
nm = "John"
For i = 1 To rws
Set c = fld.Item(i)
If c.Value = nm Then
c.EntireRow.Delete
i = i - 1
End If
Next i
End Sub
Nice code.
I've got one suggestion: It's pretty probably that you don't only have to selete poor John, but perhaps to delete poor Joanne for a change (could be couple! ;)). So I suggest you'll give the happy user the possiblity to change the poor girl's/man's name, so why not use a cell in a sheet in which the user could change the name?
Anyways, I still will use the Autofilter solution beceause it's more flexible (other rows perhaps have the children's names of John and Joanna...)
Sure, so we'll just set the string variable to a range.
Replace nm = "John" with
nm = Worksheet("Yoursheetnamehere").Range("Yourcellholdingnamevaluehere") and whoever you want to delete would be in the cell. Actually, you know what I'd do... I'd set a dynamic range to have a dropdown list of all names in column A. That dropdown selection would be the value to delete. Then I'd assign the macro to a button. So then deleting names would be a matter of dropdown selection, button click. Much faster than anything else I think.
Nice tip. Thank you for sharing.
Don't forget about us Mac users - no "find all" on Excel 2011 for mac. Autofilter worked great!
I didn't know that it works on Mac too. I only use Windows.
Thans for telling me it also works on Mac! 🙂
Chandoo: Your tip is damn easy.
I would just do a replace:
Replace "John" with ""
This would not delete the rows, though.
There is a quite quicker trick for that.
Use autofilters and filter for John. Then select all rows. Enter [CTRL]+[;] which makes sure that you'll only select rows that you can see and exclude those that are hidden by the filter.
Delete the rows by clicking on a row numer with the right mouse button. Choose delete.
This can also be done programmatically as:
Sub DeleteJhon()
For Each cl In ActiveSheet.UsedRange
If cl.Value = "John" Then
cl.EntireRow.Delete
End If
Next cl
End Sub
This works but it must be run multiple times because it is not accounting for the deleted rows as it makes its way through the range.
Two quick notes;
1. Instead of scrolling down in the Results field (too much work!) once you select a cell in the results field, jus hit Ctrl+a to select all of them.
2. Several comments about Autofilter...while this would work quickly if all your data is in one column, this tip is far superior if the value you're looking for is in multiple columns.
True - I didn't realize that yet: You can delete "John" across multiple columns. Thanks for pointing that out 🙂
However, the advantage of the Autofilter is that you can filter the data with more than just one column, so if you have multiple cumulative conditions, then the Autofilter is way to go. That is why I usually have to use this solution, because I usually have to choose two criterias in two different columns.
We can do it in another way.
Activate Auto filter and select the name "John" Or use custom then type name , select contains (if you want partial match) or equal, then delete entire row by simply press Ctrl and minus. If you want partial match using "Ctrl + F" method, use "John*" in find field.
WOW!IT was so easy!
Xl lent way to remove something
It is Nice tip BUTTTTTT
when I removed John from the List, using the given method, even Johnson which was there in the List also gone..
A bit of caution is needed.
M. A. Waseem
That wouldn't happen with Autofilter.
On a second thought, you also could use the option "match full cell content" (or something similar, it's my translation from German)
Please try below in VBA
Sub delrowacell ()
Dim str As Variant
str = ActiveCell.Value
'On Error Resume Next
Range("A:A").Find(What:=str, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).EntireRow.Delete
End sub
You can ofcource change Range("A:A") with any range
Great tip, although I cannot think of any example case where this would be needed? I can understand to remove rows with certain value in a certain column, but in any column, why?
Dear Chandoo
Great tip, but how to use it on mac excel 2011.
Is there a way of inserting a blank line after a certain condition.
Regards
Manoj
Thanks! It works great.
Thanks you sir, you saved my brain & Time... Thanks Alot
Thanks you sir, you saved my brain & Time… Thanks Alot
When I select control, nothing happens. Any ideas? If I right click to delete it deletes the entire spreadsheet.
THANK YOU! Of all the tips/tricks I found, this one was by far the easiest, faster, and most understandable one for what I needed.
awesome! That's so smart 😀
Thank you! ^_^
Wow !! it worked perfectly for me. Thank you, you just saved me HOURS of work.
Thanks for sharing - 7500 line spreadsheet...reduced down to 2400 with this trick for getting rid en-masse all the lines containing things I don't care about.
Thank you!
Awesome tip. Thanks!
PS. I really like how your blog is set up. Which plugin did you use to set up the 3 choices below comments? These 3:
Notify me of when new comments are posted via e-mail
Notify me of follow-up comments by email.
Notify me of new posts by email.
Plz sent me exccel Formullah's
i m student
m B.A, B.Ed
WHAT"S THIS ALL ABOUT????????
@John
Can you elaborate ?
I have a workbook with many different sheets. I'm looking to delete all cells in all worksheets that have a specific words (e.g. Harris). I want to be able to do this time and time again as names get crossed off. Is there a way to easily do this?
Need to bifurcate as Parent & Child data from raw data which in in single column. Here is the sample data.
Raw Data Parent Child
1. AAA 1. AAA a1
a1 1. AAA a2
a2 1. AAA a3
a3 1. AAA a4
a4 1. AAA a5
a5 1. AAA a6
a6 2. BBB b1
2. BBB 2. BBB b2
b1 2. BBB b3
b2 2. BBB b4
b3 2. BBB b5
b4 3. CCC c1
b5 3. CCC c2
3. CCC 3. CCC c3
c1 3. CCC c4
c2 3. CCC c5
c3 4. DDD d1
c4 4. DDD d2
c5 4. DDD d3
4. DDD 4. DDD d4
d1 4. DDD d5
d2 5. EEE e1
d3 5. EEE e2
d4 5. EEE e3
d5 5. EEE e4
5. EEE 5. EEE e5
e1
e2
e3
e4
e5