Good afternoon friends!

The topic of this article will be another opportunity to work with text, this is knowledge of how it works. I think there is no need to explain how the usefulness of this function can be expressed, it is already clear simply from the name and this means that, this function will search for an exact match of the given text in the specified text string, and return the starting position of the found string.

But I can immediately say that in its pure form the FIND function looks very unsightly and, in principle, is practically not used. The most common feature is used only in combination, in conjunction with functions such as LEFT, RIGHT And . Such combinations will allow you to effectively use all the capabilities of the FIND function and perform more complex tasks compared to simple use.

Well, now let's look at the syntax that has:

=FIND(text we are looking for; text we are viewing; [starting position]), Where

Please note that a feature that has is hypersensitivity to the case of letters, that is, you need to indicate uppercase and lowercase letters correctly.

For example, consider 2 examples for comparison:

=FIND("WATER","WATER water"), will return the value 1, but here is the formula

=FIND("water","WATER water"), will return position 6.

As can be seen from the example, this is a very significant difference to ignore.

If the text you want to find is not found, the formula will return . So, in fact, let's look at variability using examples, as it is used in simple and complex examples:

Simple examples:

Let's consider the use of only one FIND function, in several variations and designs. Let’s take, for example, the text value “Artyom Nagaev” and apply the function:

=FIND("A",A2), in this formula we are looking for the position of the first letter “A” in cell A2, and we get the result “8”;

=FIND(“a”,A2), in this formula we are looking for the position of the first letter “a” in cell A2, and we get the result “2”;

=FIND("a",A2,3), in this formula we are looking for the position of the first letter “a” in cell A2, but we start the search from the third character and get the result “4”.
Combined examples:

The first example will use the combination using the PSTR function. For example, we have a product name with the item number “LED No. 456217-485” and we need to separate the wheat from the chaff, that is, the name of the part from the number and for this we need the formula:

=PSTR(A4,1,FIND(“ №”,A4,1) -1), in the formula we find the position “No.” and everything before it is pulled out by the corresponding formula, and we get the result “LED”. The “-1” argument will take into account the fact that the position occupied by the text we are looking for is also included in the counting range and cannot be returned.
Second example I use combinations with functions LEFT, RIGHT. For example, in cell A5 we entered the first and last name “Artyom Nagaev”, in this case using the formulas:

=LEFTCHAR(A5,FIND(CHAR(32),A5) -1), we can extract the surname,

=RIGHT(A5,LENGTH(A5) -FIND(CHAR(32),A5)), let's extract the name.
In the case where there are extra spaces, two or more, between the two values ​​of the first and last name, then for the above formulas to function it is necessary to use SPACE function.

At this point, I think that you were able to appreciate one more text function. We learned, or at least figured out how and where it is used. We studied its usefulness and application options. More functions are presented for review in.

If you have any questions, write comments, like if you liked it.

See you in new articles!

“We always have money on a whim; we skimp only on useful and necessary expenses.
"
O. Balzac

Main purpose office program Excel – performing calculations. This program's document (Book) can contain many sheets with long tables filled with numbers, text or formulas. Automated quick search allows you to find the necessary cells in them.

Simple search

To search for a value in Excel spreadsheet, you need to open the drop-down list of the “Find and Replace” tool on the “Home” tab and click the “Find” item. The same effect can be achieved using the keyboard shortcut Ctrl + F.

In the simplest case, in the “Find and Replace” window that appears, you need to enter the desired value and click “Find All”.

As you can see, search results have appeared at the bottom of the dialog box. The found values ​​are underlined in red in the table. If instead of “Find all” you click “Find next”, then the first cell with this value will be searched first, and when you click again, the second one will be searched.

Text search is performed in the same way. In this case, the searched text is typed in the search bar.

If data or text is not searched in the entire Excel table, then the search area must first be selected.

Advanced Search

Suppose you want to find all values ​​in the range from 3000 to 3999. In this case, you would type 3??? in the search bar. Wildcard "?" replaces any other.

Analyzing the results of the search, it can be noted that, along with the correct 9 results, the program also produced unexpected ones, highlighted in red. They are associated with the presence of the number 3 in a cell or formula.

You can be satisfied with most of the results obtained, ignoring the incorrect ones. But the search function in Excel 2010 can work much more accurately. This is done using the Options tool in the dialog box.

By clicking "Options", the user has the ability to perform advanced searches. First of all, let’s pay attention to the “Search Area” item, which by default is set to “Formulas”.

