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

The original title: "stronger than Vlookup!" This function combination, people who have used it all say incense, miss regret 100 million! "

Hello, everyone. I'm Xiaolan who knows a little bit about Excel.

In Excel, INDEX and MATCH functions are a pair of universal oil search function formulas, together, they can play a huge role!

The MATCH function is responsible for finding the location, then tells INDEX the location, and INDEX goes to get the stuff, just like the relationship between the detective and the police.

Let's look at a simple example.

Q: now you need to find the sales of grapefruit from the table on the left and fill in the E2 cell. What should I do?

A: first, use the MATCH function to find the position of the grapefruit in column A. the formula is as follows:

The result of = MATCH (D2 ~ A1 _ _ A30 ~ ~ 0) is 13.

This means that in the A1:A30 area, grapefruit is in the 13th position.

Then, outside the MATCH function, nest the INDEX function. The formula is as follows:

= INDEX, the result is 231, and the result is 231m. This is the sales of grapefruit.

The meaning of the formula is also easy to understand. In the B1:B30 area, find the data in line 13, which is 231.

The above is a relatively simple example, but you can also see the role that INDEX+MATCH can play in finding data.

Below, I have also prepared the classic usage of 6 INDEX+MATCH functions to help you master these two functions more quickly.

1. Reverse lookup: the result information of the search is in front of the desired search condition column.

As shown in the following figure: look for the number of column An according to the fruit name of column B.

Formula:

= INDEX (A2VOR A30 match (E2MagneB2Pho B30P0)) Formula: it is not much different from the forward search formula given above, and the only thing you need to pay attention to is the change of the search area.

INDEX+MATCH is easier to understand and easier to operate than the VLOOKUP function, which requires the help of the IF function to construct an array.

2. Cross-look for cross-reference data, such as the following figure, according to the "column number" and "row number", find the result information.

Formula:

= INDEX (B2VOR F11 match (I1 Personaa2GRO), MATCH (I2Parade F1JE0))

Description of the formula:

Use the first MATCH function to find the position of "row 2" in A2:A11, and the result is 2

Use the second MATCH function to find the location of "column 3" in B2:F2, and the result is 3

Finally, use the INDEX function to look in the B2:F11 area.

3. Look for the "sales" of January, February and March according to the "name" as shown in the figure below.

Formula:

= INDEX ($Bath2VOV) Dinghe30Magnum match ($F3LECHEROGRAPHY AUBLING), MATCH (GLT2LINGOLING BLING1GRAPHY DISCON0))

Formula description: compared with the previously mentioned cross-query formula, we can find that the principle of the two formulas is actually the same, except for the addition of absolute reference and mixed reference.

4. Multi-condition search, for example, look for "sales volume" according to two conditions "month" and "name".

Formula:

= INDEX ($Cong2VOV / CZO21 match (E3 / F3 / AZ / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2 / 2)

Description of the formula: use the & symbol to combine the search conditions and search areas together, and then search according to the forward search method.

5. Interval search returns the corresponding target value if the search value is in a certain interval.

As shown in the figure below, employees' bonuses need to be calculated according to the performance range.

Formula:

= INDEX (I2 I5 match (E2, {0pr 41pr 81121}, 1)) Formula shows that this formula requires attention to the application of the second and third parameters. If the third parameter is 1 or omitted, the MATCH function looks for the maximum value that is less than or equal to the first parameter, and the values in the second parameter must be sorted in ascending order.

6. Fuzzy lookup: by combining the use of wildcards ("? "question mark and" * "asterisk) to find the target value.

As shown in the following figure, you need to find out how much fruit is sold with the last character "melon" and a total of three characters.

Formula:

= INDEX (C2 C30 match ("?" & E2MagneB2DreB30pr 0)) Formula explains:

"? "question mark wildcard, which represents any single character. The "*" asterisk wildcard indicates that any number of characters are matched.

We used two here. "the question mark represents any two characters.

PS: question marks and asterisks must be in English half-width!

7. Write it at the end and give you a little summary ~

There are seven common ways to use the INDEX and MATCH functions:

Forward / reverse lookup

Cross search

One-to-many search

Multi-condition search

Interval search

Fuzzy search

The function of INDEX and MATCH is very powerful, I wish you all to master it as soon as possible!

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

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.