Excel table search. The index and position search functions in excel are the best alternative for cp. We use the sumproduct function

Batyanov Denis As a Guest Contributor, he explains in this post how to find data in one Excel spreadsheet and extract it into another, and also reveals all the secrets of the vertical viewing function.

Using the COLUMN function to specify the column to extract

If the table into which you are retrieving data using VLOOKUP has the same structure as the lookup table, but simply contains fewer rows, then in VLOOKUP you can use the COLUMN () function to automatically calculate the numbers of the columns to be retrieved. In this case, all VLOOKUP formulas will be the same (adjusted for the first parameter, which changes automatically)! Note that the first parameter has an absolute column coordinate.

Creating a composite key with & "|" &

If it becomes necessary to search by several columns at the same time, then it is necessary to make a composite key for the search. If the return value were not textual (as is the case with the "Code" field), but numeric, then the more convenient SUMIFS formula would be suitable for this and the composite column key would not be required at all.

This is my first article for Lifehacker. If you liked it, I invite you to visit my site, and also gladly read in the comments about your secrets of using the VLOOKUP function and the like. Thanks. :)

Microsoft Excel is often used with large amounts of information. It creates huge tables with thousands of rows, columns and positions. It can be difficult to find any specific data in such an array. And sometimes it’s not possible at all. This task can be simplified. Find out how to find the right word in Excel. This will make it easier for you to navigate the document. And you can quickly jump to the information you are looking for.

To display the addresses of all cells that contain what you are looking for, do the following:

If you have Office 2010, go to Menu - Edit - Find.
A window with an input field will open. Write a search phrase in it.
In Excel 2007, this button is on the Home menu in the Editing panel. She is on the right.
The same result can be achieved in all versions by pressing Ctrl + F.
In the field, type the word, phrase or numbers that you want to find.
Click Find All to start searching the entire document. If you click "Next", the program will select cells that are below the Excel cell cursor one by one.
Wait for the process to finish. The larger the document, the longer the system will search.
A list will appear with the results: the names and addresses of the cells that match the given phrase, and the text that is written in them.
When you click on each line, the corresponding cell will be highlighted.
For convenience, you can "stretch" the window. This will show more lines in it.
To sort the data, click on the column names above the search results. If you click on "Sheet", the lines will be arranged alphabetically depending on the name of the sheet, if you select "Values" - they will stand by value.
These columns are also "stretched".

You can set your own conditions. For example, start a search with multiple characters. Here's how to find a word in Excel that you don't remember entirely:

Enter only part of the label. You can have at least one letter - all the places in which it is present will be highlighted.
Use the * (asterisk) and? (question mark). They replace missing characters.
The question denotes one missing item. If you write, for example, "P ???", cells will be displayed that contain a four-character word starting with "P": "Plow", "Field", "Pair" and so on.
A star (*) stands for any number of characters. To find all values that contain the root "rast", start your search using the key "* rast *".

You can also go to the settings:

In the Find window, click Options.
In the "Browse" and "Search Scope" sections, indicate where and by what criteria to search for matches. You can select formulas, notes, or values.
To make the system distinguish between lowercase and uppercase letters, check the "Match case" checkbox.
If you check the box "Whole cell", the results will show cells containing only the specified search phrase and nothing else.

Cell format options

To find values with a specific fill or style, use the settings. Here's how to find a word in Excel if it looks different from the rest of the text:

In the search box, click "Options" and click on the "Format" button. A menu with several tabs will open.
You can specify a specific font, frame type, background color, data format. The system will look at places that match the given criteria.
To take information from the current cell (selected at this moment), click "Use the format of this cell". Then the program will find all values that have the same size and type of characters, the same color, the same borders, and the like.

Search multiple words

In Excel, you can find cells by whole phrases. But if you entered the "Blue Ball" key, the system will work exactly for this request. Results will not show values with "Blue Crystal Ball" or "Blue Shiny Ball".

To find not one word in Excel, but several at once, do the following:

Write them in the search bar.
Place stars between them. It will turn out "* Text * * Text2 * * Text3 *". This will search for all values containing the specified labels. Regardless of whether there are any characters between them or not.
In this way, you can set a key even with separate letters.

Filter

Here's how to search Excel using a filter:

Select some filled cell.
Click Home - Sort - Filter.
Arrows will appear next to the cells in the top line. This is a dropdown menu. Open it up.
Enter your query in the text box and click OK.
Only cells containing the search phrase will be displayed in the column.
To reset the results, check "Select all" in the drop-down list.
To turn off a filter, click on it again in the sort.

This method will not work if you do not know in which row the value you need is in.

To find a phrase or number in Excel, use the built-in interface capabilities. You can select additional search options and enable a filter.

Good afternoon, dear residents!

From time to time, some (and maybe more than some) of us have to deal with the tasks of processing small amounts of data, from drawing up and analyzing a home budget and ending with any calculations for work, school, etc. Perhaps the most suitable tool for this is Microsoft Excel (or perhaps its other analogs, but they are less common).