This means that the search was carried out, including in those cells where there is not a value, but a formula. The presence of the number 3 in them gave three incorrect results. If you select "Values" as the search scope, you will only search for data and the incorrect results associated with formula cells will disappear.

In order to get rid of the only remaining incorrect result on the first line, in the advanced search window you need to select the “Entire cell” item. After this, the search result becomes 100% accurate.

This result could be achieved by immediately selecting the “Entire Cell” item (even leaving the “Formula” value in the “Search Area”).

Now let's turn to the “Search” item.

If instead of the default “On Sheet” you select “In Workbook”, then there is no need to be on the sheet of cells you are looking for. The screenshot shows that the user initiated the search while on empty sheet 2.

The next item in the advanced search window is “View”, which has two meanings. The default is “by rows”, which means that the cells are scanned row by row. Selecting a different value – “by columns” – will only change the search direction and the sequence of results.

When searching in Microsoft documents Excel, you can use another wildcard – “*”. If the considered "?" meant any character, then “*” replaces not one, but any number of characters. Below is a screenshot of a search for Louisiana.

Sometimes it is necessary to take into account the case of characters when searching. If the word louisiana is capitalized, the search results will not change. But if you select “Match case” in the advanced search window, the search will be unsuccessful. The program will consider the words Louisiana and louisiana different, and, naturally, will not find the first of them.

Types of search

Search for matches

Sometimes it is necessary to detect duplicate values ​​in a table. To search for matches, you first need to select a search range. Then, on the same “Home” tab in the “Styles” group, open the “ Conditional Formatting" Next, sequentially select the items “Rules for highlighting cells” and “Repeating values”.

The result is shown in the screenshot below.

If necessary, the user can change the color of the visual display of matched cells.

Filtration

Another type of search is filtering. Let's assume that the user wants to find numeric values ​​in the range from 3000 to 4000 in column B.

As you can see, only rows that satisfy the entered condition are displayed. All the rest were temporarily hidden. To return to the initial state, repeat step 2.

Various search options have been reviewed at Excel example 2010. How to search in Excel for other versions? There is a difference in the transition to filtering in version 2003. In the “Data” menu, you should sequentially select the commands “Filter”, “Auto Filter”, “Condition” and “Custom Auto Filter”.

Video: Search in an Excel table

Also articles about working with tables in Excel:

• Formatting tables in Excel
• Creating tables in excel
• Creation pivot table in excel
• How to select an entire table in excel?

Among thousands of rows and dozens of columns of data, it is almost impossible to manually find something in an Excel table. The only option is to use some kind of search function, and next we will look at how to search in an Excel table.

To search for data in an Excel table, you must use the “Find and Select” menu item on the “Home” tab, in which you need to select the “Find” option or use the “Ctrl + F” key combination.

As an example, let’s try to find the required number among the data in our table, since it is when searching for numbers that it is necessary to take into account some of the subtleties of the search. We will search in the table Excel number"10".

After selecting the required menu item, enter the desired value in the search box that appears. We have two options for searching for values ​​in an Excel table: find all matches at once by clicking the “Find All” button or immediately view each found cell by clicking the “Find Next” button each time. When using the Find Next button, you should also take into account the current location of the active cell, since the search will begin from this position.

Let's try to find all the values ​​at once, and everything found will be listed in the window under the search settings. If we leave all the default settings, the search result will not be exactly what we expected.

For correct search data in the Excel table, click the “Options” button and configure the search area. Now the desired value is searched even in the formulas used in the cells for calculations. We need to specify the search only in values ​​and, if desired, we can also specify the format of the searched data.

When searching for words in an Excel table, you should also take into account all these subtleties and, for example, you can even take into account the case of letters.

And finally, let’s look at how to search for data in Excel only in the required area of ​​the sheet. As can be seen from our example, the desired value “10” occurs in all data columns at once. If you need to find this value, say, only in the first column, you need to select this column or any range of values ​​in which you need to search, and then start searching.

Our first column only has two values ​​equal to "10", so when we use the Find All option, only two search results should appear in the list.

In documents Microsoft Excel, which consist of a large number of fields, you often need to find specific data, row name, etc. It is very inconvenient when you have to look through a huge number of lines to find the right word or expression. The built-in Microsoft Excel search will help you save time and nerves. Let's figure out how it works and how to use it.

Search function in Excel

Search function in Microsoft program Excel offers the ability to find the text or number values ​​you need through the Find and Replace window. In addition, the application offers advanced data search capabilities.

Method 1: Simple Search

