We have a table in which the sales volumes of certain goods are recorded in different months. It is necessary in the table to find the data, and the search criterion will be headlines of rows and columns. But the search should be performed separately by the range of the line or column. That is, only one of the criteria will be used. Therefore, the Index function cannot be applied here, and a special formula is needed.

Search for values \u200b\u200bin Excel Table

To solve this problem, we illustrate an example on a schematic table, which corresponds above the conditions described.

Sheet with a table to search for values \u200b\u200bvertically and horizontal:

Above the table itself is a string with results. In the cell B1 there is a criterion for a search query, that is, a column header or a string name. And in the cell D1, the search formula must return the result of calculating the corresponding value. After that, the second formula will work in the F1 cell, which will already use the values \u200b\u200bof the cells B1 and D1 as the criteria for searching for the relevant month.

Search Values \u200b\u200bin the Excel string

Now we learn what maximum volume and in which month was the maximum sale of goods 4.

To search for columns follows:

Found in which month and what was the largest sale of goods 4 for two quarters.

Principle of action search formula Value in the Excel line:

In the first argument, the PRD (vertical view) function indicates a link to a cell where the search criteria is. The second argument indicates the range of cells for viewing during the search process. In the third argument, the PRD function should be indicated by the column number from which the value should be taken against the row with the name of the goods 4. But since we do not know this number in advance. We use the column function. Create an array of column numbers for the B4 range: G15.

This allows the functions of the VDC collect a whole array of values. As a result, all the corresponding values \u200b\u200bof each column on the line Item 4 are stored in memory (namely: 360; 958; 201; 605; 462; 832). After that, the Max functions remain only to take a maximum number from this array and return as a value for a cell D1, as the result of the calculation of the formula.

As you can see the design of the formula simple and laconic. On its basis, it is possible in a similar way to find for a specific product and other indicators. For example, the minimum or average value of sales uses for this function mines or CPNAV. You do not prevent you that this skeleton of the formula is applied using more complex functions to implement the most comfortable analysis of the sales report.

How to get columns headlines on the inspirit of one cell?

For example, as spectacularly we displayed a month in which there was a maximum sale using a second formula. It is not difficult to notice that in the second formula we used the skeleton of the first formula without the Max function. The main structure of the formula: PRD (B1; A5: G14; column (B5: G14); 0). We replaced the Max function on the search, which in the first argument uses the value obtained by the previous formula. It now acts as a criterion to search for a month. And as a result, the search function for us returns the number of the column 2 where is located maximum value Sales volume for the product 4. After that, the function includes an index function that returns the value by the timeline and the column from the range defined in its arguments. Since we have a column number 2, and the row number in the range where the names of the months are stored in any cases will be 1. Then it remains a function of the index to obtain a corresponding value from the B4 range: G4 - February (second month).



Search Values \u200b\u200bin the Excel column

The second task is to search the table using the name of the month as a criterion. In such cases, we must change the skeleton of our formula: the function of the UPR is replaced with the GRP, and the function of the column is replaced by a string.

This will allow us to find out what volume and which product was the maximum sale at a certain month.

To find what product had the maximum sales in a certain month, it follows:

Principle of action search formula Value in the Excel column:

In the first argument, the GPR (horizontal view) function indicate a link to a cell with a search criterion. In the second argument, a link to the viewed range of the table is indicated. The third argument generates a string function that creates an array of lines from 10 elements in memory. Since in the table part we have 10 lines.

Next, the GRP function alternately using each line number creates an array of appropriate sales values \u200b\u200bfrom the table at a certain month (June). Next, the Max functions remain only to choose the maximum value from this array.

ATTENTION! When using a skeleton of formulas for other tasks, always pay attention to the second and third argument of the search function of the GRP. The number of rows covered in the range of the argument specified in the argument should coincide with the number of rows in the table. Also, the numbering should begin with the second row!

In documents Microsoft Excel.which consist of large number Fields often need to find certain data, the name of the string, etc. Very uncomfortable when you have to view a huge number of rows to find the desired word or expression. Save time and nerves will help the built-in Microsoft Excel search. Let's figure it out how it works, and how to use it.