The search gave me only one article on Habré on a similar topic - "Talmud by formulas in Google SpreadSheet". It gives a good description of the basic things for working in excel (although it is not 100% about excel itself).

Thus, having accumulated a certain pool of requests / tasks, the idea appeared to type them and propose possible solutions (albeit not all possible ones, but quickly giving results).

It will focus on solving the most common tasks faced by users.

The description of the solutions is structured as follows - a case is given containing the initial task, which is gradually becoming more complicated, a detailed solution with explanations is given for each step. The names of the functions will be given in Russian, but the original name in English will be given in brackets at the first mention (since, according to experience, the overwhelming majority of users have the Russian version installed).

Case_1: Logical and Matching Functions
"I have a set of values in the plate and it is necessary that when a certain condition / set of conditions are met, a certain value is displayed" (c) User

Data is usually presented in tabular form:

Condition:

if the value in the "Quantity" column is greater than 5,
then you need to display in the column "Result" the value "Order is not required",

The formula "IF" (IF) will help us with this, which refers to logical formulas and can give out any values in the solution that we write down in the formula in advance. Please note that any text values are written using quotes.

The formula syntax is as follows:
IF (logical_test, [value_if_true], [value_if_false])

Log_expression is an expression that results in TRUE or FALSE.
Value_if_true - the value that is output if the boolean expression is true
Value_if_false - the value that is displayed if the boolean expression is false

Solution formula syntax:

= IF (C5> 5; "No order required"; "Order required")

At the output, we get the result:

It happens that the condition is more complex, for example, the fulfillment of 2 or more conditions:

if the value in the "Quantity" column is greater than 5, and the value in the "Type" column is "A"

In this case, we can no longer limit ourselves to using only one formula "IF", it is necessary to add another formula to its syntax. And it will be another logical AND formula.
The formula syntax is as follows:
AND (boolean1, [boolean2], ...)

Boolean1-2, etc. - a checked condition, the calculation of which gives the value TRUE or FALSE

Outputting the result to cell D2:

= IF (AND (C2> 5; B2 = "A"); 1; 0)

Thus, using a combination of 2 formulas, we find a solution to our problem and get the result:

Let's try to complicate the task - a new condition:

if the value in the "Quantity" column is 10, and the value in the "Type" column is "A"
or the value in the "Quantity" column is greater than or equal to 5, and the "Type" value is "B"
then you need to display in the column "Result" the value "1", otherwise "0".

The solution syntax will be as follows:
Outputting the result to cell D2:

= IF (OR (AND (C2 = 10; B2 = "A"); AND (C2> = 5; B2 = "B")); 1; 0)

As you can see from the entry, the IF formula includes one OR condition and two conditions using the AND formula included in it. If at least one of the conditions of the 2nd level has the value "TRUE", then the result "1" will be displayed in the "Result" column, otherwise it will be "0".
Result:

Now let's move on to the next situation:
Imagine that, depending on the value in the "Condition" column, a certain condition should be displayed in the "Result" column, below is the correspondence between the values and the result.
Condition:

1 = A
2 = B
3 = B
4 = G

When solving a problem using the "IF" function, the syntax will be as follows:

= IF (A2 = 1; "A"; IF (A2 = 2; "B"; IF (A2 = 3; "C"; IF (A2 = 4; "D"; 0))))

Result:

As you can see, writing such a formula is not only inconvenient and cumbersome, but it can take some time for an inexperienced user to edit it in case of an error.
The disadvantage of this approach is that it is applicable for a small number of conditions, because all of them will have to be typed manually and "inflated" our formula to large sizes, but the approach is distinguished by complete "omnivorous" values and universality of use.

Alternative solution_1:
Using the CHOOSE formula,
Function syntax:
CHOICE (index_number, value1, [value2], ...)

Index_num is the number of the value argument to select. The index number must be a number between 1 and 254, a formula, or a cell reference containing a number in the range 1 through 254.
Value1, value2, ... - a value from 1 to 254 value arguments from which the SELECT function, using the index number, selects a value or an action to be performed. Arguments can be numbers, cell references, specific names, formulas, functions, or text.

When using it, we immediately enter the results of the conditions depending on the specified values.
Condition:

1 = A
2 = B
3 = B
4 = G

Formula syntax:

= CHOICE (A2; "A"; "B"; "C"; "D")

The result is the same as the IF chaining solution above.
The following restrictions apply when applying this formula:
Only numbers can be specified in cell "A2" (index number), and the result values will be displayed in ascending order from 1 to 254 values.
In other words, the function will work only if the numbers from 1 to 254 in ascending order are specified in cell "A2" and this imposes certain restrictions when using this formula.
Those. if we want the value of "Г" to be displayed when specifying the number 5,

1 = A
2 = B
3 = B
5 = G

then the formula will have the following syntax:
Outputting the result to cell B2:

= CHOICE (A31; "A"; "B"; "C" ;; "D")

As you can see, we have to leave the value "4" in the formula empty and transfer the result "G" to the ordinal number "5".

Alternative solution_2:
Here we come to one of the most popular Excel functions, mastering which automatically turns any office worker into an "experienced excel user" / sarcasm /.
Formula syntax:
VLOOKUP (lookup_value, table, column_number, [range_view])

Lookup_value is the value searched for by the function.
Table is a range of cells containing data. It is in these cells that the search will take place. Values can be text, numeric, or logical.
Column_number is the number of the column in the "Table" argument from which the value will be output in case of a match. It is important to understand that the columns are not counted along the general grid of the sheet (A.B, C, D, etc.), but within the array specified in the "Table" argument.
Range_lookup - Determines whether the function should find an exact match or an approximate match.

Important: the "VLOOKUP" function searches for a match only by the first unique record, if the desired_value is present in the "Table" argument several times and has different values, then the "VLOOKUP" function will only find the FIRST match, the results for all other matches will not be shown Using the "VLOOKUP" formula (VLOOKUP) is associated with another approach to working with data, namely the formation of "reference books".
The essence of the approach is to create a "reference book" for matching the "Seek_value" argument to a certain result, separately from the main array, in which the conditions and their corresponding values are written:

Then, in the working part of the table, a formula is already written with a link to the reference book, filled out earlier. Those. in the reference book in the column "D" the value is searched for from the column "A" and, when a match is found, the value from the column "E" is displayed in the column "B".
Formula syntax:
Outputting the result to cell B2:

Result:

Now let's imagine a situation when it is necessary to pull data into one table from another, while the tables are not identical. See example below

It can be seen that the rows in the "Product" columns of both tables do not match, however, this is not an obstacle to using the "VLOOKUP" function.
Outputting the result to cell B2:

But when solving, we are faced with a new problem - when "stretching" the formula we have written to the right from column "B" to column "E", we will have to manually replace the argument "column_number". This is a laborious and thankless task, therefore, another function comes to our aid - "COLUMN" (COLUMN).
Function syntax:
COLUMN ([link])

Reference is the cell or range of cells for which you want to return the column number.

If you use a record like:

then the function will display the number of the current column (in the cell of which the formula is written).
The result is a number that can be used in the "VLOOKUP" function, which we will use and get the following formula entry:
Outputting the result to cell B2:

= VLOOKUP ($ A3; $ H $ 3: $ M $ 6; COLUMN (); 0)

The COLUMN function will determine the number of the current column, which will be used by the Column_num argument to determine the number of the lookup column in the lookup.
In addition, you can use the construction:

Instead of the number "1", you can use any number (and also not only subtract it, but also add it to the resulting value) to get the desired result, if you do not want to refer to a specific cell in the column with the number we need.
The resulting result is:

We continue to develop the topic and complicate the condition: imagine that we have two directories with different data on products and it is necessary to display the values in the table with the result, depending on what type of directory is indicated in the "Directory" column
Condition:

If the number 1 is indicated in the "Directory" column, the data should be pulled from the "Directory_1" table, if the number is 2, then from the "Directory_2" table in accordance with the specified month

The solution that immediately comes to mind is the following:

= IF ($ B3 = 1; VLOOKUP ($ A3; $ G $ 3: $ I $ 6; COLUMN () - 1; 0); VLOOKUP ($ A3; $ K $ 3: $ M $ 6; COLUMN () - 1; 0 ))

pros: the name of the directory can be anything (text, numbers and their combination), cons - poorly suited if there are more than 3 options.
If directory numbers are always numbers, it makes sense to use the following solution:
Outputting the result to cell C3:

= VLOOKUP ($ A3; CHOICE ($ B3; $ G $ 3: $ I $ 6; $ K $ 3: $ M $ 6); COLUMN () - 1; 0)

pros: the formula can include up to 254 names of reference books, cons - their name must be strictly numeric.
The result for the formula using the SELECT function:

Bonus: VLOOKUP for two or more features in the "lookup_value" argument.
Condition:

Imagine that we, as always, have an array of data in tabular form (if not, then we bring data to it), from the array, according to certain criteria, it is necessary to obtain values and place them in another tabular form.

Both tables are shown below:

As can be seen from the tabular forms, each position has not only a name (which is not unique), but also belongs to a certain class and has its own packaging option.
Using a combination of name and class and packing, we can create a new attribute, for this we create an additional column "Additional attribute" in the table with data, which we fill in using the following formula:

Using the "&" symbol, we combine three signs into one (the separator between words can be any, as well as not be at all, the main thing is to use a similar rule for searching)
The analogue of the formula can be the CONCATENATE function, in this case it will look like this:

= CONCATENATE (H3; "_"; I3; "_"; J3)

After an additional feature is created for each record in the table with data, we proceed to writing a search function for this feature, which will look like:
Outputting the result to cell D3:

