Hello friends. How often do you have to search for a match in an Excel table for a given value? For example, you need to find the address of a person in a directory, or in the price list - the price of an item. If such tasks are encountered - this post is for you!

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

Excel table lookup, VLOOKUP and HLOOKUP functions

The role of these functions in the life of an ordinary user can hardly be overestimated. Now you can easily find a suitable record in the data table and return the corresponding value.

The syntax for the VLOOKUP function is: \u003d VLOOKUP ( Lookup_value; table_for_search; column_number to display; [match_type]). Consider the arguments:

The sought value - the value to be looking for. This is a required argument;
Search table - the array of cells in which the search will be. The column with the desired values \u200b\u200bmust be the first in this array. This is also a required argument;
Displayed column number - the ordinal number of the column (starting from the first in the array), from which the function will output data if the required values \u200b\u200bmatch. Required argument;
Match type - select "1" (or "TRUE") for a non-strict match, "0" ("FALSE") for a complete match. The argument is optional, if you omit it, the search will be performed loose match.

Finding an exact match using VLOOKUP

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

VLOOKUP function in Excel

Let's write the formula: \u003d VLOOKUP (B2; B5: E10; 3; FALSE).

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

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

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

Finding a fuzzy match using VLOOKUP

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

Array B5: C12 shows interest rates on loans depending on the loan amount. In cell B2, we indicate the amount of the loan and we want to get the rate in C2 for such a transaction. The task is complicated by the fact that the sum can be any and is unlikely to coincide with those specified in the array; search by exact match is not suitable:

Then we write down the formula for a loose search: \u003d VLOOKUP (B2, B5: C12, 2, TRUE)... Now the program will search for the nearest smaller of all data presented in the B column. That is, for the sum of 8000, the value 5000 will be selected and the corresponding percentage is displayed.

VLOOKUP search in Excel

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

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

Finding data using the BROWSE function

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

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

With this notation, you are giving a non-relative reference to the result array. And you point directly to it, i.e. there is no need to pre-calculate the number of the output column. We use the LOOKUP function in the first example for the VLOOKUP function (fixed assets, inventory numbers): \u003d LOOKUP (B2, B5: B10, D5: D10)... The problem was successfully solved!

"VIEW" function in Microsoft Excel

Search by relative coordinates. SEARCH and INDEX functions

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

The first of them is used to search for a value in an array and get its ordinal number: SEARCH ( Lookup_value; Watched_array; [Match type] ). Function arguments:

The sought value - required argument
Array to look through - one row or column in which we are looking for a match. Required argument
Match type - specify "0" to search for an exact match, "1" - the nearest lower, "-1" - the closest higher. Since the function searches from the beginning of the list to the end, when searching for the closest smaller one, sort the search column in descending order. And when looking for more, sort it in ascending order.

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

Array - the argument indicates from which array of cells to select a value
Line number- specify the ordinal number of the line (starting from the first cell of the array) that you want to display. Here you can write the value manually, or use the result of calculating another function. For example, SEARCH.
Column number- an optional argument, specified if the array consists of several columns. If the argument is omitted, the formula uses the first column of the table.

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

Functions SEARCH and INDEX in Excel

