Excel data query INDEX and MATCH functions

This article brings you relevant knowledge about excel, mainly introducing the INDEX function and MATCH function. The combination of these two functions can complete all the search functions of the VLOOKUP function and HLOOKUP function. And it can realize queries in any direction and multi-condition queries, etc. I hope it will be helpful to everyone.

Relevant learning recommendations: excel tutorial

Let’s first talk about the role of the INDEX function:

## The #INDEX function is used to return content based on the specified row and column numbers in a range.

This function usually uses two or three parameters. The three parameters are written as:

INDEX (cell range, specified number of rows, specified number of columns)

For example, the following formula is used to return the cell at the intersection of row 3 and column 4 of the A1:D4 cell range, that is, cell D3.

=INDEX(A1:D4,3,4)

The two parameters are written as:

INDEX (a row or column of data, specify which element to return)

For example, the following formula is used to return the third element in the cell range A1:A4, that is, the content of cell A3.

=INDEX(A1:A4,3)

Next let’s talk about the role of the MATCH function.

MATCH function is used to search for specified content in the query area of ​​a row or column, and then return the relative position of the content in the query area.

MATCH function has three parameters. The first parameter is the search object, the second parameter specifies the search range, and the third parameter is used to specify the matching method. Usually 0 is used to indicate an exact match.

Commonly used writing is:

MATCH (who you are looking for, the row or column area you want to query, the matching method)

For example, the following formula returns the content of cell C3 At the position in A2:A10, the result is 7.

MATCH(C3,A2:A10,0)

Note that the 7 here refers to the location of the search content in the query area, not the worksheet Which row of . In addition, if the data range contains multiple lookup values, the MATCH function will only return the first occurrence of the lookup value.

Seeing this, some friends may have already figured it out. INDEX returns the content based on the position, and MATCH returns the position of the content in a row or column. Next, let’s talk about the common usage of these two partners:

Forward query

As shown in the figure below, based on the name in cell D3, query the corresponding query in columns A~B position.

The formula in cell E3 is:

=INDEX(B:B,MATCH(D3,A:A,0))

First use the MATCH function to find the position of "Little Dragon Girl" in cell D3 in column A, and the result is 8. Then use the INDEX function to return the content of the 8th element in column B. The result is the position corresponding to Xiao Longnu.

Reverse query

As shown in the figure below, according to the position in cell D3, query the corresponding name in columns A~B.

The formula in cell E3 is:

=INDEX(A:A,MATCH(D3,B:B,0))

First use the MATCH function to find the position of "Secretary" in cell D3 in column B, and the result is 5. Then use the INDEX function to return the content of the fifth element in column A. The result is the corresponding name of the secretary.

The formulas used for queries in two different directions are exactly the same. If you are interested, you can try how to write the search formula in the up and down directions.

Multi-condition query

In addition to conventional single-condition search, these two partners can also complete multi-condition queries.

As shown in the figure below, based on the position and age information in columns E and F, in the cell area of ​​columns A~C, search for the position corresponding to "Secretary" and the age "19" Name.

G3 cell formula is:

=INDEX(A:A,MATCH(E3&F3,B:B&C:C,0))

Note that this is an array formula. After the input is completed, you need to place the cursor in the edit box, hold down Shift ctrl, and then press Enter to complete.

First use the connector & to merge the job ages of E3 and F3 into a new condition.

Use the connector to merge the information in columns B and C into a new query area.

Then use the MATCH function to query the position of position & age in the query area 8.

Finally use the INDEX function to obtain the content of the 8th element in column A, and finally complete the data query of the two conditions.

Related learning recommendations:

excel tutorial

The above is the detailed content of Excel data query INDEX and MATCH functions. For more information, please follow other related articles on the PHP Chinese website!