= IFERROR (VLOOKUP (A2 & "_" & B2 & "_" $ G $ 2: $ K $ 6; 5; 0); 0)

In the function "VLOOKUP" as the argument "sought_value" we use the same bunch of three attributes (name_class_packaging), but we take it already in the table for filling and enter it directly into the argument (as an option, one could select the value for the argument in an additional column in the table to fill, but this action would be overkill).
Let me remind you that the use of the IFERROR function is necessary if the desired value is still not found, and the VLOOKUP function will display the value "# N / A" (more on that below).
The result is in the picture below:

This technique can be used for a larger number of features, the only condition is the uniqueness of the resulting combinations, if it is not observed, then the result will be incorrect.

Case_3 Finding a value in an array, or when VLOOKUP is unable to help us

Consider a situation when it is necessary to understand whether the cell array contains the values we need.
Task:

a value is indicated in the column "Search condition" and it is necessary to determine whether it is present in the column "Array for search"

Visually, everything looks like this:

As we can see, the VLOOKUP function is powerless here, since we are looking not for an exact match, but for the presence in the cell of the value we need.
To solve the problem, you must use a combination of several functions, namely:
"IF"
"ESLIOSHIBKA"
"LINE"
"FIND"

In order about all, "IF" we have already sorted out earlier, so let's move on to the function "IFERROR"

IFERROR (value, value_on_error)

Value is the argument to be checked for errors.
Error_value is the value returned on error when evaluating a formula. The following types of errors are possible: # N / A, #VALUE !, #REF !, # DIV / 0 !, #NUM !, #NAME? and # EMPTY !.

Important: this formula is almost always required when working with arrays of information and reference books, since it often happens that the required value is not in the lookup, and in this case the function returns an error. If an error is displayed in a cell and the cell is involved, for example, in a calculation, then it will also fail with an error. In addition, the cells where the formula returned an error can be assigned different values, which make it easier to aggregate them. Also, in case of an error, you can perform other functions, which is very convenient when working with arrays and allows you to build formulas taking into account rather branched conditions.

LOWER

Text - text converted to lowercase.

Important: the LOWER function does not replace non-letter characters.
Role in the formula: since the FIND function searches and case-sensitive text, it is necessary to bring the entire text to one case, otherwise “tea” will not equal “tea” and so on. This is relevant if the register value is not a condition for the search and selection of values, otherwise the "LOWER" formula may not be used, so the search will be more accurate.

Now more about the syntax of the FIND function.

FIND (search_text, crawled_text, [start_position])

Search_text is the text to find.
Searchable_text is the text in which you want to find the text you are looking for.
Start_position is the character from which to start the search. The first character in the text "viewed_text" is numbered 1. If no number is specified, it defaults to 1.

The syntax of the solution formula will be:
Outputting the result to cell B2:

= IF (IFERROR (FIND (LOWER (A2); LOWER (E2); 1); 0) = 0; "fail"; "bingo!")

Let's analyze the logic of the formula by actions:

LOWER (A2) - Converts the "Lookup_text" argument in a cell in A2 to lowercase text
The FIND function begins to search for the transformed argument "Lookup_text" in the array "Viewed_text", which is converted by the "LOWER (E2)" function, also to lowercase text.
If, the function finds a match, i.e. returns the ordinal number of the first character of the matched word / value, the TRUE condition in the "IF" formula is triggered, since the resulting value is not zero. As a result, the "Result" column will display the value "Bingo!"
If, however, the function does not find a match i.e. the ordinal number of the first character of the matching word / value is not specified and instead of the value an error is returned, the condition in the formula "IFERROR" is triggered and the value equal to "0" is returned, which corresponds to the condition FALSE in the formula "IF", since the resulting value is "0". As a result, the value “fail” will be displayed in the “Result” column.

As you can see from the picture above, thanks to the "LOWER" and "FIND" functions, we find the desired values regardless of the case and location in the cell, but you need to pay attention to line 5.
The search term is "111", but the search array says "1111111 cookies", but the formula returns "Bingo!" This happens because the value "111" is included in the range of values "1111111", as a result, a match is found. Otherwise, this condition will not work.

Case_4 Search for a value in an array by several conditions, or when the VLOOKUP is all the more unable to help us

Imagine a situation when you need to find a value from the "Table with the result" in a two-dimensional array "Reference" by several conditions, namely, by the value "Name" and "Month".
The tabular form of the task will look like this:

Condition:

In the table with the result, it is necessary to tighten the data in accordance with the coincidence of the conditions "Name" and "Month".

To solve such a problem, a combination of the "INDEX" and "SEARCH" functions is suitable

INDEX function syntax

INDEX (array, line_num, [column_num])

Array - a range of cells from which values will be displayed if their search conditions match.
If the array contains only one row or one column, row_num or column_num is optional, respectively.
If an array occupies more than one row and one column, and only one of the "row_num" and "column_num" arguments is specified, then the INDEX function returns an array consisting of an entire row or an integer column of the "array" argument.
Line_number is the number of the line in the array from which you want to return a value.
Column_number is the number of the column in the array from which you want to return a value.