A simple data search in Excel allows you to find all cells that contain the set of characters entered into the search window (letters, numbers, words, etc.) without taking into account case.

1. While in the “Home” tab, click on the “Find and Select” button, which is located on the ribbon in the “Editing” tool block. In the menu that appears, select “Find...”. Instead of these actions, you can simply type the key combination Ctrl+F on your keyboard.
2. After you have followed the appropriate items on the ribbon, or pressed a hotkey combination, the “Find and Replace” window will open in the “Find” tab. That's what we need. In the “Find” field, enter the word, symbols, or expressions that you are going to search for. Click on the “Find next” button, or on the “Find all” button.
3. When we click on the “Find Next” button, we move to the first cell that contains the entered groups of characters. The cell itself becomes active.

   The search and display of results is carried out line by line. All cells in the first row are processed first. If data matching the condition was not found, the program starts searching in the second line, and so on, until it finds a satisfactory result.

   Search symbols do not have to be independent elements. So, if the expression “rights” is specified as a query, then the results will show all cells that contain this sequential set of characters, even within a word. For example, the word “To the right” will be considered relevant to the query in this case. If you enter the number “1” in a search engine, the answer will be cells that contain, for example, the number “516”.

   To move on to the next result, click the “Find Next” button again.

   This can be continued until the results are displayed in a new circle.

4. If, when starting the search procedure, you click on the “Find all” button, all search results will be presented in the form of a list at the bottom of the search window. This list contains information about the contents of cells with data that satisfies the search request, their location address is indicated, as well as the sheet and book to which they belong. In order to go to any of the search results, simply click on it with the left mouse button. After this, the cursor will move to that Excel cell, which entry was clicked by the user.

Method 2: Search by specified cell range

If you have a fairly large table, then in this case it is not always convenient to search the entire sheet, because in search results there may be a huge number of results that are not needed in a particular case. There is a way to limit the search space to only a certain range of cells.

1. Select the area of ​​cells in which we want to search.
2. We type the key combination Ctrl+F on the keyboard, after which the familiar “Find and Replace” window will open. Further actions exactly the same as with the previous method. The only difference will be that the search is performed only in the specified range of cells.

Method 3: Advanced Search

As mentioned above, during a normal search, the search results include absolutely all cells containing a sequential set of search characters in any form, regardless of case.

In addition, not only the contents of a specific cell, but also the address of the element to which it refers can be included in the results. For example, cell E2 contains a formula that is the sum of cells A4 and C3. This sum is 10, and it is this number that appears in cell E2. But, if we enter the number “4” in the search, then the same cell E2 will be among the results. How could this happen? It’s just that cell E2 contains as a formula the address for cell A4, which just includes the desired number 4.

But how can we cut off these and other obviously unacceptable search results? It is for these purposes that Excel advanced search exists.

1. After opening the “Find and Replace” window using any method described above, click on the “Options” button.
2. A number of additional tools for managing the search appear in the window. By default, all of these tools are in the same state as a normal search, but adjustments can be made if necessary.

   By default, the “Consider case” and “Entire cells” functions are disabled, but if we check the corresponding boxes, then the entered case and exact match will be taken into account when generating the result. If you enter a word with a small letter, then cells containing the spelling of this word with a capital letter will no longer appear in the search results, as would be the case by default. In addition, if the “Whole Cells” function is enabled, then only elements containing the exact name will be added to the results. For example, if you set search query“Nikolaev”, then cells containing the text “Nikolaev A.D.” will no longer be added to the search results.

   By default, the search is performed only on the active Excel sheet. But, if you change the “Search” parameter to the “In book” position, then the search will be carried out across all sheets of the open file.

   In the Browse option, you can change the search direction. By default, as mentioned above, the search is carried out in line-by-line order. By moving the switch to the “By columns” position, you can set the order in which the results are generated, starting from the first column.

   In the “Search Area” column, it is determined which specific elements are being searched. By default, these are formulas, that is, the data that, when you click on a cell, is displayed in the formula bar. This can be a word, a number, or a cell reference. At the same time, the program, when performing a search, sees only the link, and not the result. This effect was discussed above. In order to search specifically according to the results, according to the data that is displayed in the cell, and not in the formula bar, you need to move the switch from the “Formulas” position to the “Values” position. In addition, it is possible to search by notes. In this case, move the switch to the “Notes” position.

   You can specify an even more precise search by clicking on the “Format” button.

   This opens the Format Cells window. Here you can set the format of the cells that will participate in the search. You can set restrictions on number format, by alignment, font, border, fill and protection, by one of these parameters, or by combining them together.

   If you want to use the format of a specific cell, then at the bottom of the window click on the “Use the format of this cell...” button.

   After this, a tool in the form of a pipette appears. Using it, you can select the cell whose format you are going to use.

   After the search format is configured, click on the “OK” button.

   There are times when you need to search not by a specific phrase, but to find the cells in which search words in any order, even if they are separated by other words and symbols. Then these words must be highlighted on both sides with a “*” sign. Now the search results will display all the cells containing these words in any order.

