Excel function learning: How to use the search function INDEX()

This article will take you through the INDEX function! INDEX is also a member of the search family. Due to its powerful coordinate positioning function, sometimes VLOOKUP has to step aside!

1. Understanding the INDEX function

Index function: In a given cell range, return the cell at the intersection of a specific row and column The value or reference of the cell.

Function structure: index (cell area, row number, column number)

Area, row number, column number, it’s like aiming through coordinates. Just like the animation below, find the column, find the row, click and hit!

We want to find "囡", and you can see that its coordinates are row 4 and column 3.

So the formula: =INDEX(B2:G11,4,3) can get "囡".

2. Basic usage of INDEX function

1. Extract values ​​from a single row or column: only one coordinate value is required

If the given area is a single row or column, then the coordinates do not need two numbers, only one.

For example, we now need to get "Li Hui" from A17:A21 in F17.

Enter the formula: =INDEX(A17:A21,2).

For another example, we need to obtain Li Hui’s basic salary from A18:D18 in G17.

Enter the formula: = INDEX(A18:D18,4).

2. Extract values ​​from a multi-row and multi-column area: two coordinate values ​​in rows and columns are required

This point will not be listed. . This is how you look for the word "囡" in the front.

As can be seen from the above example, INDEX returns a value through coordinates, like a precision-guided missile, hitting where it points (returning where). However, purely manual coordinate checking and then inputting the coordinates is not in line with "modernity". In actual operation, the data often consists of dozens of columns, dozens of rows or even tens of thousands of rows. At this time, it is too unrealistic for us to manually check the coordinates and enter the coordinates as needed. Therefore, INDEX needs assistants and a team to conquer the world.

3. Practical usage of INDEX

1. Group with assistants COLUMN and ROW: realize semi-automatic value search

(1) Grouping with COLUMN can continuously return multiple data of the same row

For example, we need to continuously obtain the name, age, and job number C23 from the table Entry Time.

In the data area A17:E21, the job number C23 is located in the 3rd row. The column numbers of name, age, and joining time are 2 and 3 respectively from left to right. ,4. We can use COLUMN (B1) to replace 2, 3, and 4 to achieve semi-automatic effects. The formula is as follows:

=INDEX($A17:$E21,3,COLUMN(B1))

Then pull right to fill in.

#The entry time obtained is a number, just change the format to a short date.

(2) Grouping with ROW can continuously return multiple data in the same column

For example, below, we use the formula: =INDEX(A$17:E$21, ROW(A3),2) Drop down and fill in to get the names of three work numbers.

(3) Group together with COLUMN and ROW at the same time

For example, we can use the formula: =INDEX($A $17:$E$21,ROW(A3),COLUMN(B1))Pull right and drop down to fill in the name, age, and joining date of job numbers C23, C08, and C10.

By forming a group with COLUMN and ROW, a semi-automatic effect is achieved. As long as the values ​​are continuous and regular, it can be achieved with INDEX ROW COLUMN.

For example, we need to get values ​​in alternate rows and columns to obtain the names and joining dates of job numbers C15, C23, and C10. The formula is:

=INDEX($A$17:$E$21,ROW(A1)*2-1,COLUMN(A1)*2)

Then right Just pull down to fill.

Semi-automatic is much easier than completely manual coordinate checking and inputting coordinates, but the reason why it is called semi-automatic is that it still requires manual work to find the rules of the data. If the data patterns of values ​​are complex or irregular, we cannot be semi-automatic. At this time, you need to form a team with the big assistant MATCH to perform fully automatic work.

2. MATCH group with the assistant: realize fully automatic value search

(1) INDEX MATCH group

The following data search rules are messy. We don't need to find the rules ourselves, just leave everything to MATCH.

Enter the formula in C28:

=INDEX($A$17:$E$21,MATCH($B28,$A$17: $A$21,0),MATCH(C$27,$A$16:$E$16,0))

Then pull down to the right and fill in the formula.

Using the MATCH function to query row and column positions in a fixed area based on conditions completely replaces manual search for coordinates or data patterns, achieving full automation.

(2) The difference between INDEX MATCH and VLOOKUP MATCH

Input formula:

=VLOOKUP($B28,$ A$17:$E$21,MATCH(C$27,$A$16:$E$16,0),0)

Pull right and drop down to fill in.

In terms of formula length, VLOOKUP MATCH is simpler than INDEX MATCH. So why do we still need INDEX MATCH? The reason is that the INDEX function can find the data as long as it receives the row and column coordinate values. There is no difference between forward search and reverse search at all. VLOOKUP will not work. By default, it can only achieve forward search, that is, it can only search from left to right in the search area, but not from right to left. If VLOOKUP wants to implement a reverse search from right to left, it needs to use the IF function or CHOOSE function to construct a new search area.

For example, we need to check the job number by name, as follows:

Use the INDEX MATCH combination to directly write the formula: =INDEX(A$17:B $21,MATCH(G17,B$17:B$21,0),1), and then pull down to

If you use VLOOKUP to search, because it is For reverse search, you need to use the IF function to reconstruct the search area. The formula becomes:

=VLOOKUP(G17,IF({1,0},B$17:B$21,A$17: A$21),2,0)

So, in comparison, when searching forward, both INDEX MATCH and VLOOKUP MATCH can be used, and VLOOKUP MATCH is relatively more efficient. Simple; when performing reverse search, INDEX MATCH is the simplest, especially when the reverse search area has three or four columns of data, INDEX MATCH is the best choice.

Okay, after answering the questions in Function Class 2, we continue to look at the practical grouping of INDEX.

3. Form a group with special guests SMALL and IF plus size assistants: realize one-to-many search

The formula format after grouping is =INDEX (Search area, SMALL(IF(),ROW()),MATCH())

For example, as shown in the animation below:

The formula is very long:

=INDEX($A$2:$D$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20), 99),ROW(A1)),MATCH(F$3,$A$1:$D$1,0))

If you apply the error-proof IFERROR function, it will be even longer:

=IFERROR(INDEX($A$2:$D$21,SMALL(IF($C$2:$C$21=$F$2,ROW($1:$20),99),ROW(A1)) ,MATCH(F$3,$A$1:$D$1,0)),"")

Such a combination formula, we often call it the snake oil formula, is mainly used for one-to-many search.

Ok, that’s all we’ve introduced about the practical usage of INDEX. The INDEX function has the advantage of using coordinates to accurately obtain values, but it lacks the function of automatically finding coordinates based on conditions. It is lame, so in actual combat it requires the assistance of an assistant to find coordinates. It's a precision-guided missile in function, and it's a Crip, a powerful Crip!

Related learning recommendations: excel tutorial

The above is the detailed content of Excel function learning: How to use the search function INDEX(). For more information, please follow other related articles on the PHP Chinese website!