In other words, the function returns from the specified array in the "Array" argument the value that is at the intersection of the coordinates specified in the "Line_num." And "Column_number" arguments.

MATCH function syntax

MATCH (lookup_value, lookup_array, [match_type])

Lookup_value is the value that matches the values in lookup_array. Lookup_value can be a value (number, text, or boolean) or a reference to a cell containing such a value.
Lookup_array is the range of cells to search.
Collation_type is an optional argument. The number is -1, 0, or 1.

The MATCH function searches for a specified item in a range of cells and returns the relative position of that item in the range.
The essence of using the combination of the "INDEX" and "SEARCH" functions is that we search for the coordinates of the values by their name along the "coordinate axes".
The Y-axis will be the Name column, and the X-axis will be the Months row.

Part of the formula:

SEARCH ($ A4; $ I $ 4: $ I $ 7; 0)

returns the number on the Y-axis, in this case it will be equal to 1, since the value "A" is present in the desired range and has a relative position of "1" in this range.
part of the formula:

SEARCH (B $ 3; $ J $ 3: $ L $ 3; 0)

returns # N / A because the value "1" is not present in the viewing range.

Thus, we got the coordinates of the point (1; # N / A) that the "INDEX" function uses to search in the "Array" argument.
The fully written function for cell B4 will look like this:

= INDEX ($ J $ 4: $ L $ 7; SEARCH ($ A4; $ I $ 4: $ I $ 7,0); SEARCH (B $ 3; $ J $ 3: $ L $ 3,0))

In fact, if we knew the coordinates of the value we need, the function would look like this:

= INDEX ($ J $ 4: $ L $ 7; 1; # N / A))

Since Column_num is # N / A, the result for cell B4 will be appropriate.
As you can see from the result, not all the values in the table with the result match the reference, and as a result, we see that some of the values in the table are displayed as "# N / A", which makes it difficult to use the data for further calculations.
Result:

To neutralize this negative effect, we use the "IFERROR" function, which we read about earlier, and replace the value returned in case of an error with "0", then the formula will look like:

= IFERROR (INDEX ($ J $ 4: $ L $ 7; SEARCH ($ A4; $ I $ 4: $ I $ 7,0); SEARCH (B $ 3; $ J $ 3: $ L $ 3,0)); 0)

Demonstration of the result:

As you can see in the picture, the "# N / A" values no longer interfere with our subsequent calculations using the values in the table with the result.

Case_5 Search for a value in a range of numbers

Imagine that we need to give a certain sign to numbers that are in a certain range.
Condition:
Depending on the value of the product, a certain category should be assigned to it.
If the value is in the range

0 to 1000 = A
1001 to 1500 = B
1501 to 2000 = V
2001 to 2500 = Y
More than 2501 = D

The LOOKUP function returns a value from a row, column, or array. The function has two syntactic forms: vector and array.

LOOKUP (lookup_value, lookup_vector, [result_vector])

Lookup_value is the value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, boolean, name, or value reference.
Lookup_vector is a range of one row or one column. The values in lookup_vector can be text, numbers, or Boolean values.
The values in lookup_vector must be in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may return an incorrect result. Lowercase and uppercase text are considered equivalent.
Result_Vector is a single row or column range. The result_vector must be the same size as the view_vector.

= LOOKUP (E3, $ A $ 3: $ A $ 7, $ B $ 3: $ B $ 7)

The arguments "View_Vector" and "Result_Vector" can be written in the form of an array - in this case, you do not have to display them in a separate table on the Excel sheet.
In this case, the function will look like this:
Outputting the result to cell B3:
= LOOKUP (E3; (0; 1001; 1501; 2001; 2501); ("A"; "B"; "C"; "D"; "D"))

Case_6 Summation of numbers by attributes

Three different functions can be used to sum numbers based on certain criteria:
SUMIF - sums up only one attribute
SUMIFS - sums over multiple features
SUMPRODUCT - sums over multiple features
There is also a variant using SUM and an array formula function, where the SUM formula is raised to an array:
((= SUM (() * ()))
but this approach is rather inconvenient and completely overlaps in functionality by the formula "SUMPRODUCT"
Now, in more detail on the syntax "SUMPRODUCT":

SUMPRODUCT (array1, [array2], [array3], ...)

Array1 is the first array whose components you want to multiply and then add the results.
Array2, array3 ... - from 2 to 255 arrays, the components of which must be multiplied and then added together.

Condition:

Find the total amount for the value of shipments for each of the products for a certain period:

As you can see from the data table, in order to calculate the cost, the price must be multiplied by the quantity, and the resulting value, by applying the selection conditions, should be transferred to the table with the result.
However, the "SUMPROIZ" formula allows such calculations to be carried out within the formula.
Outputting the result to cell B4:

= SUMPRODUCT (($ A4 = $ H $ 3: $ H $ 11) * ($ K $ 3: $ K $ 11> = B $ 3) * ($ K $ 3: $ K $ 11
Let's analyze the formula piece by piece:
- set the selection condition in the "Name" column of the table with data on the "Name" column in the table with the result
($ K $ 3: $ K $ 11> = B $ 3) * ($ K $ 3: $ K $ 11 - set the condition for the time frame, the date is greater than or equal to the first day of the current month, but less than the first day of the next month. Similarly, a condition is in the table with the result, an array is in the table with data.
- we multiply the columns "Quantity" and "Price" in the table with data.
The undoubted advantage of this function is the free order of writing conditions, they can be written in any order, this will not affect the result.
Result:
Now let us complicate the condition and add the requirement that the selection by the name of the "cookie" would take place only by the classes "small" and "large", and by the name of the "roll" everything, except for the class "with jam":
Outputting the result to cell B4:
= SUMPRODUCT (($ A4 = $ H $ 3: $ H $ 11) * ($ J $ 3: $ J $ 11> = B $ 3) * ($ J $ 3: $ J $ 11
A new condition has been added to the cookie selection formula:
(($ I $ 3: $ I $ 11 = "small") + ($ I $ 3: $ I $ 11 = "large"))
- as you can see, two or more conditions in one column are highlighted in a separate group using the "+" symbol and enclosing the conditions in additional brackets.
A new condition has also been added to the selection formula for buns:
= SUMPRODUCT (($ A5 = $ H $ 3: $ H $ 11) * ($ J $ 3: $ J $ 11> = B $ 3) * ($ J $ 3: $ J $ 11 "With jam"); ($ L $ 3: $ L $ 11) * ($ K $ 3: $ K $ 11))

this is:
($ I $ 3: $ I $ 11<>"With jam")
- in fact, in this formula it was possible to write the selection condition as well as in the selection by cookies, but then, it would be necessary to list three conditions in the formula, in this case, it is easier to write an exception - not equal to "with jam" for this we use the value "<>».
In general, if groups of features / classes are known in advance, then it is better to combine them into these groups, creating reference books, than to write all conditions into a function, inflating it.
Result:
Well, here we come to the end of our short manual, which in fact could have been much larger, but the goal was still to give a solution to the most common situations, and not describe the solution of particular (but much more interesting cases).
I hope that the manual will help someone in solving problems using Excel, because this will mean that my work has not been wasted!
Thank you for your time!

The button Find and select groups Editing the ribbon also has a large list (Fig. 2.57). Searching is convenient and sometimes even necessary for large tables! Imagine, you open the price list, and there are hundreds of items. Do not flip through it all to find what interests you!

For example, in the price list of a company that sells office supplies, you can find all the pencils (Fig. 2.58).

The program will show you the first pencil it finds. Next, you will need to press the Find Next button until you have scrolled through the entire document to the end. If you click the Find All button in the Find and Replace window, a list of cells containing the required value will appear. You can also replace one name in bulk with another. For example, in one fell swoop to change all laboratory work for practical (Fig. 2.59).

If interested, click the Options button. There you will learn that you can set the format of both what you want to find and what it needs to be replaced with. You can also set the search area. Using the menu of the Find and Select button (see Fig. 2.57), you can also go to the cell you specified (very convenient for large tables), find formulas, notes, constants (constants).

Excel will carefully highlight all this and show you in a table. Pay attention to the Clear button. I want to note that this button should not be confused with the Delete button, which we have already discussed in the Cells group. By clicking it, you can "erase" either the contents of the cell or the format of the cell. Or both together. (Alternatively, you can select a cell and press the Delete key, which will also clear the contents of the cell.) If you clear the cell, it remains in the table, but becomes empty. If you delete a cell, you are dropping it out of the table.

Another unknown to us button in the Editing group is. I think you know that, according to the laws of serials, you need to interrupt each episode at the most interesting place, so that tomorrow everyone rushes to the TV in a single impulse, waiting for the secret to be revealed! So, to be continued. The purpose of the mysterious button will be revealed in the next episode!
21.10.2012
This tutorial talks about the main benefits of the features INDEX and SEARCH in Excel that 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 VLOOKUP powerless.
In several recent articles, we have made every effort to educate novice users on the basics of the function. VLOOKUP and show examples of more complex formulas for advanced users. Now we will try, if not dissuade you from using VLOOKUP, then at least show alternative ways to implement vertical search in Excel.

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

Basic information about INDEX and SEARCH

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

We will give here the necessary minimum to understand the essence, and then we will analyze in detail examples of formulas that show the advantages of using INDEX and SEARCH instead of VLOOKUP.

INDEX - syntax and application of the function

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

Each argument has a very simple explanation:

array(array) is the range of cells from which to extract the value.

row_num(line_number) is the number of the line in the array from which 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 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 is the simplest example of a function INDEX(INDEX):

INDEX (A1: C10,2,3)
= INDEX (A1: C10; 2; 3)
The formula searches the range A1: C10 and returns the value of the cell 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 - syntax and function application

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 values New-York, Paris, London, then the following formula will return a digit 3 because “London” is the third item in the list.

MATCH ("London", B1: B3,0)
= SEARCH ("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(lookup_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(lookup_array) - the range of cells in which to search.

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 scanned must be in ascending order, that is, from smallest to largest.

0 - finds the first value equal to the desired one. For combination INDEX/SEARCH an exact match is always needed, so the third argument of the function SEARCH should be equal 0 .

-1 - Finds the smallest value greater than or equal to the desired value. The array being scanned must be in descending order, that is, from largest to smallest.

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

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

How to use INDEX and SEARCH 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 desired value in a given range of cells, and INDEX uses that number (or numbers) and returns the result from the corresponding cell.

Not quite clear yet? Introduce functions INDEX and SEARCH in this form:

INDEX (, (MATCH ( desired value,the column in which we are looking,0))
= INDEX ( the column from which to extract; (SEARCH ( desired 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; SEARCH ("Japan"; $ B $ 2: $ B $ 10; 0))
Now let's take a look at what each element of this formula does:

Function MATCH(MATCH) searches for "Japan" in a column B, specifically - in cells B2: B10, and returns the number 3 because "Japan" is in third place on the list.

Function INDEX(INDEX) uses 3 for argument row_num(line_number), which specifies which line to return the value from. Those. a simple formula is obtained:
INDEX ($ D $ 2: $ D $ 10.3)
= INDEX ($ D $ 2: $ D $ 10,3)
The formula says something like this: search in cells from D2 before D10 and extract the value from the third row, that is, from the cell D4 since the counting starts from the second line.

Here's the result in Excel:

Important! The number of rows and columns in the array that the function uses INDEX(INDEX), must match the values of the arguments row_num(line_number) and column_num(column_number) function MATCH(SEARCH). Otherwise, the result of the formula will be erroneous.
Stop, stop ... why can't we just use the function VLOOKUP(VLOOKUP)? Is there any point in wasting time trying to figure out the mazes SEARCH and INDEX?

VLOOKUP ("Japan", $ B $ 2: $ D $ 2.3)
= VLOOKUP ("Japan"; $ B $ 2: $ D $ 2; 3)
In this case, it makes no sense! The purpose of this example is for demonstration purposes only, 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 that easily handles many difficult situations where VLOOKUP is stumped.

Why is INDEX / SEARCH better than VLOOKUP?

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

4 main advantages of using SEARCH / INDEX in Excel:

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

2. Safely adding or removing columns. Formulas with function VLOOKUP stop working or return erroneous values if you delete or add a column to the lookup table. For function VLOOKUP any inserted or deleted column will change the result of the formula, since 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 extract data from the column B, then you need to set the value 2 for argument col_index_num(column_number) function 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 column you just inserted.

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

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

Suppose you use this formula with VLOOKUP looking 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 is in the cell 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; SEARCH (TRUE; INDEX (B5: B10 = A2; 0); 0))
4. Higher speed of work. If you work with small spreadsheets, the difference in Excel performance will most likely be subtle, especially in recent versions. If you work with large tables that contain thousands of rows and hundreds of search formulas, Excel will work much faster when using SEARCH and INDEX instead of VLOOKUP... In general, such a replacement increases the speed of Excel by 13% .

Influence VLOOKUP Excel performance is especially noticeable if the workbook contains hundreds of complex array formulas such as VLOOKUP + SUM... The fact is that checking each value in the array requires a separate function call. VLOOKUP... Therefore, the more values the 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 just searches and returns the result, doing the same job noticeably faster.

INDEX and MATCH - examples of formulas

Now that you understand the reasons why it is worth learning functions SEARCH and INDEX, let's move on to the most interesting and see how you can apply theoretical knowledge in practice.

How to search from the left side using SEARCH and INDEX

Any tutorial on VLOOKUP insists that this function cannot look to the left. Those. if the column being viewed is not the leftmost 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 they don't care where the column with the value to be retrieved is. For example, let's go back to the table with state capitals and population. This time, let's write the formula SEARCH/INDEX, which will show what place in terms of population is the capital of Russia (Moscow).

As you can see in the picture below, the formula does this very well:

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

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

First, we use the function MATCH(MATCH) which finds the position of “Russia” in the list:
MATCH ("Russia", $ B $ 2: $ B $ 10.0))
= SEARCH ("Russia"; $ B $ 2: $ B $ 10; 0))

Next, we set the range for the function INDEX(INDEX) from which to extract the value. In our case it is A2: A10.

Then we connect both parts and get the formula:
INDEX ($ A $ 2: $ A $ 10; MATCH ("Russia"; $ B $ 2: $ B $ 10; 0))
= INDEX ($ A $ 2: $ A $ 10; SEARCH ("Russia"; $ B $ 2: $ B $ 10; 0))

Prompt: The correct solution is to always use absolute links for INDEX and SEARCH so that the search ranges do not get lost when copying the formula to other cells.
Calculations using INDEX and SEARCH in Excel (AVERAGE, MAX, MIN)

You can nest other Excel functions in INDEX and SEARCH, for example, to find the minimum, maximum, or closest to the mean. Here are several options for the formulas applied 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; SEARCH (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; SEARCH (MIN ($ D $ 2: I $ 10); $ D $ 2: D $ 10; 0))
Result: Lima

3. AVERAGE(AVERAGE). The formula calculates the average over a range D2: D10, then finds the closest 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; SEARCH (AVERAGE ($ D $ 2: D $ 10); $ D $ 2: D $ 10; 1))
Result: Moscow

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

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 if the range you are viewing contains a value equal to the mean. If you are sure that there is such a value, put 0 to find an exact match.

If you specify 1 , the values in the search column must be sorted in ascending order, and the formula will return the maximum value less than or equal to the mean.

If you specify -1 , the values in the search column must be sorted in descending order, and the minimum value that is greater than or equal to the mean is returned.

In our example, the values in the column D ordered in ascending order, so we use the matching type 1 ... Formula INDEX/SEARCHPOZ returns “Moscow” because the population of the city of Moscow is closest to the mean (12,269,006).

How to use INDEX and SEARCH to search for a known row and column

This formula is equivalent to two-dimensional search VLOOKUP and allows you to find a value at the intersection of a specific row and column.
In this example, the formula INDEX/SEARCH will be very similar to the formulas we have already discussed in this tutorial, with only one difference. Guess which one?

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

INDEX (array, row_num,)
INDEX (array; line_num; [column_num])
And I congratulate those of you who guessed it!

Let's start by writing a formula template. For this we take the already familiar formula INDEX/SEARCH and add one more function to it SEARCH which will return the column number.

INDEX (Your table, (MATCH (, the column to search in, 0)), (MATCH (, string in which to search,0))
= INDEX (Your table, (MATCH ( value for vertical search,the column to search in, 0)), (MATCH ( value for horizontal search,string in which to search,0))
Note that for a two-dimensional search, you need to specify the entire table in the argument array(array) functions INDEX(INDEX).
Now let's put this pattern to the test. Below you can see a list of the most populated countries in the world. Suppose our task is to find out the US population 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 each nested one separately.

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

SEARCH for a 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)
= SEARCH ($ H $ 2; $ B $ 1: $ B $ 11; 0)
4 as “USA” is the 4th list item 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)
= SEARCH ($ H $ 3; $ A $ 1: $ E $ 1; 0)

The result of this formula will be 5 because “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; SEARCH ($ H $ 2; $ B $ 1: $ B $ 11; 0); SEARCH ($ H $ 3; $ A $ 1: $ E $ 1; 0))
If we replace the functions SEARCH on the values they return, the formula will become easy and straightforward:

INDEX ($ A $ 1: $ E $ 11,4,5))
= INDEX ($ A $ 1: $ E $ 11; 4; 5))
This formula returns the value at the intersection 4th strings and 5th column in range A1: E11, that is, the cell value E4... Just? Yes!

Multiple search with INDEX and SEARCH

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

Suppose we have a list of orders and we want to find the amount by 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 the buyers in the table on the sheet Lookup table are arranged in no particular order.

This is 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 the others 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 should 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 desired value is 1 and the search array is the result of the multiplication. Okay, what should we multiply and why? Let's break it down in order:

We take the first value in the column A(Customer) per sheet Main table and compare it with all 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 (LYING).

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), will you get 1 ... If both criteria are false, or only one of them is met, you will receive 0 .

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

Note: In this case, you must use the third optional argument to the function. INDEX... It is necessary because in the first argument, we set the entire table and must tell the function which column to extract the value from. In our case, this is the column C(Sum) and so we entered 3 .
And finally, since we need to check every cell in the array, this formula should be an array formula. You can see this by the curly braces that enclose it. Therefore, when you are finished entering the formula, do not forget to press Ctrl + Shift + Enter.

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

INDEX and SEARCH in combination with IFERROR in Excel

As you probably already noticed (and more than once), if you enter an incorrect value, for example, which is not in the viewed array, 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 the formula with INDEX and SEARCH into 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 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 return if the formula throws an error.

For example, you can insert into the 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. Please try again!") = IFERROR (INDEX ($ A $ 1: $ E $ 11; SEARCH ($ G $ 2; $ B $ 1: $ B $ 11; 0); SEARCH ($ G $ 3; $ A $ 1 : $ E $ 1; 0));
"No matches found. Please try again!")
And now, if someone enters a wrong value, the formula will return this result:

If you prefer to leave the cell empty in case of an error, you can use quotation marks (“”) 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; SEARCH (lookup_value; lookup_array; 0), "")
Hope you found at least one formula in this tutorial helpful. If you have come across 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.

Categories

Popular