3. Once the search settings are set, you should click on the “Find all” or “Find next” button to go to the search results.

As we see, Excel program is a fairly simple, but at the same time very functional set of search tools. In order to make a simple squeak, just call up the search window, enter a query into it, and press the button. But, at the same time, it is possible to configure an individual search with big amount various parameters and additional settings.

We are glad that we were able to help you solve the problem.

Ask your question in the comments, describing the essence of the problem in detail. Our specialists will try to answer as quickly as possible.

Did this article help you?

A significant part of the problems that are solved using spreadsheets, assume that in order to detect the desired result, the user already has at least some initial data. However, Excel 2010 has necessary tools, with the help of which you can solve this problem in reverse - select the necessary data to obtain the desired result.

“Search for a solution” is one of these tools, most convenient for “optimization problems”. And if you haven’t had to use it before, now is the time to fix it.

So, we start by installing this add-in (since it will not appear on its own). Fortunately, now this can be done quite simply and quickly - open the “Tools” menu, and already in it “Add-ons”

All that remains is to indicate “Excel Add-ins” in the “Management” column, and then click the “Go” button.

After this simple action, the “Search for a solution” activation button will be displayed in “Data”. As shown in the picture

Let's look at how to properly use the solution search in Excel 2010 with a few simple examples.

Example one.

Let's say that you hold the post of head of a large production department and you need to correctly distribute bonuses to employees. Let's say total amount The bonus is 100,000 rubles, and it is necessary that the bonuses be proportional to salaries.

That is, now we need to select the correct proportionality coefficient to determine the size of the bonus relative to the salary.

First of all, you need to quickly create (if you don’t already have one) a table where the initial formulas and data will be stored, according to which you can get the desired result. For us, this result is the total amount of the premium. And now pay attention - the target cell C8 must be linked using formulas to the desired cell to be changed at address E2. This is critical. In the example, we connect them using intermediate formulas, which are responsible for calculating the bonus for each employee (C2:C7).

Now you can activate “Search for solutions”. A new window will open in which we need to specify the necessary parameters.

Under "" is our target cell. There can only be one.

"" are possible optimization options. There are a total of "Maximum", "Minimum" or "Specific" possible values ​​to choose from. And if you need a specific value, then you need to indicate it in the appropriate column.

"" - there can be several changeable cells (a whole range or separately specified addresses). After all, Excel will work with them, sorting through the options so that the value specified in the target cell is obtained.

"" - If you need to set restrictions, then you should use the “Add” button, but we will look at this a little later.

"" - a button for moving to interactive calculations based on the program we have specified.

But now let’s return to the ability to change our task using the “Add” button. This stage is quite important (no less than the construction of formulas), since it is the limitation that allows us to obtain the correct result at the output. Here everything is done as conveniently as possible, so you can set them not only for the entire range at once, but also for certain cells.

To do this, you can use a number of specific (and familiar to all Excel 2010 users) symbols “=”, “>=”, “

Today we will look at the Excel function − FIND(in English version FIND), which allows you to find the position of the search text in the text relative to the first character. Usually, but not always, the FIND function is used in conjunction with other Excel functions, which we will consider in the following articles, and today we will try to understand the principle of operation of this function.

Let's say we have a table with a list of first and last names.

Our task is to find the position of the space relative to the first character. For example, let's take the first name and last name Dani Estrada, let’s calculate on our own where the gap is. There is a space 5th character starting with the first letter. Now we’ll do it automatically using the FIND function.

The function syntax is as follows

FIND(search_text; viewed_text; [start_position ]), where

search_text is the text that we need to find in the text, in our case it is a space, it is designated like this “ “.

viewed_text– this is the text in which the desired text is searched, in our case this is the first and last name or cell A2

start_position is an optional argument; it indicates from which position in the account we need to search for the text. This may be needed, for example, if the search text occurs twice and we need to know the position of the second search text.

We write the formula =FIND(" " ;A2 ;1), drag it down and get the result we need.