Search function B. microsoft program Excel offers the ability to find the desired text or numeric values \u200b\u200bthrough the "Find and Replace" window. In addition, the application has the ability to enhanced data search.

Method 1: Simple search

A simple data selection in the Excel program allows you to find all the cells that contain the character set in the search window (letters, numbers, words, etc.) without registering the register.

Method 2: Search at the specified interval of cells

If you have a rather large-scale table, then in this case it is not always convenient to search all over the 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 only with a certain range of cells.

Method 3: Advanced Search

As mentioned above, with the usual search for issuing results, absolutely all cells containing a serial set of search symbols in any form are not dependent on the register.

In addition, not only the contents of a particular cell, but also the address of the element to which it refers can be released. For example, in the cell E2 contains a formula that is the sum of the cells A4 and C3. This amount is 10, and this number is displayed in the E2 cell. But, if we ask in the search for the number "4", then among the results of issuing there will be the same cell E2. How could this happen? Simply in the E2 cell, the formula contains an address to the A4 cell, which just includes the desired figure 4.

But, how to cut off such, and other knowingly unacceptable search results? It is for these purposes that there is an advanced search Excel.

1. After opening the window "Find and replace" In any way described in the way, click on the button "Parameters".



2. A number of additional tools for managing the search appear in the window. By default, all these tools are in a state, as with normal search, but if necessary, you can adjust.

   

   Default function "Consider the register" and "Cells entirely" Disabled, but if we put the ticks near the relevant items, then in this case, when the result is generated, the entered register will be taken into account, and the exact coincidence. If you enter a word with a small letter, then in search issuance, cells containing the writing of this word with a capital letter, as it would be by default, will no longer fall. In addition, if the function is enabled "Cells entirely", Only elements containing accurate name will be added to the issuance. For example, if you specify search query "Nikolaev", then the cells containing the text "Nikolaev A. D." will not be added to the issuance.

   

   By default, the search is made only on active excel sheet. But if the parameter "Search" You will transfer to the position "In the book"The search will be made on all open files.

   

   In parameter "Browse" You can change the search direction. By default, as mentioned above, the search is carried out in order line. Rearming the switch to the position "By columns", You can set the procedure for the formation of the results of issuing, starting from the first column.

   

   In graf "Search area" Determined, among which specific items are searched. By default, these are formulas, that is, those data that when clicking on the cell is displayed in the formula string. It may be a word, a number or link to the cell. At the same time, the program, performing the search, sees only the reference, and not the result. This effect was discussed above. In order to search precisely on the results, the data that is displayed in the cell is displayed, and not in the formula string, you need to rearrange the switch from the position "Formulas" To position "Values". In addition, there is a possibility of searching for notes. In this case, the switch is rearranged to the position "Notes".

   

   Even more accurately, the search can be set by clicking on the button. "Format".

   

   This opens the cell format window. Here you can set the format of the cells to participate in the search. You can set restrictions on numeric format, alignment, font, border, fill and protection, one of these parameters, or combining them together.

   

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

   

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

   

   After the search format is configured, click on the button Ok.

   

   There are cases when you need to search not for specific phrases, but find cells in which search words are in any order, even if other words and symbols are separated. Then these words should be allocated on both sides the "*" sign. Now in the search results will be displayed all cells in which these words are located in any order.



3. As soon as the search settings are installed, click on the button "Find all" or "Find further"To go to search results.

As you can see excel program It is quite simple, but at the same time a very functional search for search tools. In order to produce the simplest squeak, it is enough to call a search box, enter a query in it, and click on the button. But, at the same time, it is possible to configure individual search with a large number of different parameters and additional settings.

Conditional formatting (5)
Lists and ranges (5)
Macros (VBA Procedures) (63)
Miscellaneous (39)
Excel bugs and glitches (3)

How to find a value in another table or PRP power

