This article describes formula syntax and function usage SEARCH And SEARCH in Microsoft Excel.

Description

Functions SEARCH AND SEARCH find one text string within another and return the starting position of the first text string (counting from the first character of the second text string). For example, to find the position of the letter "n" in the word "printer", you could use the following function:

SEARCH("n","printer")

This function returns 4 , since "n" is the fourth character in the word "printer".

You can also find words in other words. For example, the function

SEARCH("base","database")

returns 5 , since the word "base" begins with the fifth character of the word "database". Functions can be used SEARCH And SEARCH to determine the position of a character or text string in another text string and then return the text using functions PSTR And PSTRB or replace it with functions REPLACE And REPLACE. These functions are shown in this article.

Important:

These features may not be available in all languages.

The SEARCH function counts two bytes per character only if the default language is a BDC-enabled language. Otherwise, the SEARCH function works the same as the SEARCH function and counts one byte per character.

Languages ​​that support BDCS include Japanese, Simplified Chinese, Traditional Chinese, and Korean.

Syntax

SEARCH(search_text, viewed_text, [start_position])

SEARCHB(search_text, viewed_text, [start_position])

The arguments to the SEARCH and SEARCHB functions are described below.

Search_text Required. The text you want to find.

Viewed_text Required. Text in which to find the value of the argument search_text .

Start_position Optional. Character number in argument viewed_text , where you should start your search.

Comment

Functions SEARCH And SEARCH are not case sensitive. If you need to be case sensitive, use the functions FIND And NIGHTIB.

In argument search_text You can use wildcards: question mark ( ? ) and an asterisk ( * ). A question mark matches any character, an asterisk matches any sequence of characters. If you want to find a question mark or asterisk, type a tilde ( ~ ).

If the argument value search_text not found, #VALUE! error value returned.

If the argument start_position is omitted, then it is set to 1.

If Start_position no more than 0 or greater than argument length viewed_text , #VALUE! error value returned.

Argument start_position can be used to skip a certain number of characters. Let us assume that the function SEARCH should be used to work with the text string "MDS0093.Men's Clothing". To find the first occurrence of "M" in the descriptive portion of a text string, set the argument to start_position value 8 so that the search is not performed in that part of the text that is serial number(in this case - "MDS0093"). Function SEARCH starts the search from the eighth character, finds the character specified in the argument search_text , in the next position, and returns the number 9. The function SEARCH always returns the character number, counting from the beginning of the text being viewed , including characters that are skipped if the argument value is start_position more than 1.

Examples

Copy the sample data from the following table and paste it into cell A1 of a new Excel worksheet. To display the results of formulas, select them and press F2, then press Enter. If necessary, change the width of the columns to see all the data.

