How to Search in Excel?
There are multiple ways to search in Excel. The first option is to find a particular piece of data in a cell or group of cells among a massive spreadsheet.
The second option includes using search functions like VLOOKUP or HLOOKUP that let you search one sheet for data and output the results in a second cell location or a different worksheet.
Most of the time, searching in an Excel spreadsheet is easy. If you can't scan through the rows and columns, you can use Ctrl + F to open a search dialog box. If you're working with a really big spreadsheet, it can save a lot of time to use one of these four lookup functions.
How to Search Using the Search Sheet Bar
Follow the following steps to search specific data in excel by using the search sheet bar.
Step 1: Click into the taskbar with the faint words "Search Sheet" at the top right corner of the spreadsheet and enter the words or numbers which you want to search in the sheet.
Step 2: Press Enter or Return if you're using a Mac to find the data. And then use the arrows beside the search term to jump to the next or previous data.
Step 3: Also, you can quickly access this search bar by using a "command + F" keyboard shortcut on a Mac and a "Control + F" shortcut on a PC.
How to Search Using Find Feature
With Excel open to your spreadsheet with data, you can find anything on the spreadsheet using a straight word search or using special wildcard characters. Follow these steps to use the find feature in Excel:
Step1: Go to the Home tab. And select Find & Select button in the Editing group, then select the Find feature.
Step 2: You'll see a simple Find and Replace window. Also, you can see all of the advanced options by selecting Options.
You can use the following options to fine-tune your search:
If you select Find Next button, you'll see each incident in the spreadsheet where the text is found highlighted. Select the button again to move on to the next result. If you want to see them all at once, select the Find All button.
Step 3: This displays all of the results, along with the sheet and cell number where they're found, in a list at the bottom of the window. Just select any one of them to see that cell in the sheet.
Excel Search Wildcard Characters
When you're typing search characters into the Find What field, you must understand how wildcard characters work. These let you customize more advanced search so you can search in Excel and find exactly what you're looking for in the worksheet.
Excel SEARCH Function
The SEARCH function in Excel is very similar to the FIND function. It also returns the location of a substring in a text string. Unlike FIND, the SEARCH function is case-insensitive, and it allows using the wildcard characters.
Below is the basic syntax of the SEARCH function.
And here are some examples to evaluate the search function.
=SEARCH("market", "supermarket") returns 6 because the substring "market" begins at the 6th character of the word "supermarket".
=SEARCH("e", "Excel") returns 1 because "e" is the first character in the word "Excel", ignoring the case.
Like the FIND function, Excel's SEARCH function also returns the #VALUE! Error if:
How to Search Using Functions
In Excel, Many useful functions let you search columns, rows, or tables to find information and return related information.
The following are a few examples of the most popular functions you can use to search in Excel.
1. VLookup Function
This function allows you to specify a column and a value. It will return a value from the corresponding row of a different column. Here's the syntax of the function:
2. HLOOKUP Function
Where VLOOKUP finds corresponding values in another column, HLOOKUP finds corresponding values in a different row. Because it's usually easiest to scan through column headings until you find the right one and use a filter to find what you're looking for, HLOOKUP is best used when you have really big spreadsheets, or you're working with values that are organized by time.
Here's the syntax of the function:
3. INDEX and MATCH Function
INDEX and MATCH are two different functions, but they can make searching a large spreadsheet a lot faster when they're used together. Both functions have drawbacks, but by combining them, we'll build on the strengths of both.
Below is the syntax of both functions:
How to Search for Excel Formulas
In Excel, the search for formulas is a little different because formulas display numbers in a cell. It can be difficult to know which cells contain numbers and which cells contain formulas.
To help you find which cells contain formulas, Excel gives you two choices:
And to display (or hide) formulas in a spreadsheet, you have two methods, such as:
Method 1: Press Ctrl + (an accent grave character, which appears on the same key as the ~ sign, often to the left of the number 1 key near the top of a keyboard).
Method 2: Click the Formulas tab, and then click the Show Formulas button in the Formula Auditing group.
This image shows what a spreadsheet looks like when formulas appear inside cells.
To highlight all cells that contain formulas, follow these steps:
Step 1: Go to the Home tab.
Step 2: Click on the Find & Select icon in the Editing group. And a pull-down menu appears.
Step 3: Then click on the Formulas. Excel highlights all the cells that contain formulas.
Difference between SEARCH and FIND Functions
In Excel, the FIND and SEARCH functions are very similar in terms of syntax and use. However, there are still some differences that distinguish them to be selective for solving query in Excel.