In fact, I want to tell about the opportunity not only in this article. functions of Armsbut also want to affect and Search boardAs a very related function with an Arbitration. Each of these functions has both its advantages and cons. If in a nutshell, then the UPR is looking for some value specified by us among many values \u200b\u200blocated in one column. Perhaps most often the need for an Arbitration occurs when it is necessary to compare the data, find the data in another table, from the same table add data to another, based on any criterion, etc.
To a little better understand the principle of operation of the UPR, it is better to start with a certain practical example. There is a table of this type:
fig.1

and from the first table must be substituted into the second date for each name. For three recordings, this is not a problem and make hands - everything is obvious. But in life, these are tables on thousands of entries and the search with the data substitution manually may take not one hour. Plus another pair of flying in the target: not only the fact that the name is located completely differently in both tables and the number of records in the tables are different, so more tables are located on different sheets / books. I suppose that I convinced you that the substitution of these hands is not an option at all. But VLOOKUP) It will be indispensable here. At the same time, practically nothing will need to do - only write in the first cell of the column from the second table (where it is necessary to substitute dates from the first table) Such formula:
\u003d UPR ($ A2; List1! $ A $ 2: $ C $ 4; 3; 0)
You can write the formula either directly into the cell, or using the function dispatcher by selecting in the category Links and arrays PRD and separately specifying the necessary criteria. Now copy ( Ctrl+C.) Cell with a formula that allocate all cells of the column with to the end of the data and insert ( Ctrl+V.).

First, the main principle of operation: UPR is looking for a table value specified by the argument in the first column of the argument Skeeping_New . When you find the desired value, the function returns the value opposite the value found, but from the column specified by the argument Number_stolbets . With interval view, we'll figure it out a little later. VDP can return only one values \u200b\u200b- the first one suitable for the criterion. If the desired value is not found (missing in the table)then the result will be the function # N / D . Do not be afraid of this - it is even useful. You will know exactly what records are not and thus you can compare two tables with each other. Sometimes it turns out that you see: the data is in both tables, but the VD issues # n / d. So the data in your tables are not identical. In some of them there are extra inconspicuous gaps (usually before the value or after), or the signs of Cyrillic are mixed with Latin signs. Same # N / D will be if the criteria of the number and in the desired table they are recorded as text (As a rule, green triangles appears in the upper left corner of such a cell), and in the final - as numbers. Or vice versa.

