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

Farewell to VLOOKUP! Find data, this simple and efficient function is easy to use!

Hello, my friends. I am a farmer, a farmer who specializes in "difficult problems"~

I believe that in our daily study and work, we often extract the data in the worksheet according to the corresponding conditions ~

For example:

From the sales data table, including commodity name, model, province, sales volume, unit price, etc., extract the corresponding sales volume according to commodity name and model;

From the report card, including student name, course name, score, etc., extract corresponding score according to student name and course name;

From the employee data table, including name, position, gender, household registration, etc., extract the corresponding position according to the employee name, etc.

In response to this demand, I guess many friends will think of the VLOOKUP function, right?

But in fact, there is also a lookup reference function, which can also easily handle such data requirements. This is the INDEX function to be introduced today ~

INDEX can solve some basic data lookup reference requirements by itself, however, when combined with other functions, its power is multiplied!

In order to better and more flexible use INDEX function, in this article we first learn the basic usage of INDEX function ~

INDEX returns a value or reference to a value in a row or range. The syntax is as follows:

=INDEX (query's single row or range, row number, column number)

If the data area is a single row or column, the corresponding parameter row number or column number is optional.

Extract data from a single area. The following table is an example. If you want to find cell data in row 2 and column 3 in the area A2:G21, you can enter:

=INDEX(A2:G21,2,3) can output cell data: Qiu Erbing.

Single line extraction data. If you want to find the data in column E2:E21, row 2, you can enter:

=INDEX(E2:E21,2) can output cell data: Outline of Modern Chinese History.

Separate data extraction. If you want to find the data in column 3 in row A2:G5, you can enter:

=INDEX(A5:G5,3) can output cell data: Wang Yihu.

Multi-region extraction data. If you want to query data in two discontinuous regions, you need to use INDEX function multi-region extraction ~

For example, query the data in row 4 and column 1 in the second area of A1:C11 and D1:E11.

The specific formula is as follows:

=INDEX((A1:C11,D1:E11),4,1,2)

where INDEX function,

The first parameter is the two selected data regions, which are enclosed in brackets and separated by commas.

The second parameter is the line number;

The third parameter is the column number;

The fourth parameter is the region where the query data is located (here we chose the second region).

Back to results: New Media and Cybersecurity.

PS: comma: union operator, which merges multiple reference ranges into a single reference range; space: crossover operator, which generates a reference range of parts common to two reference ranges.

Interlaced extract data. Many times, we also encounter the need to extract data in a fixed number of rows.

For this kind of data, we can also use INDEX function processing.

The key is to construct the arithmetic series with a tolerance of 2 from the second parameter of INDEX function. The specific formula is as follows:

=INDEX(B:B,2+2*(ROW(A1)-1))& where 2+2*(ROW (A1)-1) is an arithmetic sequence.

ROW function returns the row number of the cell, such as ROW (A1) plus the row number of A1, then 2+2*(ROW (A1)-1)=2+2*0=2, with the pull-down will generate 2, 4, 6, 8, 10... Arithmetic sequence.

Finally, the INDEX function is used to extract the corresponding cell data.

"" is used to cause INDEX to return null instead of zero when referring to empty cells.

Okay, let's go one step further and build a data table where names and subjects correspond to each other.

We just need to replace the first 2 with a function that changes as we pull down: COLUMNS (A1)(horizontal pull unchanged, vertical pull changes + 1), so we can construct two arithmetic series, extracting the name column and the account column respectively.

The specific formula is as follows:

=INDEX($B:$B,COLUMN(A1)+2*(ROW(A1)-1))&

Of course, there is a simpler way, not to go into detail here, just to demonstrate with animation:

From the above basic application point of view, INDEX function usage is actually very simple, if you want to play its power, or in combination with other functions, especially its "golden partner"-MATCH function.

The MATCH function becomes its "golden partner" mainly because the result of the MATCH function can return the relative position of the data, which perfectly serves as the parameter of the INDEX function.

So, let's first introduce the basic application of MATCH function.

MATCH function MATCH function returns the relative position of the content in a single row or column (referring to the position of the content in the search area, not the position in the worksheet). The syntax is as follows:

=MATCH (Find object, Find range, Specify matching method)

Some small partners may have to ask, if in the multi-row or multi-column area to find it?

This is done with the help of the & hyphen.

First, use the connector & to merge the search objects composed of multiple conditions into a new condition;

Then use connectors to merge the information in the rows or columns where the condition is located into a new query area. The formula is as follows:

=MATCH (combined search object, search range 1 & search range 2, specify matching method)

Matching method:

1, indicating that the function will find the maximum value less than or equal to the query value;

0, means that the function will find the first value equal to the "query value", that is, an exact match;

-1, which means the function will find the minimum value greater than or equal to the value to be queried.

For example, to find the relative position of data that meets the two conditions of "Wang Yihu" and "Outline of Modern Chinese History," you need to combine the two conditions into a new search object with &.

The specific formula is as follows:

=MATCH(I11&J11,C2:C21&E2:E21,0) can output the relative position of the data in the area: 5.

Note:

If there are multiple lookup values in the query area, MATCH returns only the first occurrence of the lookup value;

MATCH function is case insensitive when looking for text values.

INDEX and MATCH function basic use method is not complicated, but we need to deeply understand its syntax, in order to flexibly combine with other functions, to solve the daily encountered various data needs.

Below, let's review the relevant knowledge points:

INDEX function ① Single area extraction data:

INDEX query area, row number, column number

② Single line extraction data:

INDEX (query row, row number)

③ Single column extraction data:

INDEX query column, column number

④ Data extraction from multiple regions:

INDEX (Region 1, Region 2), row number, column number, selected region number)

5. Number of alternate lines

Construct arithmetic series

MATCH function.① Basic applications

MATCH (Find object, Find range, Specify matching method)

② Multi-condition application

MATCH (combined search object, search range 1 & search range 2, specify matching method)

Learn the basic use of INDEX and MATCH functions, we will be able to solve the work and learning, some simple data search and extraction needs.

As we said at the beginning, the INDEX function is required to implement more complex requirements.

Such as reverse lookup, multi-condition lookup, and bidirectional lookup, INDEX needs to be used in combination with other functions.

This article comes from Weixin Official Accounts: Autumn Leaf Excel (ID: excel100), Author: Farmer, Editor: Yali Zi, Zhu Lan

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.