adov_polosach September 29, 2014 at 5:28 pm

Manual for solving typical problems in Microsoft Excel

• Algorithms

Good afternoon, dear Habro residents!

From time to time, some (and maybe more than some) of us have to deal with tasks of processing small amounts of data, ranging from compiling and analyzing a household budget to 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 -. It gives good description 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 arose to type them and propose possible solutions(albeit not all possible, but quickly giving results).

We will talk about solving the most common problems that users encounter.

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

Case_1: Logic functions and matching functions
“I have a set of values ​​in a table and it is necessary that when a certain condition/set of conditions is met, it will display specific value» (c) User

The data is usually presented in tabular form:

Condition:

• if the value in the "Quantity" column is greater than 5,
• then you need to display the value “No order required” in the “Result” column,

The “IF” formula will help us with this, which refers to logical formulas and can produce in the solution any values ​​that we write in advance in the formula. Please note that any text values ​​are written using quotes.

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

• Logical expression is an expression that evaluates to TRUE or FALSE.
• Value_if_true - the value that is printed if logical expression true
• Value_if_false - the value that is printed if the logical expression is false

Formula syntax for solution:

=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 the “IF” formula; we need to add another formula to its syntax. And this will be another logical formula “AND”.
The formula syntax is as follows:
AND(boolean_value1, [boolean_value2], ...)

• Boolean_value1-2, etc. - a condition to be tested, the evaluation of which results in the value TRUE or FALSE

Outputting the result in 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 the value “1” in the “Result” column, otherwise “0”.

The solution syntax will be as follows:
Outputting the result in 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 has one OR condition and two AND conditions included within 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:
Let’s 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 not very convenient and cumbersome, but it can also 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 entered manually and our formula “inflated” to large sizes; however, the approach is distinguished by complete “omnivorousness” of values ​​and versatility of use.

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

• Index_number - the number of the selected value argument. The index number must be a number between 1 and 254, a formula, or a reference to a cell containing a number between 1 and 254.
• Value1, value2,... - a value from 1 to 254 value arguments, from which the “SELECT” function, using the index number, selects the value or 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 similar to the IF function chain solution above.
The following restrictions apply when applying this formula:
Only numbers can be entered into 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 only work if cell “A2” contains numbers from 1 to 254 in ascending order, and this imposes certain restrictions when using this formula.
Those. if we want the value “G” 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 in 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 serial number “5”.

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

• Search_value – the value that is searched by the function.
• A 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 boolean.
• Column_number - the number of the column in the “Table” argument from which the value will be derived if there is a match. It is important to understand that columns are not counted according to general grid sheet (A.B,C,D, etc.), but inside the array specified in the "Table" argument.
• Interval_lookup - determines whether the function should find an exact match or an approximate match.

Important: The VLOOKUP function searches for a match only on the first unique record if the lookup_value is present in the Table argument multiple times and has different meanings, then the “VLOOKUP” function will find only the very FIRST match, the results for all other matches will not be shown. The use of the “VLOOKUP” formula is associated with another approach to working with data, namely the formation of “directories”.
The essence of the approach is to create a “directory” of the correspondence of the “Searched_value” argument to a specific result, separately from the main array, in which the conditions and the corresponding values ​​are written:

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

Result:

Now imagine a situation where you need to pull data into one table from another, but 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 in cell B2:

But when solving it, we encounter a new problem - when “stretching” the formula we wrote to the right from column “B” to column “E”, we will have to manually replace the “column_number” argument. This is a labor-intensive and thankless task, therefore, another function comes to our aid - “COLUMN” (COLUMN).
Function syntax:
COLUMN([link])

• A reference is a 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:
Outputting the result in 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_Number" argument to determine the number of the search column in the directory.
Alternatively, you can use the following construction:

Instead of the number “1”, you can use any number (and not only subtract it, but also add it to the resulting value) to obtain 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: imagine that we have two directories with different data on products and we need 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), disadvantages - it doesn’t fit well if there are more than 3 options.
If directory numbers are always numbers, it makes sense to use the following solution:
Outputting the result in cell C3:

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