Description of arguments of the Argument
$ A2 - Argument Skeeping_New (Let's call it Criterion to be short). This is what we are looking for. Those. For the first record of the second table, it will be Petrov S.A. Here you can specify either the text of the criterion (in this case, it should be in quotes - \u003d VD ("Petrov S.A"; list1! $ A $ 2: $ C $ 4; 3; 0), or a link to the cell, with this text (as in the example function). There is a small nuance: you can also apply substitution symbols: "*" and "?". It is very convenient if you need to find values \u200b\u200bonly by part of the line. For example, you can not enter completely "Petrov S.A.", but to introduce only the surname and the star sign - "Petrov *". Then any entry will be withdrawn, which begins on Petrov. If you need to find an entry in which the name "Petrov" is found anywhere in the line, then you can specify this: "* Petrov *." If you want to find the surname Petrov and no matter what initials will be at the name-personal name (if the name is recorded in the form of Ivanov I.I.), then here is the best one: "Ivanov?.?." . It is often necessary to specify its value for each line (in the column and the last name and you need to find them all). In this case, references to the cells of the column A. For example, in the cell A1 recorded: Ivanov. It is also known that Ivanov is in another table, but after the surname, the name and patronymic may be recorded (or something else). But we need to find only a string that begins on the surname. Then you need to record as follows: A1 & "*". This entry will be equivalent to "Ivanov *". In A1, Ivanov is recorded, ampersand (&) is used to merge into one line of two text values. An asterisk in quotes (as it should be to be text inside the formula). So we get:
A1 & "*" \u003d\u003e
"Ivanov" & "*" \u003d\u003e
"Ivanov *"
Very convenient if there are many characters for searching.
If you need to determine if there is a word somewhere in the string, then the stars put on both sides: "*" & A1 & "*"

Sheet1! $ A $ 2: $ C $ 4 - Argument Table. Specifies the range of cells. Only the range must contain data from the first cell with the data to the mostst. This does not have to be specified in the example range. If strings 100, then list1! $ A $ 2: $ C $ 100. It is important to remember three things: first, it Table should always start with that column in which we are looking for Criterion . And nothing else. Otherwise, nothing is found will not be or the result will not be exactly the one that expects. Second: Argument Table must be "fixed" . What does it mean. See the dollar signs - $? This is fixing (More precisely, this is called an absolute link to the range). How it's done. Allocate the link text (Only one range is one criterion) And Zhmete F4. Until we see that the dollar did not appear before the designation name of the column name and before the line number. If this is not done, then when copying the formula, the argument table will "move" and the result will be incorrect again. And last - the table must contain columns from the first (in which we are looking for) to the latter (from which it is necessary to return values). In the example List1! $ A $ 2: $ C $ 4 - So it will not be possible to return the value from the column D (4), because The table is only three columns.

3 - Number_stolbets . Here simply specify the column number in the argument Table, Values \u200b\u200bfrom which we need to substitute as a result. In the example, this is the date of adoption - i.e. Column number 3. If I needed a department, I would indicate 2, and if we needed to simply compare if there were one table names in another, then it would be possible to indicate and 1. Important: argument Number_stolbets should not exceed the number of columns in the argument Table . Otherwise the result of the formula will be an error #LINK!. For example, if a range of $ b $ 2: $ C $ 4 is specified and it is necessary to return the data from the column C, then correctly indicate 2. Because argument Table($ B $ 2: $ C $ 4) contains only two columns - in and C. If you try to specify the number of the column 3 (what account is on the sheet), we will get a mistake #LINK!because The third column in the specified range is simply not.

Practical Tip: If the argument table has too large number of columns and you need to return the result from the last column, then it is not necessary to calculate them with the count. It can be specified like this: \u003d VD ($ A2; List1! $ A $ 2: $ C $ 4; Numeric (sheet1! $ A $ 2: $ C $ 4); 0). By the way in this case sheet1! You can also remove as unnecessary: \u200b\u200b\u003d VD ($ A2; List1! $ A $ 2: $ C $ 4; NUTERSTOLBA ($ A $ 2: $ C $ 4); 0).

0 - Interval_Prising - Very interesting argument. It may be equal to either true or false. Immediately the question arises: why in my formula there 0? Everything is very simple - Excel in the formulas can perceive 0 as a lie, and 1 as truth. If in the UPR to specify this parameter is 0 or a lie, then the search for accurate compliance with the specified criterion will occur. It has nothing to do with substitution signs ("*" and "?"). If you use 1 or truth (or do not specify the last argument at all, because by default it is the truth), then ... a very long history. In short - the EPR will look for the most similar value suitable for Criterion . Sometimes very useful. True, if you use this parameter, it is necessary that the list in the argument table is sorted ascending. I draw attention to the fact that the sorting is necessary only if the argument interval_specting you are equal to the truth or 1. If 0 or a lie - the sorting is not needed.

Many probably noticed that in my picture I had apartments for FULL NAME. This is not an entry error. In the example attached to the article, it is shown, as possible to substitute them and dates, without changing the manual argument Number_stolbets. It seemed to me that such an example could well come in handy.

How to avoid errors # n / d (# n / a) in the UPR?
Another frequent problem - many do not want to see # N / D results, if the coincidence is not found. It is easy to get around:
\u003d If (UND (EGR ($ A2; List1! $ A $ 2: $ C $ 4; 3; 0)); ""; Arm ($ A2; List1! $ A $ 2: $ C $ 4; 3; 0))
Now if an arbitrariness does not find the coincidence, the cell will be empty.
And users of Excel 2007 versions can be used if you can use if:
\u003d If utility (Arm A2; list1! $ A $ 2: $ C $ 4; 3; 0); "")

Promised search board

This feature is looking for the value specified by the parameter Skeeping_New in argument Visible_Massive . And the result of the function is the position number of the found value in Viewed_Massive. It is the position number, not the value itself. In principle, I will not paint it in the same way, because the main points are exactly the same. If we wanted to apply it for the table above, it would be like this:
\u003d Search Company ($ A2; List1! $ A $ 2: $ A $ 4; 0)
$ A2 is the desired one. Here everything is exactly the same as with the UPR. The symbols of the substitution and exactly in the same performance are also allowed.