This tutorial explains the main benefits of the functions INDEX And SEARCH in Excel, which make them more attractive compared to VLOOKUP. You will see several examples of formulas that will help you easily cope with many complex tasks that the function faces. VLOOKUP powerless.

In several recent articles, we have made every effort to explain the basics of the function to novice users. VLOOKUP and show examples of more complex formulas for advanced users. We will now try, if not dissuade you from using VLOOKUP, then at least show alternative ways implementation of vertical search in Excel.

Why do we need this? - you ask. Yes, because VLOOKUP Search is not the only search feature in Excel, and its many limitations can prevent you from getting the results you want in many situations. On the other hand, functions INDEX And SEARCH– more flexible and have a number of features that make them more attractive compared to VLOOKUP.

Basic information about INDEX and MATCH

Since the purpose of this tutorial is to show the capabilities of functions INDEX And SEARCH to implement vertical search in Excel, we will not dwell on their syntax and application.

Here we present the minimum necessary to understand the essence, and then we will examine in detail examples of formulas that show the advantages of using INDEX And SEARCH instead of VLOOKUP.

INDEX – function syntax and usage

Function INDEX(INDEX) in Excel returns a value from an array at the given row and column numbers. The function has this syntax:

Each argument has a very simple explanation:

• array(array) is the range of cells from which you want to extract the value.
• row_num(line_number) is the number of the line in the array from which you want to extract the value. If not specified, then an argument is required column_num(column_number).
• column_num(column_number) is the number of the column in the array from which you want to extract the value. If not specified, then an argument is required row_num(line_number)

If both arguments are specified, then the function INDEX returns the value from the cell at the intersection of the specified row and column.

Here simplest example functions INDEX(INDEX):

INDEX(A1:C10,2,3)
=INDEX(A1:C10,2,3)

Formula searches a range A1:C10 and returns the cell value in 2nd line and 3m column, that is, from a cell C2.

Very simple, right? However, in practice, you do not always know which row and column you need, and therefore you need the help of the function SEARCH.

MATCH - function syntax and usage

Function MATCH(MATCH) in Excel searches for a specified value in a range of cells and returns the relative position of that value in the range.

For example, if in the range B1:B3 contains the values ​​New-York, Paris, London, then the following formula will return the number 3 , since “London” is the third element in the list.

MATCH("London",B1:B3,0)
=MATCH("London";B1:B3;0)

Function MATCH(MATCH) has the following syntax:

MATCH(lookup_value,lookup_array,)
MATCH(lookup_value, lookup_array, [match_type])

• lookup_value(search_value) is the number or text you are looking for. The argument can be a value, including a boolean, or a cell reference.
• lookup_array(viewed_array) – the range of cells in which the search occurs.
• match_type(match_type) – This argument tells the function SEARCH, whether you want to find an exact or approximate match:
  • 1 or not specified– finds the maximum value less than or equal to the desired one. The array being viewed must be ordered in ascending order, that is, from smallest to largest.
  • 0 – finds the first value equal to the desired one. For combination INDEX/SEARCH you always need an exact match, so the third argument to the function SEARCH must be equal 0 .
  • -1 – finds the smallest value greater than or equal to the search value. The array being viewed must be sorted in descending order, that is, from largest to smallest.

At first glance, the benefit of the function SEARCH is doubtful. Who needs to know the position of an element in a range? We want to know the meaning of this element!

Let us remind you that the relative position of the value we are looking for (i.e. row and/or column number) is exactly what we need to specify for the arguments row_num(line_number) and/or column_num(column_number) functions INDEX(INDEX). As you remember, the function INDEX can return the value located at the intersection given strings and column, but it cannot determine which row and column we are interested in.

How to Use INDEX and MATCH in Excel

Now that you know the basic information about these two functions, I believe that it is already becoming clear how the functions SEARCH And INDEX can work together. SEARCH determines the relative position of the search value in a given range of cells, and INDEX uses that number (or numbers) and returns the result from the corresponding cell.

Still not entirely clear? Present the functions INDEX And SEARCH in this form:

INDEX(,(MATCH ( search value,the column in which we are looking,0))
=INDEX( the column from which we extract;(MATCH( search value;the column in which we are looking;0))

I think it will be even easier to understand with an example. Suppose you have the following list of state capitals:

Let's find the population of one of the capitals, for example, Japan, using the following formula:

INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))
=INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))

Now let's look at what each element of this formula does:

• Function MATCH(MATCH) looks for the value “Japan” in the column B, and specifically – in cells B2:B10, and returns a number 3 , since “Japan” is in third place on the list.
• Function INDEX(INDEX) uses 3 for argument row_num(row_number), which specifies from which row the value should be returned. Those. we get a simple formula:

  INDEX($D$2:$D$10,3)
  =INDEX($D$2:$D$10,3)

  The formula says something like this: look in cells from D2 before D10 and extract the value from the third row, that is, from the cell D4, since counting starts from the second line.

This is the result you get in Excel:

Important! Number of rows and columns in the array that the function uses INDEX(INDEX), must match the argument values row_num(line_number) and column_num(column_number) functions MATCH(MATCH). Otherwise, the result of the formula will be erroneous.

Wait, wait... why can't we just use a function VLOOKUP(VPR)? Is there any point in wasting time trying to figure out mazes? SEARCH And INDEX?

VLOOKUP("Japan",$B$2:$D$2,3)
=VLOOKUP("Japan",$B$2:$D$2,3)

In this case, there is no point! The purpose of this example is purely for demonstration purposes so that you can understand how the functions SEARCH And INDEX work in pairs. The following examples will show you the true power of the bundle. INDEX And SEARCH, which easily copes with many difficult situations when VLOOKUP finds himself in a dead end.

Why is INDEX/MATCH better than VLOOKUP?

When deciding what formula to use for a vertical search, most Excel gurus believe that INDEX/SEARCH much better than VLOOKUP. However, many Excel users still resort to using VLOOKUP, because this function is much simpler. This happens because very few people fully understand all the benefits of switching from VLOOKUP per bunch INDEX And SEARCH, and no one wants to waste time studying a more complex formula.

4 Main Benefits of Using MATCH/INDEX in Excel:

1. Search from right to left. As any competent Excel user knows, VLOOKUP cannot look to the left, which means that the value being sought must necessarily be in the leftmost column of the range being examined. In case of SEARCH/INDEX, the search column can be either on the left or right side of the search range. Example: will show this feature in action.

2. Safely add or remove columns. Formulas with function VLOOKUP stop working or return erroneous values ​​if you remove or add a column to a lookup table. For function VLOOKUP any inserted or removed column will change the result of the formula because the syntax VLOOKUP requires you to specify the entire range and the specific column number from which you want to extract data.

For example, if you have a table A1:C10, and you want to retrieve data from a column B, then you need to set the value 2 for argument col_index_num(column_number) functions VLOOKUP, like this:

VLOOKUP("lookup value",A1:C10,2)
=VLOOKUP("lookup value";A1:C10;2)

If you later insert a new column between the columns A And B, then the value of the argument will have to be changed from 2 on 3 , otherwise the formula will return the result from the newly inserted column.

Using SEARCH/INDEX You can remove or add columns to the range being examined without distorting the result, since the column containing the desired value is directly defined. Indeed, this is a big advantage, especially when you have to work with large amounts of data. You can add and remove columns without worrying about having to fix every function you use VLOOKUP.

3. There is no limit on the size of the searched value. Using VLOOKUP, remember that the length of the searched value is limited to 255 characters, otherwise you risk getting an error #VALUE!(#VALUE!). So, if the table contains long rows, the only workable solution is to use INDEX/SEARCH.

Let's say you use this formula with VLOOKUP, which searches in cells from B5 before D10 the value specified in the cell A2:

VLOOKUP(A2,B5:D10,3,FALSE)
=VLOOKUP(A2,B5:D10,3,FALSE)

The formula will not work if the value in the cell is A2 longer than 255 characters. Instead, you need to use a similar formula INDEX/SEARCH:

INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))

4. Higher operating speed. If you work with small tables, then the difference in Excel performance will most likely not be noticeable, especially in latest versions. If you work with large tables that contain thousands of rows and hundreds of search formulas, Excel will work much faster if you use SEARCH And INDEX instead of VLOOKUP. In general, this replacement increases speed Excel work on 13% .

Influence VLOOKUP Excel performance is especially noticeable if workbook contains hundreds of complex array formulas such as VLOOKUP+SUM. The point is that checking each value in the array requires separate call functions VLOOKUP. Therefore, the more values ​​an array contains and the more array formulas your table contains, the slower Excel works.

On the other hand, a formula with functions SEARCH And INDEX it simply performs a search and returns the result, performing similar work noticeably faster.

INDEX and MATCH - examples of formulas

Now that you understand the reasons why you should learn functions SEARCH And INDEX, let's get to the fun part and see how you can apply theoretical knowledge in practice.

How to search from the left side using MATCH and INDEX