These are the ways to find and display data in Excel. Further, you can use them in calculations, use in a presentation, perform operations with them, specify other functions as arguments, etc.

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

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, study, etc. Perhaps the most suitable tool for this is Microsoft Excel (or perhaps other analogues, 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's 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, 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 functions and search functions
"I have a set of values \u200b\u200bin 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 produce any values \u200b\u200bin the solution that we write down in the formula in advance. Please note that any text values \u200b\u200bare 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 displayed if the boolean expression is true
Value_if_false - the value that is displayed if the boolean expression is false

Solution formula syntax:

\u003d IF (C5\u003e 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 restrict 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:

\u003d IF (AND (C2\u003e 5; B2 \u003d "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:

\u003d IF (OR (AND (C2 \u003d 10; B2 \u003d "A"); AND (C2\u003e \u003d 5; B2 \u003d "B")); 1; 0)

As you can see from the entry, one OR condition and two conditions using the AND formula are included in the IF formula. 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 \u200b\u200band the result.
Condition:

1 \u003d A
2 \u003d B
3 \u003d B
4 \u003d G

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

\u003d IF (A2 \u003d 1; "A"; IF (A2 \u003d 2; "B"; IF (A2 \u003d 3; "C"; IF (A2 \u003d 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, however, the approach is distinguished by complete "omnivorous" values \u200b\u200band universality of use.

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

Index_number 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, ... is a value from 1 to 254 value arguments from which the SELECT function selects a value or an action to be performed using the index number. 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 \u003d A
2 \u003d B
3 \u003d B
4 \u003d G

Formula syntax:

\u003d CHOICE (A2; "A"; "B"; "C"; "D")

The result is similar to the IF function 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 \u200b\u200bwill 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 "G" to be displayed when specifying the number 5,

1 \u003d A
2 \u003d B
3 \u003d B
5 \u003d G

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

\u003d 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 that the function searches for.
Table is a range of cells that contains data. It is in these cells that the search will take place. Values \u200b\u200bcan 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 very FIRST match, the results for all other matches will not be shown. (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 \u200b\u200bare written:

Then, in the working part of the table, a formula is already written with a link to the reference book filled in 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 imagine a situation when you need 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 face a new problem - when we "stretch" the formula we have written to the right from column "B" to column "E", we will have to manually replace the "column_number" argument. This is a laborious and ungrateful business, 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:

\u003d 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:

We continue to develop the topic and complicate the condition: let's imagine that we have two directories with different data on products and it is necessary to display the values \u200b\u200bin a 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 "Reference" column, the data should be pulled from the "Reference_1" table, if the number is 2, then from the "Reference_2" table in accordance with the specified month

The solution that immediately comes to mind is the following:

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

pros: the name of the reference book 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:

\u003d 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, as always, we 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 \u200b\u200band 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, in the table with data, create an additional column "Additional attribute", which we fill in using the following formula:

Using the "&" symbol, we combine three features 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:

\u003d CONCATENATE (H3; "_"; I3; "_"; J3)

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

\u003d 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 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, 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 you need to understand whether the array of cells contains the values \u200b\u200bwe need.
A 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, because we are not looking for an exact match, but the presence of the value we need in the cell.
To solve the problem, you must use a combination of several functions, namely:
"IF"
"ESLIOSHIBKA"
"STROCHN"
"TO 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 desired 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. Plus, the cells where the formula returned an error can be assigned different values, which make it easier to aggregate them. Also, in the event 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 is case sensitive, it is necessary to bring the entire text to one case, otherwise “tea” will not equal “tea”, etc. 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.
Searched_text is the text in which to find the text you are looking for.
Start_position is the character from which to start the search. The first character in the "viewed_text" 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:

\u003d IF (IFERROR (FIND (LOWER (A2); LOWER (E2); 1); 0) \u003d 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 translated argument "Look for_text" in the array "Viewed_text", which is converted by the function "LOWER (E2)", also to lowercase text.
If, the function finds a match, i.e. returns the ordinal number of the first character of the matching 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 required values \u200b\u200bregardless of the case of characters and the 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 \u200b\u200b"1111111", as a result, a match is found. Otherwise, this condition will not work.

Case_4 Searching for a value in an array by several conditions, or when VLOOKUP is even 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 is a range of cells from which values \u200b\u200bwill 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 the array occupies more than one row and one column, and only one of the "row_num" and "column_number" 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 Row_num and Column_num arguments.

The syntax for the MATCH function

MATCH (lookup_value, lookup_array, [match_type])

Lookup_value is the value that is matched against the values \u200b\u200bin 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. Number -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 \u200b\u200bby 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:

\u003d 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:

\u003d 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 \u200b\u200bin the table with the result match the reference, and as a result we see that some of the values \u200b\u200bin 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:

\u003d 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 \u200b\u200bno longer interfere with our subsequent calculations using the values \u200b\u200bin 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 \u003d A
1001 to 1500 \u003d B
1501 to 2000 \u003d V
2001 to 2500 \u003d Y
More than 2501 \u003d 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 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 \u200b\u200bin lookup_vector can be text, numbers, or Boolean values.
The values \u200b\u200bin 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 range of one row or column. The result_vector must be the same size as the view_vector.

\u003d 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:
\u003d 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:
((\u003d SUM (() * ()))
but this approach is rather inconvenient and completely overlaps in functionality by the formula "SUMPRODUCT"
Now more 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 based on the value of shipments for each product for a specific 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, is 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:

\u003d SUMPRODUCT (($ A4 \u003d $ H $ 3: $ H $ 11) * ($ K $ 3: $ K $ 11\u003e \u003d 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\u003e \u003d 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:
\u003d SUMPRODUCT (($ A4 \u003d $ H $ 3: $ H $ 11) * ($ J $ 3: $ J $ 11\u003e \u003d B $ 3) * ($ J $ 3: $ J $ 11
A new condition has been added to the cookie selection formula:
(($ I $ 3: $ I $ 11 \u003d "small") + ($ I $ 3: $ I $ 11 \u003d "large"))
- as you can see, two or more conditions in one column are separated into 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:
\u003d SUMPRODUCT (($ A5 \u003d $ H $ 3: $ H $ 11) * ($ J $ 3: $ J $ 11\u003e \u003d B $ 3) * ($ J $ 3: $ J $ 11 "With jam"); \u200b\u200b($ L $ 3: $ L $ 11) * ($ K $ 3: $ K $ 11))

this:
($ 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 have 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 it will mean that my work has not been wasted!
Thank you for your time!

Let's say your report contains a table with a lot of data and many columns. It is extremely difficult to carry out a visual analysis of such tables. And one of the tasks for working with the report is - the analysis of data on the headings of rows and columns relating to a specific month. At first glance, this is a very simple task, but it cannot be solved using one standard function. Yes, of course you can use the tool: "HOME" - "Editing" - "Find" CTRL + F to call the search window for values \u200b\u200bin the Excel sheet. Or, create a conditional formatting rule for the table. But then it will be impossible to perform further calculations with the results obtained. Therefore, it is necessary to create and correctly apply the appropriate formula.

Finding a value in an Excel array

The scheme for solving the problem looks something like this:

in cell B1 we will enter the data of interest to us;

cell B2 will display the column header that contains the value of cell B1

cell B3 will display the name of the row that contains the value of cell B1.

In fact, you need to search for coordinates in Excel. What is it for? Quite often, we need to get the coordinates of a table by value. A bit like backward analysis of a matrix. A concrete example in a nutshell looks something like this. The goal in numbers is the initial value, you need to determine who and when is the closest to this goal. As an example, we will use a simple data matrix reporting the number of items sold for three quarters, as shown in the figure below. It is important that all numbers are consistent. If you don't want to manually create and fill in an Excel table from scratch, then at the end of the article you can download a ready-made example.

We will sequentially consider solutions of varying complexity, and at the end of the article - the final result.

Finding a value in an Excel column

First, let's learn how to get the table column headings by value. To do this, follow these steps:

In cell B1, enter the value taken from table 5277 and highlight its background in blue for readability of the input field (further we will enter other numbers in cell B1 to experiment with new values).

In cell C2, enter the formula to get the header of the table column that contains this value:

After entering the formula for confirmation, press the hotkey combination CTRL + SHIFT + Enter, since the formula must be executed in an array. If everything is done correctly, curly braces () will appear at the edges of the formula bar.

Finding a value in an Excel string

Now we get the line number for the same value (5277). To do this, enter the following formula in cell C3:

After entering the formula for confirmation, press the key combination CTRL + SHIFT + Enter again and get the result:

The formula returned the number 9 - found the title of the sheet row by the corresponding table value. As a result, we have the full address of the D9 value.

How to get column header and row name of a table

Now let's learn how to get the coordinates of not the whole sheet, but the current table by value. In short, we need to find the headers by value 5277 instead of D9:

for a table column - March;

for line - Item4.

To solve this problem, we will use a formula with the already obtained values \u200b\u200bin cells C2 and C3. To do this, we do this:

As a result, the internal coordinates of the table are obtained by value - March; Item 4:

Finding duplicate values \u200b\u200bin an Excel range

To control the presence of duplicates among the values \u200b\u200bof the table, we will create a formula that can inform us about the presence of duplicates and calculate their number. To do this, enter the formula in cell E2:

Moreover, for the range of the tabular section, let's create a conditional formatting rule:

As you can see, in the presence of duplicates, the formula for headings takes the heading from the first duplicate horizontally (from left to right). And the formula for getting the name (number) of the line takes the number from the first duplicate vertically (from top to bottom). There are 2 ways to fix this solution:

In this case, we change the formulas either one or the other, but not two at once. It is worth recalling that the old formula should remain in cell C3:

Here the coordinates of the first duplicate are displayed vertically (from top to bottom) - I7 for the sheet and August; Product2 for the table. Let's leave this option for the next final example.

Find the closest value in an Excel range

This table is still not perfect. After all, when analyzing, you need to know exactly all its values. If the formula does not find the entered number in cell B1 in the table, then an error is returned - #VALUE! It would be ideal if the formula, in the absence of the initial number in the table, would itself select the nearest value that the table contains. To create such a program for analyzing tables, enter a new formula in cell F1:

Then follows in all other formulas change the link instead of B1 should be F1! You also need to change the link in conditional formatting. Select: "HOME" - "Styles" - "Conditional Formatting" - "Manage Rules" - "Edit Rule". And here in the parameters specify F1 instead of B1. To check the operation of the program, enter in cell B1 a number which is not in the table, for example: 8000. This will lead to the final result:

Now you can enter any initial value, and the program itself will select the closest number that the table contains. Then it displays the column header and row name for the current value. For example, if you enter the number 5000, we get a new result:

Our program in Excel found the closest value of 4965 for the initial one - 5000. Such a program can be useful for automatically solving various analytical tasks in business planning, setting goals, finding a rational solution, etc. And the resulting rows and columns allow you to further expand the computing capabilities of this kind of reports using new Excel formulas.

This tutorial talks about the main benefits of 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 problems that the function VLOOKUP powerless.
In several recent articles, we have made every effort to explain the basics of the function to novice users VLOOKUP and show examples of more complex formulas for advanced users. 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 - 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 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)
\u003d 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 \u200b\u200bNew-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)
\u003d 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 SEARCHwhether 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 to 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 should 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 the 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 suppose 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))
\u003d 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))
\u003d 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)
\u003d 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 D4since 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 \u200b\u200bof the arguments row_num (line_number) and column_num (column_number) function MATCH (SEARCH). Otherwise, the result of the formula will be wrong.
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)
\u003d 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 SEARCHthat 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 VLOOKUPsince this function is much simpler. This happens 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 benefits 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 \u200b\u200bif 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)
\u003d 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 feature 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 VLOOKUPlooking in cells from B5 before D10 the value specified in the cell A2:

VLOOKUP (A2, B5: D10,3, FALSE)
\u003d VLOOKUP (A2, B5: D10, 3, FALSE)
The formula will not work if the value is in the cell A2 is longer than 255 characters. Instead, you need to use a similar formula INDEX/SEARCH:

INDEX (D5: D10, MATCH (TRUE, INDEX (B5: B10 \u003d A2,0), 0))
\u003d INDEX (D5: D10; SEARCH (TRUE; INDEX (B5: B10 \u003d A2; 0); 0))
4. Higher speed of work. If you work with small spreadsheets, the difference in Excel performance will most likely not be noticeable, 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 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 \u200b\u200bthe 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 much faster.

INDEX and SEARCH - 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 population the capital of Russia (Moscow) occupies.

As you can see in the picture below, the formula does an excellent job of this:

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

Now you shouldn't have any problems 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))
\u003d 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))
\u003d 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 SEARCHso 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 some formulas that apply 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))
\u003d 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))
\u003d 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))
\u003d 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 \u200b\u200bin 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 \u200b\u200bin the search column must be in descending order, and the minimum value that is greater than or equal to the mean is returned.