Leaf1! $ A $ 2: $ A $ 4 - viewed_Massive. The main difference from the UPR is allowed to specify an array with only one column. It should be that column in which we are going to look Skeeping_New . If you try to specify more than one column, the function will return the error.

TYPE_SPECTIONS (0) - the same as in the UPR Interval_Prising . With the same features. It is distinguished by the opportunity to search for the smallest from the desired or largest. But on this I will not stop in this article.

Basically dealt with. But we need to return the number of position, but the value itself. Therefore, the search attempt is not suitable for us. At least one, by itself. But if it is used together with the function index - then this is what we need and even more.
\u003d Index (sheet1! $ A $ 2: $ C $ 4; search board ($ A2; list1! $ A $ 2: $ A $ 4; 0); 2)
Such a formula will return the same as an ARD.

Arguments Function Index
Leaf1! $ A $ 2: $ C $ 4 - an array. As this argument, we indicate the range from which we want to get values. It can be both one column and several. If the column is one, the last argument does not need to specify the function. By the way, this argument can absolutely not to coincide with the one that we point out in the argument viewed by_Massive the function of the search board.

Next comes number_stroke and number_stall. It is as a number that we substitute the search board, which returns to us position number in the array. This is built on it. The index returns the value from the array that is in the specified line (server number) of the array and the specified column (number_number) if the columns are more than one. It is important to know that in this bundle the number of rows in the argument an array of the function index and the number of strings in the argument viewed by_Massive the functions of the search board should coincide. And start with the same line. This is in normal cases, if you do not pursue different purposes.
Also, as in the case of an HDP, the index in case of not finding the desired value returns # H / D. And you can bypass such errors in the same way:
For all Excel versions (including 2003 before):
\u003d If (UND ($ A2; list1! $ A $ 2: $ A $ 4; 0)); ""; index (sheet1! $ A $ 2: $ C $ 4; Search Company ($ A2; List1! $ A $ 2: $ A $ 4; 0); 2))
For versions of 2007 and above:
\u003d If utility (index (sheet1! $ A $ 2: $ C $ 4; search board ($ A2; list1! $ A $ 2: $ A $ 4; 0); 2); "")

Working with criteria is longer than 255 characters
There is a search index and one more advantage before the PRD. The fact is that the EPR cannot look for meaning length of the string of which contains more than 255 characters. It happens rarely, but it happens. You can, of course, deceive the Arbitration and Cut the criterion:
\u003d PRP (PSTr ($ A2; 1; 255); PSTr (sheet1! $ A $ 2: $ C $ 4; 1; 255); 3; 0)
But this is an array formula. And besides, not always such a formula will return the desired result. If the first 255 characters are identical to the first 255 characters in the table, and then the signs differ - the formula will not see this. Yes, and returns the formula exceptionally textual values, which in cases where the numbers should be returned, not very convenient.

Therefore, it is better to use such a cunning formula:
\u003d Index (sheet1! $ A $ 2: $ C $ 4; Sumpered (TRUTH; list1! $ A $ 2: $ A $ 4 \u003d $ A2; 0)); 2)
Here I used the same ranges for readability, but in the example for download, they differ from those indicated here.
The formula itself is built on the capabilities of the function of the Sumpera transform into massive calculations of some functions within it. In this case, the search possession is looking for a row position in which the criterion is equal to the row value. The wildcard characters here cannot be applied.

In the example embodiment, you will find examples of using all the cases described and an example of why the index and the search board are sometimes preferable to the UPR.

Download example

(26.0 KIB, 14,082 downloads)

The article helped? Share a link with 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", "TEXTEFFECTSLIDIRECTION1": "RIGHT", "TEXTEFFECTSLIDISTANCE1": 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: A LPHA (opacity \u003d 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," TexTeffectResPonsize ": 640," TitleCsSResponsive ":" Font-Size: 12px; "," DescriptionCSSResponsive ":" Display: None! Important; "," ButtonCsSResponsive ": "", "addgoooglefonts": false, "googlefonts": "", "TextLeftRightPerCentForStatic": 40))