pros: the formula can include up to 254 directory names, disadvantages - their names must be strictly numerical.
Result for formula using SELECT function:

Bonus: VLOOKUP based on two or more characteristics in the “search_value” argument.
Condition:

• Let's imagine that, as always, we have an array of data in tabular form (if not, then we bring the data to it), we need to obtain values ​​from the array based on certain characteristics and place them in another tabular form.

Both tables are shown below:

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

Using the “&” symbol, we combine three characteristics into one (the separator between words can be anything, or not at all, the main thing is to use a similar rule for searching)
An analogue of the formula can be the “CONCATENATE” function, in which case it will look like this:

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

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

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

In the “VLOOKUP” function, as the “search_value” argument, we use the same combination of three characteristics (name_class_packing), but we take it already in the table for filling and enter it directly into the argument (alternatively, we could select the value for the argument in an additional column in table to fill out, but this action will be unnecessary).
I remind you that using the “IFERROR” function is necessary if the desired value is not found, and the “VLOOKUP” function will give us the value “#N/A” (more on this below).
The result is in the picture below:

This technique can be used for a larger number of characteristics, the only condition is the uniqueness of the resulting combinations; if this is not met, the result will be incorrect.

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

Let's consider a situation where we need to understand whether the cell array contains the values ​​we need.
Task:

• the “Search Condition” column contains a value and you need to determine whether it is present in the “Search Array” column

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 rather the presence of the value we need in the cell.
To solve the problem it is necessary to use a combination of several functions, namely:
"IF"
"IF ERROR"
"LOWER"
"FIND"

In order about everyone, we have already discussed “IF” earlier, so let’s move on to the “IFERROR” function.

IFERROR(value, error_value)

• The value is the argument that is checked for errors.
• Value_on_error - the value returned if there is an error when calculating the formula. The following types of errors are possible: #N/A, #VALUE!, #REF!, #DIV/0!, #NUMBER!, #NAME? and #EMPTY!.

Important: this formula is almost always required when working with arrays of information and reference books, because It often happens that the value you are looking for is not in the directory, 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 occur with an error. Plus, cells where the formula returned an error can be assigned different values, which makes it easier statistical processing. 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 ramified conditions.

"LOWER"

• Text - text converted to lowercase.

Important: the "LOWER" function does not replace characters that are not letters.
Role in the formula: since the “FIND” function searches and takes into account the case of the text, it is necessary to convert all the text to the same case, otherwise “tea” will not be equal to “tea”, etc. This is relevant if the register value is not a condition for searching and selecting values, otherwise the “LOWER” formula can not be used, so the search will be more accurate.

Now let's take a closer look at the syntax of the FIND function.

FIND(search_text, viewed_text, [start_position])

• Search_text - the text that needs to be found.
• Search_text - the text in which you want to find the searched text.
• Start_position - the sign from which to start the search. The first character in the text "view_text" is numbered 1. If the number is not specified, it defaults to 1.

The syntax of the solution formula will look like:
Outputting the result in cell B2:

=IF(IFERROR(FIND(LINE(A2), LINE(E2),1),0)=0,“fail”,“bingo!”)

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

1. LOWER(A2) – converts the Search_Text argument in cell A2 to lowercase text
2. The FIND function begins searching for the converted argument Search_Text in the Search_Text array, which is converted by the LOWER(E2) function, also to lowercase text.
3. If the function finds a match, i.e. returns the serial number of the first character of the matching word/value, the TRUE condition in the “IF” formula is triggered, because the resulting value is not zero. As a result, the “Result” column will display the value “Bingo!”
4. If, however, the function does not find a match, i.e. the serial number of the first character of the matching word/value is not indicated and an error is returned instead of the value, the condition included in the “IFERROR” formula is triggered and a value equal to “0” is returned, which corresponds to the FALSE condition in the “IF” formula, because the resulting value is “0”. As a result, the value “fail” will be displayed in the “Result” column.

As can be seen from the figure above, thanks to the “LOW” and “FIND” functions, we find the desired values ​​regardless of the case of characters and location in the cell, but we need to pay attention to line 5.
The search term is set to "111", but the search array contains the value "1111111 cookies", but the formula returns the result "Bingo!" This happens because the value “111” is included in the series of values ​​“1111111”, as a result a match is found. Otherwise, this condition will not work.