Data
Revenue: margin
"Boss" here.
Formula	Description	Result
SEARCH("and";A2;6)	The position of the first "and" in the row of cell A2, starting at the sixth character.
SEARCH(A4;A3)	The starting position of the "margin" row (the searched row is in cell A4) in the "Revenue: Margin" row (the searched cell is A3).
REPLACE(A3,SEARCH(A4,A3),6,"volume")	Replaces the word "margin" with the word "volume" by locating the word "margin" in cell A3 and replacing that character and the following five characters with the text string "volume."	Revenue: volume
PSTR(A3;SEARCH(" ";A3)+1,4)	Returns the first four characters that follow the first space in the Revenue: Margin row (cell A3).
SEARCH("""";A5)	Position first double quotes(") in cell A5.
PSTR(A5;SEARCH("""";A5)+1;SEARCH("""";A5;SEARCH("""";A5)+1)-SEARCH("""";A5)-1)	Returns only the text enclosed in double quotes from cell A5.

Working with VB project (12)
Conditional Formatting (5)
Lists and ranges (5)
Macros (VBA procedures) (63)
Miscellaneous (39)
Excel bugs and glitches (3)

Find any word from the list in a cell

Let's assume you receive from a supplier/customer/client a completed table with a list of products:

And this table must be compared with the article numbers/product codes in your existing product catalog:

As you can see, our catalog contains only articles without names. In addition to the article numbers, the customer also has the name of the product, i.e. a lot of unnecessary stuff. And you need to understand which products are present in your catalog and which are not:


There are no standard formulas in Excel for such a search and comparison. Of course, you can try applying VLOOKUP with wildcards first to one table and then to another. But if such an operation needs to be done over and over again, then writing several formulas for each table, frankly speaking, is not comme il faut.
That's why I decided today to demonstrate a formula that, without any extras. manipulations will help to make such a comparison. To figure it out yourself, I recommend downloading the file:
Download file:

(49.5 KiB, 13,249 downloads)

On the “Order” sheet in this file there is a table received from the customer, and on the “Catalog” sheet there are our articles.
The formula itself, using the example file, will look like this:

BROWSE(2,1/SEARCH(Directory!$A$2:$A$11, A2);Directory!$A$2:$A$11)
=LOOKUP(2,1/SEARCH(Directory!$A$2:$A$11,A2),Directory!$A$2:$A$11)
this formula will return the name of the article if the text contains at least one article from the catalog and #N/A (#N/A) if the article is not found in the catalogue.
Before we refine this formula with all sorts of additions (like removing unnecessary #N/A), let's figure out how it works.
The LOOKUP function searches for the specified value (2) in the specified range (array - second argument). The range is usually an array of cells, but the VIEW function has the first feature we need - it tries to convert directly into an array any expression written as the second argument. In other words, it evaluates the expression in this argument, which we use by substituting the expression as the second argument: 1/SEARCH(Directory!$A$2:$A$11;A2) . The SEARCH part (Catalog!$A$2:$A$11;A2) searches in turn for each value from the Catalog list in cell A2 (name from the Customer table). If a value is found, the position number of the first character of the found value is returned. If the value is not found, the error value #VALUE! (#VALUE!) is returned. Now the second feature: the function requires the data to be arranged in the array in ascending order. If the data is located differently, the function will scan the array until it finds a value that is greater than the searched one, but as close as possible to it (although if the data allows it, for a more accurate search it is still better to sort the list in ascending order). Therefore, first we divide 1 by the expression SEARCH(Directory!$A$2:$A$11,A2) to get an array of the form: (0.0181818181818182:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE !:#VALUE!:#VALUE!:#VALUE!:#VALUE !}
Well, as the desired value we give the function a number 2 - a obviously larger number than can generally be found in the array (since one divided by any number will be less than two). And as a result, we will get the position in the array where the last match from the directory occurs. After which the VIEW function will remember this position and return the value from the array Directory!$A$2:$A$11 (third argument) written in this array for this position.
You can view the stages of calculating the function yourself for each cell; here I will simply present the stages in a slightly expanded form for understanding:

1. =BROWSE(2,1/SEARCH(Directory!$A$2:$A$11, A2);Directory!$A$2:$A$11)
2. =VIEW(2;
   1/(55:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE;!}
   Catalog!$A$2:$A$11)
3. =VIEW(2;(0.0181818181818182:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE!:#VALUE;Каталог!$A$2:$A$11)!}
4. =VIEW(2;
   1;
   ("FM2-3320":"CV455689":"Q5949X":"CE321A":"CE322A":"CE323A":"00064073":"CX292708":"CX292709":"CX292710"))
5. ="FM2-3320"

Now let’s refine the function a little and make a couple more implementations
Implementation 1:
Instead of article numbers and #N/A, we will display “Yes” for found positions, and “Not found in the catalog” for missing ones:
=IF(END(VIEW(2,1/SEARCH(Catalog!$A$2:$A$11,A2)));"Not found in the catalog","Yes")
=IF(ISNA(LOOKUP(2,1/SEARCH(Directory!$A$2:$A$11,A2))),,"Not found in the directory","Yes")
The operation of the function is simple - we have sorted out LOOKUP, so all that remains is UND and IF.
UNM (ISNA) returns TRUE if the expression inside it returns an error value #N/A (#N/A) And FALSE if the expression inside does not return this error value.
IF (IF) returns what is specified by the second argument if the expression in the first is equal to TRUE and what is specified by the third argument, if the expression of the first argument FALSE.

Implementation 2:
Instead of #N/A we will display “Not found in the catalog”, but if the articles are found, we will display the names of these articles:
=IFERROR(VIEW(2,1/SEARCH(Catalog!$A$2:$A$11,A2),Catalog!$A$2:$A$11);"Not in catalog")
=IFERROR(LOOKUP(2,1/SEARCH(Catalog!$A$2:$A$11,A2),Catalog!$A$2:$A$11),"Not in catalog")
I talked in detail about the IFERROR function in this article: How to show 0 instead of an error in a cell with a formula.
In short, if the expression specified by the first argument of the function returns the value of any error, then the function will return what is written by the second argument (in our case, the text “Not found in the directory”). If the expression does not return an error, then the IFERROR function will write the value that was obtained by the expression in the first argument (in our case, this will be the name of the article).

Implementation 3
It is necessary not only to determine which article it corresponds to, but also to display the price for the item for this article (the prices themselves should be located in column B of the Catalog sheet):
=IFERROR(VIEW(2,1/SEARCH(Directory!$A$2:$A$11,A2),Directory!$B$2:$B$11);"")
=IFERROR(LOOKUP(2,1/SEARCH(Directory!$A$2:$A$11,A2),Directory!$B$2:$B$11),"")

A couple of important notes:

• the data on the sheet with article numbers should not contain empty cells. Otherwise, with a high degree of probability, the formula will return the value of an empty cell, and not the one that matches the search conditions
• The formula searches in such a way that any match is found. For example, a number is written as an article 1 , and in the name line there may also be, in addition to the whole 1, 123 , 651123 , FG1412NM and so on. For all these items, article number 1 can be selected, because it is contained in every title. Typically this can happen if article 1 is located at the end of the list

Therefore it is advisable Sort the list before using formula ascending (from smallest to largest, from A to Z).

In the example attached at the beginning of the article you will find all the options analyzed.

If you need to display all the names, you can use the CONTAIN_ONE_OF function from my MulTEx add-on.

Did the article help? Share the link with your friends! Video lessons

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

It is quite difficult to find the information you need on a worksheet with big amount data. However, the Find and Replace dialog box makes finding information much easier. In addition, it has some useful features, which many users are not aware of.

Run the command Home Editing Find and highlight Find(or click Ctrl+F) to open the dialog box Find and Replace. If you need to replace data, then select the command Home Editing Find and highlight Replace(or click Ctrl+H). The exact command you execute determines which of the two tabs the dialog box opens on.

If in the dialog box that opens Find and press the button to replace Options, then additional information search options will be displayed (Fig. 21.1).

In many cases, it is better to specify approximate rather than exact text in the search, for example wall key holders. For example, to find data on the client Ivan Smirnov, you can, of course, enter the exact text in the search field. However, it is likely that you will not find anything, since you may have entered the client's name differently, such as Ivan Smirnov or I. Smirnov, or even made a mistake in the last name. When searching for such a name, it is best to use wildcards.

Enter iv*smir* in field Find and then click the button Find all. Using wildcards not only reduces the number of words you type, but also ensures that you will find customer data if it exists on the worksheet. Of course, the search results may contain entries that do not meet the purpose of your search, but this is better than nothing.

When searching using the dialog box Find and Replace two wildcards can be used:

• ? - matches any character;
• * - matches any number of characters.

Additionally, these wildcard characters can also be used when searching for numeric values. For example, if you specify in the search bar 3* , the result will display all cells that contain a value starting with 3, and if you enter 1?9, you will get all three-digit entries that start with 1 and end with 9.

To search for a question mark or asterisk, precede it with a tilde (~).
For example, the following search string finds the text *NONE*: -*N0NE~*
To find the tilde character, put two tildes in the search bar.

If you think your search isn't working properly, check the following three settings (which sometimes change on their own).

• Checkbox Match case- set it so that the case of the searched text matches the case given text. For example, if you enter the word Ivan in the search and check the specified box, the word Ivan will not appear in the search results.
• Checkbox Entire cell- set it to find a cell that contains exactly the text that is specified in the search bar. For example, if you type Excel in the search bar and check the box, you will not find a cell that contains the phrase Microsoft Excel.
• Drop-down list Search area- the list contains three items: values, formulas and notes. For example, if you enter the number 900 in the search bar and in the drop-down list Search area If you select the value item, you will not see the cell containing the value 900 in the search results if it was obtained by using the formula.

Remember that the search is performed within the selected range of cells. If you need to search the entire sheet, select only one cell before you start searching.

Also, note that using the window Find and Replace cannot be found formatted numeric values. For example, if you enter into the search bar $5* , a value that has currency formatting applied and looks like $54.00 will not be found.

Working with dates can be challenging because Excel supports so many date formats. If you are looking for a date that has default formatting applied, Excel finds dates even if they are formatted different ways. For example, if your system uses the m/d/y date format, the search string 10/*/2010 finds all dates in October 2010, regardless of how they are formatted.

Use a blank field Replaced by to quickly remove some information from a worksheet. For example, enter - * in the field Find and leave the field Replace on empty. Then click the button Replace all to have Excel find and remove all the stars in the worksheet.

Hello friends. How often do you have to look for a match in an Excel spreadsheet for a value? For example, you need to find a person’s address in a directory, or the price of a product in a price list. If such tasks occur, this post is just for you!

I perform similar procedures every day and without the functions described below I would really have a hard time. Take note and apply them in your work!

Search in Excel table, VLOOKUP and GLOOKUP functions

The role of these functions in the life of an ordinary user is difficult to overestimate. Now you can easily find a suitable entry in the data table and return the corresponding value.

The syntax of the VLOOKUP function is: =VLOOKUP( lookup_value; lookup_table; output_column_number; [mapping_type]). Let's look at the arguments:

• Search value– the value that we will look for. This is a required argument;
• Search table– the array of cells in which the search will take place. The column with the searched values ​​must be the first in this array. This is also a required argument;
• Column number to display– the serial number of the column (starting from the first in the array), from which the function will display data if the required values ​​match. Required argument;
• Matching type– select “1” (or “TRUE”) for a weak match, “0” (“FALSE”) for a complete match. The argument is optional; if omitted, a search will be performed non-strict match.

Finding an exact match using VLOOKUP

Let's look at an example of how the VLOOKUP function works when the matching type is FALSE, searching for an exact match. The B5:E10 array indicates the fixed assets of a certain company, their book value, inventory number and location. Cell B2 contains the name for which you need to find the inventory number in the table and place it in cell C2.

VLOOKUP function in Excel

Let's write the formula: =VLOOKUP(B2,B5:E10,3,FALSE) .

Here the first argument indicates that in the table you need to look for the value from cell B2, i.e. the word "Fax". The second argument says that the table to search is in the range B5:E10, and you need to look for the word “Fax” in the first column, i.e. in the array B5:B10. The third argument tells the program that the result of the calculation is contained in the third column of the array, i.e. D5:D10. The fourth argument is FALSE, i.e. a complete match is required.

And so, the function will receive the string “Fax” from cell B2 and will look for it in the array B5:B10 from top to bottom. Once a match is found (line 8), the function will return the corresponding value from column D, i.e. D8 contents. This is exactly what we needed, the problem is solved.

If the searched value is not found, the function will return .

Finding an imprecise match using VLOOKUP

Thanks to this option in VLOOKUP, we can avoid complex formulas to find the desired result.

Array B5:C12 shows interest rates on loans depending on the loan amount. In cell B2 we indicate the loan amount and want to receive the rate for such a transaction in C2. The task is difficult in that the amount can be anything and is unlikely to coincide with those specified in the array; searching for an exact match is not suitable:

Then we write the formula for a non-strict search: =VLOOKUP(B2,B5:C12,2,TRUE). Now, from all the data presented in the B column, the program will look for the nearest smaller one. That is, for the amount of 8,000, the value of 5,000 will be selected and the corresponding percentage will be displayed.

Non-strict VLOOKUP search in Excel

For the function to work correctly, you need to sort the first column of the table in ascending order. Otherwise, it may give an erroneous result.

The GLOOKUP function has the same syntax as VLOOKUP, but looks for the result in rows rather than columns. That is, it scans tables not from top to bottom, but from left to right and displays the specified row number, not column.

Searching for data using the VIEW function

The LOOKUP function works similarly to VLOOKUP, but has a different syntax. I use it when the data table contains several dozen columns and to use VLOOKUP you need to additionally calculate the number of the output column. In such cases, the VIEW function makes the task easier. And so, the syntax: =VIEW( lookup_value; array_to_search; Array_to_display) :

• Search value– data or a link to the data to be searched;
• Array to search– one row or column in which we are looking for a similar value. We must sort this array in ascending order;
• Array to display– a range containing data for displaying results. Naturally, it must be the same size as the search array.

When writing this way, you are giving a non-relative reference to the results array. And you point directly at it, i.e. there is no need to first calculate the number of the output column. We use the VIEW function in the first example for the VLOOKUP function (fixed assets, inventory numbers): =VIEW(B2,B5:B10,D5:D10). The problem has been successfully solved!

VIEW function in Microsoft Excel

Search by relative coordinates. MATCH and INDEX functions

Another way to search for data is to combine the MATCH and INDEX functions.

The first of them is used to search for a value in an array and obtain its serial number: SEARCH( lookup_value; viewed_array; [Matching type] ). Function arguments:

• Search value– required argument
• Array to be viewed– one row or column in which we are looking for a match. Required argument
• Matching type– specify “0” to search for an exact match, “1” for the nearest smaller match, “-1” for the nearest larger match. Since the function searches from the beginning of the list to the end, when searching for the nearest smaller one, sort the search column in descending order. And when looking for more, sort it in ascending order.

The position of the required value has been found, now you can display it on the screen using the function INDEX( Array; line_number; [Column_number]) :

• Array– the argument specifies from which cell array the value should be selected
• Line number– indicate the serial number of the line (starting from the first cell of the array) that you want to display. Here you can write the value manually, or use the result of another function calculation. For example, SEARCH.
• Column number– optional argument, specified if the array consists of several columns. If an argument is omitted, the formula uses the first column of the table.

Now let's combine these functions to get the result:

MATCH and INDEX functions in Excel

These are the methods for searching and displaying data that exist in Excel. Further, you can use them in calculations, use them in presentations, perform operations with them, specify them as arguments to other functions, etc.

Do you feel how your knowledge and skills are growing and strengthening? Then don't stop, keep reading! In the next post we will consider: it will be difficult and interesting!

The question often arises « How to find in Excel»? In Excel you can find any information: text, part of text, number, phone number, email. address, last name, formula, note, cell format, etc.
Find a cell at the intersection of a row and a column in Excel – « How to find a cell in Excel at the intersection of a row and a column» ( INDEX function in Excel).
Find and move to another location in Excel(for example, on a form)several data at once - see the article “Find several data at once in Excel” (VLOOKUP function in Excel).
Or find the cell with the link in Excel formula, to replace the link, see « Change links to other sheets in Excel formulas».
Find in Excel cells with note - article "Insert a note in Excel" .
For quick search there is a keyboard shortcut - Ctrl+F. Click Ctrl key and, holding it, press the F key. A search window will appear.
You can also open the search window like this: on the “Home” tab, click the “Find and Select” button.
On the “Find” tab, in the “find” cell, write the word you are looking for (part of a word is possible) and click “find next”. The first such word will be found. Then click “find next” and the search will move to the second such word.

And if you need to show all such words at once, then click the “find all” button and a list will appear at the bottom of the search window indicating the cell address. To go to the desired word in the table, click the desired word in the list of the search window.

If the search did not find anything, and you know that this data is definitely there, then try removing the indentation from the table cells. How to remove indentation in cells, see the article " Excel text. Format".
Finding a number in Excel requires minor adjustment of search conditions - applicable advanced search in Excel.
Advice.
If you work with a table for a long time and you often need to move on to searching from one word to another.Then it is more convenient not to close the search window every time, but to move it to that part of the table where it will not interfere. You can move it below the screen, leaving only the input cell for the search word (“find”) and then press Enter.
This search dialog always remains on the screen, even if you navigate to another page. Using this window you can search on any page, you just need to activate it on open page. To do this, click the cursor on the “find” line.
For a more advanced search, click the Options button and select the desired search option.
For example, choose “Value”. Then it will search for numbers, phone number, etc.
If you need to find all words with the same weight, but in cases (milk, milk, milk, etc.), then we will write a formula with wildcards. See "Wildcards in Excel" about this.
Excel function "Find and Highlight" will help not only find data, but also replace it. See the article "How to copy a formula in Excel without changing the links".
How to remove extra spaces that interfere with processing data in a table, read the article "How to remove extra spaces in Excel".
In Excel, you can find any information not only with the Search function or formulas, but also with the conditional formatting function. Read the article “Conditional Formatting in Excel” about this.
You can also read about the "Find and Select" function in the article "Filter in Excel".