Any textbook on VLOOKUP says that this function cannot look to the left. Those. if the column you are looking at is not the leftmost one in the search range, then there is no chance of getting from VLOOKUP desired result.

Functions SEARCH And INDEX Excel is much more flexible and doesn't care where the column with the value you want to retrieve is located. For example, let's return to the table with state capitals and population. This time we will write the formula SEARCH/INDEX, which will show what place the capital of Russia (Moscow) occupies in terms of population.

As you can see in the figure below, the formula does this job perfectly:

INDEX($A$2:$A$10,MATCH("Russia",$B$2:$B$10,0))

Now you should have no problem understanding how this formula works:

• First, let's use the function MATCH(MATCH), which finds the position of “Russia” in the list:

  MATCH("Russia",$B$2:$B$10,0))
  =MATCH("Russia",$B$2:$B$10,0))

• Next, set the range for the function INDEX(INDEX) from which to extract the value. In our case it is A2:A10.
• Then we combine both parts and get the formula:

  INDEX($A$2:$A$10;MATCH("Russia";$B$2:$B$10,0))
  =INDEX($A$2:$A$10,MATCH("Russia",$B$2:$B$10,0))

Clue: The correct solution is to always use absolute references for INDEX And SEARCH, so that the search ranges do not get lost when copying the formula to other cells.

Calculations using INDEX and MATCH in Excel (AVERAGE, MAX, MIN)

You can invest others Excel functions V INDEX And SEARCH, for example, to find the minimum, maximum, or closest to the average value. Here are several options for formulas in relation to the table from:

1. MAX(MAX). The formula finds the maximum in a column D C the same line:

INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))

Result: Beijing

2. MIN(MIN). The formula finds the minimum in a column D and returns the value from the column C the same line:

INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))

Result: Lima

3. AVERAGE(AVERAGE). The formula calculates the average of a range D2:D10, then finds the closest one to it and returns the value from the column C the same line:

INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))
=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))

Result: Moscow

Things to remember when using the AVERAGE function with INDEX and MATCH

Using the function AVERAGE in combination with INDEX And SEARCH, as the third argument of the function SEARCH most often you will need to indicate 1 or -1 in case you are not sure that the range you are viewing contains a value equal to the average. If you are sure that such a value exists, put 0 to find an exact match.

• If you indicate 1 , the values ​​in the lookup column should be ordered in ascending order, and the formula will return the maximum value less than or equal to the average.
• If you indicate -1 , the values ​​in the lookup column should be ordered in descending order and the return will be minimum value, greater than or equal to the average.

In our example, the values ​​in the column D are ordered in ascending order, so we use the collation type 1 . Formula INDEX/SEARCHPOZ returns “Moscow”, since the population of the city of Moscow is the closest smaller to the average value (12,269,006).

How to use INDEX and MATCH to search a known row and column

This formula is equivalent to a two-dimensional search VLOOKUP and allows you to find the value at the intersection of a specific row and column.

In this example the formula INDEX/SEARCH will be very similar to the formulas that we have already discussed in this lesson, with only one difference. Guess which one?

As you remember, the function syntax INDEX(INDEX) allows three arguments:

INDEX(array,row_num,)
INDEX(array, row_number, [column_number])

And I congratulate those of you who guessed it!

Let's start by writing down the formula template. To do this, let’s take the formula that is already familiar to us INDEX/SEARCH and add another function to it SEARCH, which will return the column number.

INDEX(Your table ,(MATCH(, column to search in,0)),(MATCH(, line to search in,0))
=INDEX(Your table ,(MATCH( value for vertical search,column to search in,0)),(MATCH( value for horizontal search,line to search in,0))

Note that for two-dimensional search you need to specify the entire table in the argument array(array) functions INDEX(INDEX).

Now let's try this pattern in practice. Below you see a list of the most populated countries in the world. Let's say our task is to find out the population of the United States in 2015.

Okay, let's write down the formula. When I need to create a complex formula in Excel with nested functions, I first write down each nested function separately.

So let's start with two functions SEARCH, which will return the row and column numbers for the function INDEX:

• MATCH for column– we are looking in the column B, or rather in the range B2:B11, the value that is specified in the cell H2(USA). The function will look like this:

  MATCH($H$2,$B$1:$B$11,0)
  =MATCH($H$2,$B$1:$B$11,0)

  4 , since “USA” is the 4th list element in the column B(including title).

• MATCH for string– we are looking for the cell value H3(2015) in line 1 , that is, in cells A1:E1:

  MATCH($H$3,$A$1:$E$1,0)
  =MATCH($H$3,$A$1:$E$1,0)

  The result of this formula will be 5 , since “2015” is in the 5th column.

Now we insert these formulas into the function INDEX and voila:

INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))

