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

Hello, everyone! Today our topic is cross-query.

What is a cross query? Let's first look at an example:

In the table, we arranged for 50 students to form a phalanx, which consists of 5 columns and 10 rows.

Now, as long as the instructor shouts out the number of rows and columns, the corresponding students will answer "to" in a loud voice.

For example, the instructor shouted: "row 4, column 3"!

"here we are! Shi Mei shouted.

This is a typical cross-query problem.

So, how to quickly query the results according to the row and column headings in the table?

Usually, your first reaction is to use a function nesting formula to do it.

There are four commonly used formula combinations: isn't there a simpler way?

Well, hey, there is!

It only takes two steps, and you don't need a complex function formula, and you can do it with a space.

❶ vlookup+match

The formula is as follows:

= VLOOKUP (I3 Persona2 index+match F11 match (I2 Persona1 index+match)

The formula is as follows:

= INDEX (B2MATCH F11 match), MATCH (I2Parade F1Magne0)) offset+match

The formula is as follows:

= OFFSET (A1 match), MATCH (I2 recorder B1 MATCH 0), 1 match (I3 Personality A11 indirect+match)

The formula is as follows:

= INDIRECT ("R" & MATCH) & "C" & MATCH (I2 magnificent A1 MATCH F1Magi 0), FALSE) do you think your head is very big? Each of these formulas is longer and more complex.

01. Batch naming first gives each row of the square matrix a name for each column. The conventional naming method is to select the corresponding row or column, and then enter a name in the name box, as shown below:

However, with so many rows and columns, it is too troublesome to set them one by one, so let's use a simpler method:

❶ selects the entire table area (including row and column headings).

❷ clicks the create from selection button in the Formula tab.

❸ confirm the first line and check the box in the leftmost column and click OK.

In this way, we have completed the batch naming.

In the name Manager of the Formula tab, we can view these names and the corresponding reference areas.

02. Write formulas

It's amazing!

I believe I do not have to say more, you can see how simple this formula really is.

Enter the equal sign, then enter the names of the corresponding rows and columns, type a space between the two names, and the formula will be written.

Note: in a function formula, like a colon, a space is a reference symbol. Unlike the colon, the colon refers to the range between two cells, while the space refers to the part where the two regions overlap.

Well, at this point, we have basically completed the task of cross-query.

However, there is one drawback: the current row names are entered manually, which is a little short of what we first showed.

Let's try again and enter this formula using a cell reference:

Enter the equal sign, click the column name cell above, click the space, then click the row name cell, and press enter.

It's a pity that we overturned!

Why is it successful to manually enter the column name, but fail to directly reference the contents of the cell?

Because when you type in by hand, the formula will recognize these names as the range we named, while directly referring to the cell, the formula will recognize these names as text.

So, here we still lack a key element, it is a function, can you guess what function it is?

Don't sell it! What we lack is the indirect function.

The indirect function converts text to a specified reference.

In human terms, the indirect function can convert the text into an address (provided the address is valid), because we have named each row and column at the beginning, so these addresses are valid, we only need to use the indirect function to reference these address names, and they can play their real role.

Therefore, we have to modify this formula a little bit.

It's very simple, just add the indirect function in front of the two cells, and that's fine.

How's it going? have you learned?

At the end, we will list all five methods of cross-query at the end:

❶ vlookup+match

The formula is as follows:

= VLOOKUP (I3 Persona2 index+match F11 match (I2 Persona1 index+match)

The formula is as follows:

= INDEX (B2MATCH F11 match), MATCH (I2Parade F1Magne0)) offset+match

The formula is as follows:

= OFFSET (A1 match), MATCH (I2 recorder B1 MATCH 0), 1 match (I3 Personality A11 indirect+match)

The formula is as follows:

= INDIRECT ("R" & MATCH) & "C" & MATCH (I2 Magic A1 MATCH F1), FALSE) ❺ batch naming + indirect

The formula is as follows:

It is up to you to decide which one to use INDIRECT (I2) INDIRECT (I3).

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Zhang Kaiyuan, Editor: 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.