In our example, the values \u200b\u200bin 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 SEARCHwhich will return the column number.

INDEX (Your table, (MATCH (, the column to search in, 0)), (MATCH (, string in which to search,0))
\u003d 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 is a list of the most populated countries in the world. Suppose our task is to find out the US population in 2015.

Ok, 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 SEARCHwhich 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)
\u003d SEARCH ($ H $ 2; $ B $ 1: $ B $ 11; 0)
4 since “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)
\u003d 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))
\u003d 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 \u200b\u200bthey return, the formula will become easy and straightforward:

INDEX ($ A $ 1: $ E $ 11,4,5))
\u003d 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... Simply? 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: the formula INDEX/SEARCH can search by values \u200b\u200bin 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 are in the table on the sheet Lookup table are arranged in no particular order.

This is the formula INDEX/SEARCH solves the problem:

(\u003d INDEX ("Lookup table"! $ A $ 2: $ C $ 13, MATCH (1, (A2 \u003d "Lookup table"! $ A $ 2: $ A $ 13) *
(B2 \u003d "Lookup table"! $ B $ 2: $ B $ 13), 0), 3))
(\u003d INDEX ("Lookup table"! $ A $ 2: $ C $ 13; MATCH (1; (A2 \u003d "Lookup table"! $ A $ 2: $ A $ 13) *
(B2 \u003d "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 SEARCHI think it should be explained first.

MATCH (1, (A2 \u003d "Lookup table"! $ A $ 2: $ A $ 13), 0) * (B2 \u003d "Lookup table"! $ B $ 2: $ B $ 13)
MATCH (1; (A2 \u003d "Lookup table"! $ A $ 2: $ A $ 13); 0) * (B2 \u003d "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. So when you're done entering your formula, don't 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 have 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. Try again!") \u003d 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.
Conditional Formatting (5)
Lists and Ranges (5)
Macros (VBA procedures) (63)
Miscellaneous (39)
Excel bugs and glitches (4)
How to find a value in another table or VLOOKUP strength

In fact, in this article I want to talk about the possibilities not only vLOOKUP functions, but I also want to touch on SEARCH, as a function very closely related to VLOOKUP. Each of these features has both pros and cons. In a nutshell, the VLOOKUP looks for a certain value we specified among the set of values \u200b\u200blocated in one column. Perhaps most often, the need for VLOOKUP arises when you need to compare data, find data in another table, add data from one table to another, based on some criterion, etc.
To get a little better understanding of how VLOOKUP works, it's best to start with a practical example. There is a table like this:
fig. 1

and from the first table must be substituted into the second date for each surname. For three records, this is not a problem and to do it by hand - everything is obvious. But in real life these are tables with thousands of records and manual substitution searches can take more than one hour. Plus a couple more fly in the ointment: not only are the full names arranged in a completely different order in both tables and the number of records in the tables is different, so the tables are also located on different sheets / books. I believe I have convinced you that hand substitution is not an option at all. But VLOOKUP (VLOOKUP) here will be irreplaceable. In this case, practically nothing will need to be done - just write in the first cell of column C of the second table (where you need to substitute dates from the first table) this formula:
\u003d VLOOKUP ($ A2; Sheet1! $ A $ 2: $ C $ 4; 3; 0)
You can write the formula either directly into the cell, or using the function manager by selecting in the category References and arrays VLOOKUP and separately specifying the necessary criteria. Now copy ( Ctrl+C) cell with the formula, select all the cells in column C to the end of the data and insert ( Ctrl+V).

First, the basic principle of operation: VLOOKUP searches in the first column of the Table argument for the value specified by the argument Lookup_value ... When the required value is found, the function returns the value opposite the found value, but from the column specified by the argument Column_number ... We will deal with interval viewing a little later. VLOOKUP can return only one value - the first one that matches the criterion. If the desired value is not found (not in the table), then the result of the function will be # N / A ... Do not be afraid of this - it is even useful. You will know exactly which records are missing and thus you can compare the two tables with each other. Sometimes it turns out that you see: the data is in both tables, but VLOOKUP gives # N / A. So the data in your tables is not identical. Some of them have extra inconspicuous spaces (usually before or after the value), or Cyrillic characters are mixed with Latin characters. Same # N / A will be if the criteria are numbers and in the desired table they are written as text (as a rule, a green triangle appears in the upper left corner of such a cell), and in the final - as numbers. Or vice versa.
Description of VLOOKUP arguments
$ A2 - argument Lookup_value (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 S.A. Petrov. Here you can specify either the text of the criterion directly (in this case it must be in quotation marks - \u003d VLOOKUP ("Petrov S.A"; Sheet1! $ A $ 2: $ C $ 4; 3; 0), or a link to a cell with this text (as in the example function)... There is a small nuance: you can also use wildcards: "*" and "?". This is very convenient if you only need to find values \u200b\u200bfrom part of a string. For example, you may not enter "Petrov S.A" in full, but enter only the surname and an asterisk - "Petrov *". Then any record that starts with "Petrov" will be displayed. If you need to find a record in which the surname "Petrov" appears anywhere in the line, then you can specify it like this: "* Petrov *". If you want to find the last name Petrov and no matter what initials the name and patronymic will have (if the full name is written in the form of Ivanov II), then here is just the following: "Ivanov? ... It is often necessary for each line to indicate its own value (in the Surname column A and you need to find them all). In this case, references to cells in column A are always indicated. For example, in cell A1 it is written: Ivanov. It is also known that Ivanov is in another table, but after the surname, both the name and patronymic (or something else) can be written. But we only need to find the line that starts with the last name. Then it is necessary to write as follows: A1 & "*". This entry will be equivalent to "Ivanov *". A1 says Ivanov, ampersand (&) is used to combine two text values \u200b\u200binto one string. An asterisk in quotes (as text inside a formula should be). Thus, we get:
A1 & "*" \u003d\u003e
"Ivanov" & "*" \u003d\u003e
"Ivanov *"
It is very convenient if there are many values \u200b\u200bto search for.
If you need to determine whether there is at least somewhere a word in the string, then put asterisks on both sides: "*" & A1 & "*"
Sheet1! $ A $ 2: $ C $ 4 - argument Table... Specifies a range of cells. Only the range should contain data from the first data cell to the very last. It does not have to be the range specified in the example. If there are 100 rows, then Sheet1! $ A $ 2: $ C $ 100. It is important to remember three things: first, it is Table should always start with the column you are looking for Criterion ... And nothing else. Otherwise, nothing will be found or the result will not be what you expect. Second: argument Table must be "pinned" ... What does it mean. See the dollar signs - $? This is anchoring (more precisely, this is called an absolute range reference)... How it's done. Highlight link text (only one range - one criterion) and press F4 until you see dollars appear before both the column name and the row number. If this is not done, then when copying the formula, the Table argument will "move out" and the result will again be incorrect. And the last - the table must contain columns from the first (in which we are looking) to the last (from which we need to return values). In the example Sheet1! $ A $ 2: $ C $ 4 - so it will not be possible to return the value from column D (4), since there are only three columns in the table.

3 - Column_number ... Here we just specify the column number in the argument Table, the values \u200b\u200bfrom which we need to substitute as the result. In the example, this is the Acceptance Date - i.e. column number 3. If a department was needed, then we would indicate 2, and if we just needed to compare whether the names of one table are in another, then we could also indicate 1. Important: the argument Column_number must not exceed the number of columns in the argument Table ... Otherwise, the formula will result in an error. #LINK!... For example, if the range $ B $ 2: $ C $ 4 is specified and you need to return data from column C, then 2 is correct. argument Table($ B $ 2: $ C $ 4) contains only two columns - B and C. If we try to specify the number of column 3 (what is the count on the sheet), then we get an error #LINK!since there is simply no third column in the specified range.

Practical tip: if the Table argument has too many columns and you need to return the result from the last column, then it is not at all necessary to calculate their number. You can specify it like this: \u003d VLOOKUP ($ A2; Sheet1! $ A $ 2: $ C $ 4; NUMBERCOLUMN (Sheet1! $ A $ 2: $ C $ 4); 0). By the way, in this case, Sheet1! can also be removed as redundant: \u003d VLOOKUP ($ A2; Sheet1! $ A $ 2: $ C $ 4; NUMBERCOLUMN ($ A $ 2: $ C $ 4); 0).

0 - Interval_view is a very interesting argument. Can be either TRUE or FALSE. The question immediately arises: why is there 0 in my formula? It's very simple - Excel in formulas can perceive 0 as FALSE, and 1 as TRUE. If you specify this parameter equal to 0 or FALSE in the VLOOKUP, then an exact match to the specified Criteria will be searched. This has nothing to do with wildcards ("*" and "?"). If you use 1 or TRUE (or do not specify the last argument at all, since it is TRUE by default), then ... It's a very long story. In short - VLOOKUP will look for the most similar value that matches Criterion ... Very helpful sometimes. However, if you use this parameter, then the list in the Table argument must be sorted in ascending order. I draw your attention to the fact that sorting is necessary only if the Interval_View argument is TRUE or 1. If 0 or FALSE, sorting is not needed.

Many probably noticed that in the picture I have messed up departments for the full name. This is not a recording error. The example attached to the article shows how you can substitute both them and the dates with one formula without manually changing the argument Column_number... It seemed to me that such an example may well come in handy.

How to avoid # N / A (# N / A) error in VLOOKUP?
Another common problem is that many people do not want to see # N / A as the result if no match is found. It's easy to get around it:
\u003d IF (UND (VLOOKUP ($ A2; Sheet1! $ A $ 2: $ C $ 4; 3; 0)); ""; VLOOKUP ($ A2; Sheet1! $ A $ 2: $ C $ 4; 3; 0)))
Now if VLOOKUP does not find a match, then the cell will be empty.
And users of versions of Excel 2007 and higher can use IFERROR:
\u003d IFERROR (VLOOKUP ($ A2; Sheet1! $ A $ 2: $ C $ 4; 3,0); "")

The Promised SEARCH

This function searches for the value specified by the parameter Lookup_value in argument View_array ... And the result of the function is the position number of the found value in Viewable_array... It is the position number, not the value itself. In principle, I will not describe it in the same detail, because the main points are exactly the same. If we wanted to apply it to the table above, it would be like this:
\u003d SEARCH ($ A2; Sheet1! $ A $ 2: $ A $ 4; 0)
$ A2 - Lookup_value. Here everything is exactly the same as with VLOOKUP. Wildcards are also allowed and in exactly the same design.

Sheet1! $ A $ 2: $ A $ 4 - The array to look at. The main difference from VLOOKUP is that you can specify an array with only one column. This should be the column we're going to search for Lookup_value ... If you try to specify more than one column, the function will return an error.

Match_type (0) - the same as in VLOOKUP Interval_view ... With the same features. It differs only in the ability to search for the smallest from the desired or the largest. But I will not dwell on this in this article.

With the main sorted out. But we need to return not the position number, but the value itself. It means that SEARCH in its pure form does not suit us. At least one by itself. But if you use it together with the INDEX function, then this is what we need and even more.
\u003d INDEX (Sheet1! $ A $ 2: $ C $ 4; SEARCH ($ A2; Sheet1! $ A $ 2: $ A $ 4; 0); 2)
This formula will return the same result as VLOOKUP.

INDEX function arguments
Sheet1! $ A $ 2: $ C $ 4 - Array. As this argument, we specify the range from which we want to get values. There can be one column or several. If there is only one column, then the last argument of the function should not be specified. By the way - this argument may not coincide at all with the one that we specify in the argument View_array of the MATCH function.

Next are RowNumber and ColumnNumber. It is as Line_Number that we substitute SEARCH, which returns us the position number in the array. Everything is built on this. INDEX returns the value from Array that is in the specified row (Row_Number) of the Array and in the specified column (Column_number) if there are more than one column. It is important to know that in this bundle the number of lines in the Array argument of the INDEX function and the number of lines in the Look-up_array argument of the MATCH function must match. And start on the same line. This is in normal cases, if you do not pursue other goals.
As in the case of VLOOKUP, INDEX returns # N / A if the required value is not found. And you can also work around such errors:
For all versions of Excel (including 2003 and earlier):
\u003d IF (UND (SEARCH ($ A2; Sheet1! $ A $ 2: $ A $ 4; 0)); ""; INDEX (Sheet1! $ A $ 2: $ C $ 4; SEARCH ($ A2; Sheet1! $ A $ 2: $ A $ 4; 0); 2))
For versions 2007 and higher:
\u003d IFERROR (INDEX (Sheet1! $ A $ 2: $ C $ 4; SEARCH ($ A2; Sheet1! $ A $ 2: $ A $ 4; 0); 2); "")

Working with criteria longer than 255 characters
INDEX-POISKPOS also has one more advantage over VLOOKUP. The point is that VLOOKUP cannot search for values whose line length contains more than 255 characters... This rarely happens, but it does happen. You can, of course, deceive the VLOOKUP and cut the criterion:
\u003d VLOOKUP (MID ($ A2; 1; 255); MID (Sheet1! $ A $ 2: $ C $ 4; 1; 255); 3; 0)
but this is an array formula. Moreover, such a formula does not always return the desired result. If the first 255 characters are identical to the first 255 characters in the table, and then the characters differ, the formula will no longer see this. And the formula returns only text values, which is not very convenient in cases where numbers should be returned.

Therefore, it is better to use a tricky formula:
\u003d INDEX (Sheet1! $ A $ 2: $ C $ 4; SUMPRODUCT (SEARCH (TRUE; Sheet1! $ A $ 2: $ A $ 4 \u003d $ A2; 0)); 2)
Here I have used the same ranges in the formulas for readability, but in the download example they are different from those shown here.
The formula itself is built on the ability of the SUMPRODUCT function to transform some functions inside it into massive calculations. In this case, MATCH searches for the position of the string where the criterion is equal to the value in the string. You won't be able to use wildcards here.

In the example attached to the article, you will find examples of using all the described cases and an example of why INDEX and MATCH are sometimes preferable to VLOOKUP.

Download example
(26.0 KiB, 14 615 downloads)
Did the article help? Share the link with your friends! Video lessons
("Bottom bar" :( "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic": 0, "textpositiondynamic": "bottomleft", "textpositionmarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffecteasing1": "easeOutCubic", "texteffectduration1": 600 , "texteffectdelay1": 1000, "texteffect2": "slide", "texteffectslidedirection2": "right", "texteffectslidedistance2": 120, "texteffecteasing2": "easeOutCubic", "texteffectduration2": 600, "texteffectdelay2": 1500 textcss ":" display: block; padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; height: 100% ; background-color: # 333333; opacity: 0.6; filter: 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," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "," descriptioncssresponsive ":" display: none! important; "," buttoncssresponsive ": "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40))