Hi friends. How often do you have to look for compliance in the Excel table for any meaning? For example, you need to find a person's address, or in the price list - the price of the goods. If such tasks are found - this post is for you!

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

Search in the exile table, the functions of the PRD and GRP

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

The syntax of the PRD function is: \u003d VD ( Desirable_dation; TABLE_DEL_POOK; number_store_stolbets; [type type]). Consider the arguments:

• Secondary value - The value we will look for. This is a mandatory argument;
• Table for search - That array of cells in which there will be a search. The column with the desired values \u200b\u200bshould be the first in this array. This is also a mandatory argument;
• The number of the output column - The sequence number of the column (starting from the first in the array), from which the function will output the data when the desired values \u200b\u200bcoincide. Mandatory argument;
• Type of comparison - Select "1" (or "truth") for non-strict coincidence, "0" ("Lies") - for the complete coincidence. The argument is optional if it is missed - the search will be performed. non-strict coincidence.

Search for accurate coincidence using VDP

Let's look at the example, how the PRD function works when the type of "FALS" comparison is selected, search for accurate coincidence. In the class B5: E10, the fixed assets of a certain company are indicated, their book value, inventory number and location. The B2 cell indicates the name for which it is necessary to find the inventory number in the table and place it in the C2 cell.

PRD Function in Excel

We write down the formula: \u003d VD (B2; B5: E10; 3; lies).

Here, the first argument indicates that the table needs to look for a value from the B2 cell, i.e. The word "fax". The second argument says that the search table is in the range of B5: E10, and to look for the word "fax" in the first column, i.e. In array B5: B10. The third argument informs 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 coincidence is required.

And so, the function will receive a "Fax" line from the B2 cell and will search for it in the class B5: B10 from top to bottom. Once the coincidence is found (Line 8), the function will return the corresponding value from the column D, i.e. Content D8. That is what we needed, the task was solved.

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

Search inaccurate coincidence using

Thanks to this option in uPR workWe can avoid complex formulas to find the desired result.

In the Q5: C12 array, interest rates are indicated depending on the loan amount. In the B2 cell, we specify the amount of the loan and want to get a bet in C2 for such a transaction. The task is complicated by the fact that the amount may be any and is unlikely to coincide with the specified in the array, the search for exact coincidence does not fit:

Then we write a formula of non-stroke search: \u003d VD (B2; B5: C12; 2; truth). Now, from all those represented in the column in the data, the program will look for the nearest less. That is, for the amount of 8,000, the value 5000 will be selected and the corresponding percentage has been displayed.

Unstained search for an EPR in Excel

For correct operation, the function needs to be sorted by the first column of the table ascending. Otherwise, it can give an erroneous result.

The GRP function has the same syntax as an ARP, but searches the result not in columns, but in lines. That is, it looks at the tables from top to bottom, but from left to right and displays a specified line number, not a column.

Search for data using the View feature

The View Function works similarly to the UPR, but has another syntax. I use it when the data table contains several dozen columns and to use an ART, you need to additionally calculate the number of the output speaker. In such cases, the View feature facilitates the task. And so, syntax: \u003d View ( Desirable_dation; Massive_DL_POOG; Massive_Dela_Motion) :

• Secondary value - data or reference to the data you want to search;
• Array for search - One line or column in which we are looking for a similar value. This array We definitely we sort ascending;
• An array for display- Range containing data to derive results. Naturally, it should one size with a search array.

With such a record, you give not a relative reference array of results. And directly indicate it, i.e. No need to pre-calculate the number of the output column. We use the View Function in the first example for the PRD function (fixed assets, inventory numbers): \u003d View (B2; B5: B10; D5: D10). The task is successfully solved!

Function "View" in Microsoft Excel

Search by relative coordinates. Functions Search and Index

Another data search method is to combine the functions of the search and index.

The first of them serves to search for values \u200b\u200bin the array and obtaining its sequence number: search board ( Desirable_dation; Looking through_Massive; [Type of comparison] ). Arguments Functions:

• Secondary value - Mandatory argument
• Looking through array - One line or column in which we are looking for a coincidence. Mandatory argument
• Type of comparison - Specify "0" to search for accurate coincidence, "1" - the closest smaller, "-1" is the nearest more. Since the function searches from the beginning of the list to the end, when searching for the nearest smaller - sort the search column descending. And when searching more - sort it up ascending.

The position of the required value is found, you can now display it on the screen using the function. INDEX( Array; Server number; [Number_stolbits]) :

• Array - the argument indicates which array of cells need to choose a value
• Row number- Specify the sequence number of the line (starting from the first cell of the array) to be output. Here you can write the value manually, or use the result of calculating another function. For example, the search board.
• Column number- Optional argument is indicated if the array consists of several columns. If the argument is missed, the formula uses the first column of the table.

Now I combine these functions to get the result:

Functions of the search and index in Excel

These are the ways to search and dismiss the data screen exist in Excel. Next, you can use them in the calculations, use in the presentation, perform operation with them, indicate as arguments of other functions, etc.

Feel how do you grow and fasten your knowledge and skills? Then do not stop, keep reading! In the next post we will consider: it will be difficult and interesting!

Good afternoon friends!

The theme of this article will be another opportunity to work with the text, it is knowledge of how it works. I think that you should not explain what the usefulness of this function can be expressed, it is already clear simply from the name and means that this function will look for the exact match of the specified text in the specified text string, and return the starting line of the found line.

But I can immediately say that in its pure form, the found function looks very unsightly and in principle practically not used. The most common opportunity is used only in combined form, to connect with such functions as Levsimv, Pratseim and . Such combinations will allow you to effectively use all the features of the function to find and perform more complex tasks in comparison with simple use.

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

\u003d Find (looking for; text that is visible; [starting position])where

I draw your attention that a feature that has, it is hypersensitivity to the case of letters, that is, you need to specify properly and lowercase beams.

For example, consider 2 examples for comparison:

\u003d Find ("Water"; "Water Water")we will return the value of 1, but the formula

\u003d Find ("Water"; "Water Water")will return position 6.

As can be seen from the example, this is a very significant difference that it would be ignored.

In the case when the text you want to find will not be detected, the formula will return. So, in fact, let's consider the variability in the examples, as used in simple and complex examples:

Simple examples:

Consider the application of only one function to find, in several variations and versions. Take, for example, the text value "Nagaev Artem" and apply the function:

\u003d Find ("A"; A2), in this formula we are looking for the position of the first letter "A" in the cell A2, and we obtain the result "8";

\u003d Find ("A"; A2), in this formula we are looking for the position of the first letter "A" in the cell A2, and we obtain the result "2";

\u003d Find ("A"; A2; 3)In this formula, we are looking for the position of the first letter "A" in the A2 cell, but the search starts from the third mark and get the result "4".
Combined examples:

The first example There will be a combination using the PSTR function. For example, we have the name of products with the Nomenclature number "LED №456217-485" and we need to separate the grains from the splava, that is, the name of the room from the room and this requires a formula:

\u003d PST (A4; 1; Find («№»; A4; 1) -1)In the formula we find the position "№" and everything that is drawn up to it by the corresponding formula, and we get the result "LED". The argument "-1" will take into account the fact that the position that the text is occupied, which we are looking for, is also part of the counting range and is not subject to return.
The second example I use combinations with functions Levsimv, Pratseim. For example, in the A5 cell, we introduced the name and surname "Nagaev Artem", in this case, using formulas:

=Levsimv (A5; Find (symbol (32); A5) -1), we will be able to extract the surname,

\u003d Pracemir (A5; DLSTR (A5) -Night (symbol (32); A5)), I remove the name.
In the case when there will be unnecessary gaps between the two names and surnames, two and more, then to function the above formulas it is necessary to use fUNCTION SLCROBELY.

On this I think that another textual function, you were able to appreciate. I learned, or at least figured out how and where is used. We studied her utility and applications. More functions are presented for familiarization with.

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

See you in new articles!

"We always have money on the whim, we will only miss the costs useful and necessary.
"
O. Balzac