Case_4 Searching for a value in an array based on several conditions, or when VLOOKUP is even more unable to help us

Let’s imagine a situation where you need to find a value from the “Table with the result” in two-dimensional array“Directory” for several conditions, namely the value “Name” and “Month”.
The tabular form of the task will look like this:

Condition:

• It is necessary to pull the data into the table with the result in accordance with the coincidence of the conditions “Name” and “Month”.

To solve this problem, a combination of the “INDEX” and “SEARCH” functions is suitable.

Syntax of the INDEX function

INDEX(array, row_number, [column_number])

• Array - a range of cells from which values ​​will be shown if their search conditions match.
• If the array contains only one row or one column, the row_number or column_number argument, respectively, is optional.
• If the array occupies more than one row and one column, and only one of the arguments “row_number” and “column_number” is given, then the “INDEX” function returns an array consisting of whole line or an entire column of the "array" argument.
• Line_number - the number of the line in the array from which you want to return a value.
• column_number - 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 located at the intersection of the coordinates specified in the “Row_Number” and “Column_Number” arguments.

MATCH Function Syntax

MATCH(lookup_value, lookup_array, [match_type])

• Lookup_value is the value that matches the values ​​in the lookup_array argument. The lookup_value argument can be a value (number, text, or Boolean) or a reference to a cell containing such a value.
• Looked_array - the range of cells in which the search is performed.
• match_type is an optional argument. The number is -1, 0 or 1.

The MATCH function searches a range of cells for a specified element and returns the relative position of that element in the range.
The essence of using a combination of the “INDEX” and “SEARCH” functions is that we search for the coordinates of 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:

MATCH($A4,$I$4:$I$7,0)

returns the number along the Y axis, in this case it will be equal to 1, because the value "A" is present in the searched range and has a relative position of "1" in that range.
part of the formula:

MATCH(B$3,$J$3:$L$3,0)

returns #N/A because the value "1" is not in the range being viewed.

Thus, we received the coordinates of the point (1; #N/A) which the “INDEX” function uses to search in the “Array” argument.
The fully written function for cell B4 would look like this:

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

Essentially, if we knew the coordinates of the value we needed, the function would look like this:

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

Since the argument “Column_Number” has the value “#N/A”, the result for cell “B4” will be corresponding.
As can be seen from the resulting result, not all the values ​​in the table with the result match the reference book 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, MATCH($A4,$I$4:$I$7,0), MATCH(B$3,$J$3:$L$3,0)),0)

Result demonstration:

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

Case_5 Finding a value in a range of numbers

Let's imagine that we need to give a certain sign to numbers included in a certain range.
Condition:
Depending on the cost of the product, it should be assigned a certain category
If the value is in the range

• From 0 to 1000 = A
• From 1001 to 1500 = B
• From 1501 to 2000 = B
• From 2001 to 2500 = G
• 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 form.

LOOKUP(lookup_value, lookup_vector, [result_vector])

• lookup_value is the value that the LOOKUP function looks for in the first vector. Lookup_value can be a number, text, boolean, name, or value reference.
• Watch_vector is a range consisting of one row or one column. The values ​​in the lookup_vector argument can be text, numbers, or booleans.
• The values ​​in the view_vector argument must be in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, the LOOKUP function may return an incorrect result. Text at the bottom and uppercase considered equivalent.
• result_vector is a range consisting of one row or column. The result_vector must have the same size as the lookup_vector.

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

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

Case_6 Summation of numbers by characteristics