If you replace the functions SEARCH based on the values ​​they return, the formula will become easy and understandable:

INDEX($A$1:$E$11,4,5))
=INDEX($A$1:$E$11,4,5))

This formula returns the value at the intersection 4th lines and 5th column in range A1:E11, that is, the cell value E4. Just? Yes!

Multi-criteria search with INDEX and MATCH

In the tutorial on VLOOKUP we showed an example of a formula with a function VLOOKUP to search using multiple criteria. However, a significant limitation of this solution was the need to add an auxiliary column. Good news: formula INDEX/SEARCH can search across values ​​in two columns, without the need to create a helper column!

Suppose we have a list of orders and we want to find the amount based on two criteria − buyer's name(Customer) and product(Product). The matter is complicated by the fact that one buyer can buy several different products at once, and the names of buyers in the table on the sheet Lookup table arranged in random order.

Here's the formula INDEX/SEARCH solves the problem:

(=INDEX("Lookup table"!$A$2:$C$13,MATCH(1,(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13),0),3))
(=INDEX("Lookup table"!$A$2:$C$13;MATCH(1,(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13);0);3))

This formula is more complex than others that we discussed earlier, but armed with knowledge of the functions INDEX And SEARCH You will defeat her. The hardest part is the function SEARCH, I think it needs to be explained first.

MATCH(1,(A2="Lookup table"!$A$2:$A$13),0)*(B2="Lookup table"!$B$2:$B$13)
MATCH(1;(A2="Lookup table"!$A$2:$A$13);0)*(B2="Lookup table"!$B$2:$B$13)

In the formula shown above, the value we are looking for is 1 , and the search array is the result of the multiplication. Okay, what should we multiply and why? Let's look at everything in order:

• Take the first value in the column A(Customer) on sheet Main table and compare it with all the customer names in the table on the sheet Lookup table(A2:A13).
• If a match is found, the equation returns 1 (TRUE), and if not - 0 (LIE).
• Next, we do the same for the column values B(Product).
• Then we multiply the results obtained (1 and 0). Only if matches are found in both columns (i.e. both criteria are true), you will receive 1 . If both criteria are false, or only one of them is satisfied, you will receive 0 .

Now you understand why we asked 1 , what is the desired value? It is correct that the function SEARCH returned the position only when both criteria were met.

Note: In this case, you must use the third optional argument of the function INDEX. It is necessary because in the first argument we specify the entire table and must tell the function which column to retrieve the value from. In our case this is the column C(Sum), and so we entered 3 .

And finally, because we need to check every cell in the array, this formula must be an array formula. You can see this by curly braces, in which it is enclosed. So when you're done entering the formula, don't forget to click Ctrl+Shift+Enter.

If everything is done correctly, you will get the result as in the figure below:

INDEX and MATCH combined with IFERROR in Excel

As you have probably already noticed (more than once), if you enter an incorrect value, for example, one that is not in the array being viewed, the formula INDEX/SEARCH reports an error #N/A(#N/A) or #VALUE!(#VALUE!). If you want to replace such a message with something more understandable, you can insert a formula with INDEX And SEARCH into a function IFERROR.

Function Syntax IFERROR very simple:

IFERROR(value,value_if_error)
IFERROR(value,value_if_error)

Where is the argument value(value) is the value being checked for an error (in our case, the result of the formula INDEX/SEARCH); and the argument value_if_error(value_if_error) is the value to be returned if the formula throws an error.

For example, you can insert into a function IFERROR like this:

IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),
"No matches found. Try again!") =IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1 :$E$1;0));
"No matches found. Try again!")

And now, if someone enters an incorrect value, the formula will produce this result:

If you prefer to leave the cell empty in case of an error, you can use quotes (“”) as the value of the second argument of the function IFERROR. Like this:

IFERROR(INDEX(array,MATCH(lookup_value,lookup_array,0),"")
IFERROR(INDEX(array,MATCH(lookup_value,looked_array,0),"")

I hope that you found at least one formula described in this textbook useful. If you have encountered other search problems for which you could not find a suitable solution among the information in this lesson, feel free to describe your problem in the comments, and we will all try to solve it together.

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)

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 greater than the desired one, but as close as possible to it (although if the data allows - for more precise search It’s 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 articles 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.

("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; text-align:left;","textbgcss":"display:absolute; left:0px; ; 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))