Finding the right cells by a certain criterion or several is one of the most common tasks when working in Excel.
First of all, it is worth noting that everyone puts a different meaning into the term “Find”:
- For some, find means to mark the cells you want in some way – conditional formatting is usually the best way to do this;
- You can use a filter to filter out specific cells – this is also a kind of search;
- Someone wants to extract data from cells and move it somewhere else to work with it further.
- It is also possible to set certain values in the columns against the desired cells. Various formulas based on the IF function will help here.
- If we are interested in full or partial duplicates of cells of another range, the UPP function and similar functions can help us.
Now let’s understand the concept of a cell.
An Excel cell is a compound object that has three child objects – formula, value and formatting. Accordingly, you can search for cells in three globally different ways:
- By values
- By formula
- By format
This article will answer how to search and find the right cells in each of these ways and provide links to examples.
Finding cells by value
Most often you need to find certain cells by their value. In this case, the value of cells can be considered independent of other cells, or it can be considered as part of a whole range, where relationships and dependencies between cell values can be seen.
The easiest and most popular thing to do in such cell sets is to find cells that duplicate each other in one way or another.
Find unique and repeating cells
Regardless of the type of data in a cell, cells can be duplicates of each other (repeating cells), or they can contain a unique value that occurs only once.
That said, finding duplicates is one of the most popular operations in Excel.
There are two different algorithms for finding duplicates, and two separate articles are devoted to them:
- Find duplicates within the same cell range
- Compare cell ranges with each other and identify duplicates
- Line-by-line
- At each cell position (if the range is two-dimensional, containing more than one column and row)
- Find values of one column in another (compare lists without considering the position).
All these cases are characterized by such phenomenon as implicit duplicates. I highly recommend you to read how to find implicit duplicates in Excel.
Searching by cell text values
The content type of the value affects how and in what way we will search for cells.
If the cell values are textual, then the search criteria will also be text-specific:
- Find cells containing a specific text (substring or multi-string search)
- Find cells containing a specific word;
- Find cells containing multiple words or any of the words in the list;
- Find cells that begin with a specific text or one of several text variants
- Similar, but find cells ending with a specific text
Finding cells based on a single text value is fairly easily accomplished by the in-house find and replace procedure. If you have two criteria at the same time, the advanced filter procedure will help.
But if you have several search criteria at the same time, you can solve the problem only with formulas or programmed procedures. You can also use ready-made solutions, one of which is my customization for Excel !SEMTools.
In the articles above, I give both the formulas and their corresponding procedures.
Finding numeric values
When cells contain certain numbers, the situation is more interesting than with cells that contain text. The fact is that numeric cells can be treated as both text and numbers. Also, cells can be hybrid – they can contain both numbers and text.
In one way or another, all text search criteria apply to numeric values, but you can also search cells by numeric criteria:
- Equal / not equal
- Greater than / less than
- Greater than or equal to / less than or equal to
- Above / below average
- Between
- First N highest / lowest values (default is first 10 highest)
Number/year/date in text
When cells can contain both text and numbers, this is probably the most difficult situation to handle because you cannot apply numeric criteria to cells if they contain text. For example, if you have a year of birth in text cells and you want to filter only cells that have a year greater than 2005, it is recommended that you first extract the numbers from the cell into a separate column.
The second option is to list all years from 2006 and above in an array formula with the SEARCH function:
=COUNT(SEARCH({"2006":"2007":"2008":"2009":"2010":"2011":"2012":"2013":"2014":"2015":"2016":"2017":"2018":"2019":"2020":"2021":"2022":"2023"};A1))>0The function will return TRUE or FALSE depending on the presence of a year in the cell.
Read more about finding numbers in cells here:
- Find cells with numbers;
- Find cells with specific numbers.
Find cells by their formatting
Microsoft Excel offers a wide range of options for formatting cells. This allows you to revitalize and transform boring, dry summaries and make them more presentable. So, what if you need to find cells in an array of cells that have been selected by a particular formatting method and filter only those cells?
Here’s a visual demonstration of how to find bold cells:
This post is also available in RU.