To sum numbers based on certain characteristics, you can use three different functions:
SUMIF – sums by only one attribute
SUMIFS – sums over multiple characteristics
SUMPRODUCT – sums over multiple characteristics
There is also an option using "SUM" and the array formula function, when the "SUM" formula is raised to an array:
((=SUM(()*()))
but this approach is quite inconvenient and is completely covered in functionality by the “SUMPRODUCT” formula
Now for more details on the “SUMPRODUCT” syntax:

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

• Array1 is the first array whose components need to be multiplied and then the results added.
• Array2, array3… - from 2 to 255 arrays, the components of which must be multiplied and then the results added.

Condition:

• Find the total cost of shipments for each product for a certain period:

As can be seen from the table with the data, in order to calculate the cost, it is necessary to multiply the price by the quantity, and transfer the resulting value, applying the selection conditions, to the table with the result.
However, the SUMPROIZ formula allows such calculations to be made within the formula.
Outputting the result in cell B4:

=SUMPRODUCT(($A4=$H$3:$H$11)*($K$3:$K$11>=B$3)*($K$3:$K$11
Let's look at the formula in parts:
– 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 – we set a condition based on 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 a table with the result, an array is in a table with data.
– multiply the columns “Quantity” and “Price” in the table with the data.
The undoubted advantage of this function is the free order of recording conditions; they can be written in any order, this will not affect the result.
Result:

Now let’s complicate the condition and add the requirement that selection for the name “cookies” occurs only in the classes “small” and “large”, and for the name “rolls” everything except for the class “with jam”:

Outputting the result in 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 formula for selecting cookies:

(($I$3:$I$11=“small”)+($I$3:$I$11=“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 formula for selection by rolls:

=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:

($I$3:$I$11<>"with jam")

– in fact, in this formula it was possible to write a selection condition in the same way as when selecting by cookies, but then, you would have to list three conditions in the formula, in this case, it is easier to write an exception - does not equal “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 the conditions into a function, inflating it.
Result:

Well, here we come to the end of our short manual, which in fact could be much longer, but the goal was still to provide a solution to the most common situations, and not to describe the solution to 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 was not in vain!

Thank you for your time!

Good afternoon

In this article we will talk and consider the inherent capabilities and options for how to use MATCH function. This function is in some cases an alternative to vertical search capabilities for values. In some cases MATCH function more flexible and has its own characteristics that elevate it to first place.

So, let's start getting acquainted right now, and the first step will be getting to know spelling of the MATCH function. This function looks up the value you specify and returns the resulting value's position in the range. For example, we have a range B1:B3 in which the values ​​are written: January, February, March and the subsequent formula should return the number 2, since the value “February” is the second element in the list.

MATCH("February";B2:B13;0)

SyntaxMATCH functions

As can be seen from the example MATCH function has the following syntax, which we will consider in more detail:

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

• "Search_value"- this argument is responsible for the data you are looking for. This data can be blank, text, any boolean value, or just a cell reference.
• "Browsed_array"- this argument shows where the search will be performed;
• "Mapping_type"— this argument allows you to tell the MATCH function what kind of match to look for: approximate or exact:
  • 1 or no argument– will search for the maximum value that is equal to or less than the desired value. Mandatory, who views MATCH function, you must order in ascending order, from smallest to largest.
  • 0 – returns the first value that matches the searched one. This argument allows for a precise search.
  • -1 – this argument will find the smallest value that is equal to or greater than the value you are looking for. In this case, the data must be sorted in descending order from large to small.

Maybe the benefits it brings MATCH function, may seem dubious, but note that this function looks for and returns the relative position in the array of the value we are looking for. And this value is the main one for the arguments, which can be used in conjunction with the SEARCH function.

Benefits of the MATCH function

The first advantage When using the SEARCH function and its combination with other functions (for example, the INDEX function), this is the ability to search from right to left. This is different from VLOOKUP because it cannot scan to the left.

The second advantage is a feature in the lookup table. I mean, the safety of this process to work MATCH functions, deletion does not affect the correct operation of the formula. But the VLOOKUP formula may return or simply not work, since the formula syntax requires specifying the entire range, as well as the column number to extract the value.

The third advantage– there is no limit on the length of the value that the MATCH function searches for, 255 characters. There is no limit for her. And in the VLOOKUP function, the value you are looking for, and if it exceeds the specified value, then you will get an error #VALUE.

Fourth advantage– this is an increased speed of work. In principle, for those who work with small tables, the accelerated performance is unnoticeable, but with thousands of rows and hundreds of formulas, the operation of the MATCH function alone or in tandem with the INDEX function will work, judging by data in authoritative publications, relative to the VLOOKUP function by 13 -15% faster. Since checking each value in the range of values ​​calls the VLOOKUP function separately, which means that the more data and formulas in the array, the more leisurely Excel works.

Examples of the MATCH function

Let's move from theory to practice and look at a few examples of how it works MATCH function:

Find AVERAGE, MAX, MIN using a combination of MATCH and INDEX functions

MATCH function allows you to nest other functions within itself, and with this feature you can find the maximum, minimum or closest to the average (), examples:

1. MIN function:=INDEX($C$2:$C$9,MATCH(MIN($I$2:I$9),$I$2:I$9,0)) ;
2. MAX function:=INDEX($C$2:$C$9,MATCH(MAX($I$2:I$9),$I$2:I$9,0)) ;
3. AVERAGE function:=INDEX($C$2:$C$9,MATCH(AVERAGE($I$2:I$9),$I$2:I$9,1))

Each function returns the value we need.

Search using the MATCH function on the left side

I said earlier that VLOOKUP function is not friendly with the left side, which means we will use MATCH function in tandem with INDEX function, since they are more flexible and the location of the column does not matter to them. Let’s look at the example of choosing where “Belarus” ranks in terms of sales, and we will use the following formula for this:

INDEX($I$2:$I$9,MATCH(“Belarus”,$D$2:$D$9,0))

Let's look at the formula in more detail. Firstly, the formula MATCH(“Belarus”,$D$2:$D$9,0) finds the country’s position in the list. Secondly, the INDEX function of the range $I$2:$I$9 from which the value will be extracted. Third, we combine all two parts of the formula and get the result we need.

Attention! When specifying ranges in the MATCH function, it is advisable to always use absolute references to improve the reliability of the formula.

We search using several criteria

So, let's search for the values ​​we need using several criteria without additional movements, unlike the VLOOKUP function of which we need to create an additional auxiliary column. We will search in two columns: "A country" And "Product", the difficulty lies in the fact that the product will be supplied to different countries and the data in the tables is arranged in random order.

The following formula will solve our problem:

(=INDEX($F$3:$H$10,MATCH(1,(A3=$F$3:$F$10)*(B3=$G$3:$G$10),0),3))

If we look at this more complex formula in detail, it can be described as follows: let’s start with the MATCH function, we are looking for the value 1 , and the array of our search is the result of multiplication, namely, the value in the first column is taken "A country" V Table 1 and compare with the names of all countries in Table2. If a match is found, the formula returns 1 or TRUE, and if not, then 0 or LIE. Then we do the same for the column "Products". After all this, we multiply the results and if matches are found, the result will be 1 , if not, then we get - 0 . So, MATCH function returns the position if both criteria are met. Be sure to use the third argument in the formula "3", since the entire table was indicated and clarification is needed from which column the values ​​should be extracted, in our case the column was the third. Also note that this is an array formula, so we put it in curly braces and enter

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

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

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

Basic information about INDEX and MATCH

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

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

INDEX – function syntax and usage

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

Each argument has a very simple explanation:

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

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

Here is a simple example of a function INDEX(INDEX):

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

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

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

MATCH - function syntax and usage

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

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

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

Function MATCH(MATCH) has the following syntax:

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

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

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

Let us remind you that the relative position of the value we are looking for (i.e. row and/or column number) is exactly what we need to specify for the arguments row_num(line_number) and/or column_num(column_number) functions INDEX(INDEX). As you remember, the function INDEX can return the value 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 MATCH in Excel

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

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

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

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

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

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

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

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

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

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

This is the result you get in Excel:

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

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

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

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

Why is INDEX/MATCH better than VLOOKUP?

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

4 Main Benefits of Using MATCH/INDEX in Excel:

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

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

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

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

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

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

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

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

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

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

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

4. Higher operating speed. If you work with small tables, then the difference in Excel performance will most likely not be noticeable, especially in the latest versions. If you work with large tables that contain thousands of rows and hundreds of search formulas, Excel will work much faster if you use SEARCH And INDEX instead of VLOOKUP. In general, this replacement increases 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 ​​an array contains and the more array formulas your table contains, the slower Excel works.

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

INDEX and MATCH - examples of formulas

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

How to search from the left side using MATCH and INDEX

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Result: Beijing

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

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

Result: Lima

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

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

Result: Moscow

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

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

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

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

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

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

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

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

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

And I congratulate those of you who guessed it!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Multi-criteria search with INDEX and MATCH

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

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

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

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

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

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

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

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

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

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

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

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

INDEX and MATCH combined with IFERROR in Excel

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

Function Syntax IFERROR very simple:

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

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

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

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

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

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

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

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

Good afternoon, dear reader!

In this article, I want to again recall the power and usefulness of the VLOOKUP function and show 4 ways to search for data in an Excel table using other functions and their combinations with several conditions. Many, many actions can be performed using this function, but, nevertheless, it has some limitations, for example, VLOOKUP can only search on the left side or by one condition. In this regard, we will look for options and use tricks to expand its basic functionality.

In the previously described article, I described it in detail, so you can read it by following the link. This is important if you are meeting for the first time. You can also familiarize yourself with the twin VPR, I also described it in detail, with all the advantages and disadvantages. And if you are familiar with the theory, let's proceed to practical application.

Now, using examples, we will look at all 4 ways to search for data in an Excel table and combine the VLOOKUP function with other functions:

Using the SUMPRODUCT function

As I described earlier in my article about, it is one of the most powerful in Excel's arsenal. And we will do exactly the first way using the formula capabilities when using the SUMPRODUCT function. For our purposes, the formula will look like this:

=SUMPRODUCT((C2:C11=G2)*(B2:B11=G3),D2:D11)
The principle of the formula is as follows: a conditional table is created in which the cell values "G2" compared to range "C2:C11" and cell "G3" with range "B2:B11". After this, all these two arrays are compared and compared and converted into ones and zeros, where the value of one is assigned to the line where all the conditions of the formula are met. The next operation is multiplying the resulting conditional array by a range "D2:D11", and since there is only one unit in the array, the formula will get the result 146 .

Draw your attention, if in range "D2:D11" text values ​​will be found, the formula will refuse to work. For a more in-depth introduction to the SUMPRODUCT function, I advise you to read my article.

Using the SELECT function

I have already described it, but haven’t mentioned it in this version yet. In our case, we need to create a new table in which the columns will be shared "Period" And "Month", all this will be virtually created by the SELECT function. The formula to work will look like this:

(=VLOOKUP(G2SELECT((1,2),C2:C11&B2:B11,D2:D11),2,0))
The main work that the SELECT function does in its part "SELECT((1;2);C2:C11&B2:B11;D2:D11)" this is the union of the column values "Period" And "City" into a common array, the values ​​in which will be written as: "MoscowJanuary", "BryanskFebruary",…. etc... Having received such a combined value of the columns, we can easily view and select the desired value, now I think the formula has become closer.

Very important! Since we are working with , the input must be made Ctrl+Shift+Enter. In this case, the system will detect the formula as being created for arrays and will place curly braces on both sides of the formula.

Creating additional columns

Let's digress a little from the complex and look at a simpler example, this is when, instead of enlarging the formula, we divide it into components and do the work in small pieces.

Let's look at a standard example when it is necessary to determine sales by two indicators: "Period" And "City". In this case, the usual use of the VLOOKUP function will not suit us, since the function can return a value based on one condition. In this case, we need to create an additional column in which two criteria will be combined into one, so in the created column we assign the formula for merging the values: =B2&C2. And now the result from column D, we can use our formula in cell H4:

=VLOOKUP(H2D2:E11,2,0)

As you can see, our individual value selection conditions are also combined with the argument H2&H3 into one criterion. After searching in the specified range D2:E11, the formula will return the found value from column 2.

We combine the SEARCH and INDEX functions to work

The last method on our list will certainly not be the easiest, but quite simple and easily repeatable. To implement it, we will again use the array formula, and also use it in an effective and useful symbiosis. You can find out more about how these functions work in my separate articles.

And for our data search in the Excel table we will use the following formula:

=(INDEX(D2:D11,MATCH(1,(B2:B11=G3)*(C2:C11=G2),0)))

What is she doing, so big and incomprehensible... Let's look at it in terms of several blocks or stages. The formula for the function looks like this MATCH (1;(B2:B11=G3)*(C2:C11=G2);0) and the following happens to the value in the cell G3, values ​​from the range are sequentially compared B2:B11 and if the conditions match, we get the result TRUE, and if there are differences we get LIE. The same process occurs for the value G2 and range C2:C11. After comparing these arrays which consist of arguments TRUE And LIE, comparisons are made to match the value 1, this TRUE*TRUE, all other combinations will be ignored.

Now that the MATCH function has found a value in the array that matches "1" and indicated its position in the sixth line, which means that the argument was passed to the INDEX function "6" for range D2:D11.

Well, to summarize, we can answer the logical question: “what should we do?” and “which method should I use?” You can use absolutely any method, but I would recommend choosing the most convenient, simple and understandable one for you. For example, I like to use tables that are easy to change and easy to work with and understand, which I advise you to do as well.

I really want these 4 ways to search for data in an Excel table to be useful to you, so that you can quickly and efficiently find the information you need. If you have anything to add to me, write comments, I will look forward to them, like and share this useful article on social networks!

If you search for wildcard functions, Google will show that VLOOKUP is much more popular than the INDEX function. This is understandable, because in order to give the INDEX function the same functionality as VLOOKUP, you need to use another formula - SEARCH. For me, it was always a challenge to try and master two new features at the same time. But they give you more power and flexibility in creating spreadsheets. But first things first.

VLOOKUP() function

Let's say you have a table with employee data. The first column stores the employee’s personnel number, the rest - other data (full name, department, etc.). If you have a personnel number, you can use the VLOOKUP function to return certain information about the employee. Formula syntax =VLOOKUP(lookup_value, table, column_number, [interval_lookup]). She tells Excel: "Find in table a row whose first cell matches search_value, and return the value of the cell with the serial number column_number».

But there are situations when you have the name of an employee and you need to return the personnel number. In the figure in cell A10 is the employee’s name and you need to determine the personnel number in cell B10.

When the key field is to the right of the data you want, VLOOKUP will not help. If, of course, it were possible to ask column_number-1, then there would be no problems. One common solution is to add a new column A, copy the employee names into that column, populate the personnel numbers using VLOOKUP, save them as values, and delete the temporary column A.

INDEX() function

To solve our problem in one step, we need to use the INDEX and MATCH formulas. The challenge with this approach is that it requires you to apply two functions that you may never have used before. To simplify understanding, we will solve this problem in two stages.

Let's start with the INDEX function. A terrible name. When someone says “index”, not a single association arises in my head about what this function does. And it requires three arguments: =INDEX(array, row_number, [column_number]).

In simple terms, Excel goes into an array of data and returns the value at the intersection of a specified row and column. It seems simple. Thus, the formula =INDEX($A$2:$C$6,4,2) will return the value found in cell B5.

In relation to our problem, to return the employee's personnel number, the formula should look like this =INDEX($A$2:$A$6;?;1). It looks like nonsense, but if we replace the question mark with the MATCH formula, we have a solution.

MATCH() function

The syntax for this function is: =MATCH(lookup_value, lookup_array, [match_type]).

She tells Excel: "Find lookup_value in the data array and return the row number of the array in which this value occurs." Thus, to find which line the employee’s name is in cell A10, you need to write the formula =MATCH(A10, $B$2:$B$6, 0). If the name "Colin Farel" is in cell A10, then MATCH will return the 5th row of the array B2:B6.

Well, basically, that's it. The MATCH function tells the INDEX function which row to look for a value. Replace the question mark in the INDEX formula with the SEARCH formula and you get the equivalent of a VLOOKUP with the ability to search for data located to the left of the key column. It will turn out like this:

INDEX($A$2:$B$6,MATCH(A10,$B$2:$B$6,0),1)

At the beginning it was unusual, there was a huge temptation to insert another column and work as always. But over time, using the INDEX function became a habit. It turned out to be faster and require less manipulation. So the next time you want to set a column number to a negative number in a VLOOKUP, use a combination of two strange functions, INDEX and MATCH, to solve your problem.