Shulou(Shulou.com)11/24 Report--

Today we learn about a very useful query function-the index function.

1. The main function of the cognitive function is to find the target object according to the location in the area.

To make good use of it, you only need to remember three tips:

Which area should I look for?

What line is it in?

Which column is it in?

Let's take a look at the syntax of the INDEX function:

= INDEX (data region, number of rows, number of columns)

2, row positioning search case is an examination score table, now to quickly find the name of the fourth examinee in the list of candidates, what should be done?

❶ type = INDEX in the cell, press [Tab] to quickly insert the function and parentheses "(".

❷ recalls 3 sentences of formula and completes the corresponding operation according to the formula.

Which area should I look for? Directly select the [A2:A7] cell of the examinee list and put a comma in English.

What line is it in? We are looking for the name of the fourth examinee, so in line 4 of the selected area, enter a number [4]. When you have finished typing, put on the anti-parentheses and press the enter key, and the result will be found smoothly.

PS: column issues are not involved here, so the third parameter does not need to be filled in.

The complete formula is as follows:

= INDEX (A2 A7jol 4) Let's look at the second case: what is the fifth exam subject? 、

❶ enter = INDEX in the cell with (parentheses).

Which area should I look for? Directly select the subject row [B1:G1] cell.

Which column is ❷ in? -- for the second parameter, we enter the number [5] directly to indicate that we are looking for the data in column 5 of the selected area. When you have finished typing, put in anti-parentheses, press enter, and the result will be displayed.

PS: there is only one line here, and there is no question of the number of rows.

The complete formula is as follows:

= INDEX (B1index G1J 5) Let's summarize the usage of the index function. The index function can find the target according to the location. If the search range is only one row or one column, then the corresponding number of rows and columns becomes optional and can be ignored.

3. Multi-conditional search Let's look at the third case: now we want to inquire about the examination results of the fourth subject of the second examinee, what should we do?

For this problem, the search can not be limited to one row or one column, we still try to use 3 phrases to solve this problem.

❶ first enter = INDEX in the cell, followed by "(" parentheses.

In which range to find-select [B2:G7] cell, put an English comma ",".

In which line does ❷ find the second candidate, so in the second line of the selected range, enter a number [2] and put a comma in English.

In which column-the fourth subject score is in the fourth column, enter the number [4], put the anti-bracket ")", press enter, and you can see the query result.

The complete formula is as follows:

It should be noted that the number of rows and columns should be adjusted accordingly, depending on the selected range. = INDEX

Suppose my first parameter is not all the score areas, but the whole table, at this time, how to choose the number of rows and columns?

❶ We first clear the previous data, enter = INDEX, the first parameter selects the entire table [B2:G7] cell, marked with a comma ",".

Now, what we are looking for is the result of the second examinee and the fourth subject.

The second candidate for ❷ is in the third row of the table, so enter the number 3 for the second parameter.

❸ fourth subject score, in the fifth column of the selected range, so the third parameter to enter the number [5], with anti-parentheses ")", press enter to view the results.

The complete formula is as follows:

Through this case, have you found that the number of rows and columns is not fixed, so we must fill in correctly according to the selected range in the operation.

Have you learned all the skills of using the index function? Is it super simple? go to practice and try it.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Tian Tian

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.