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

The original title: "VLOOKUP stand aside, this is the most awesome search method in Excel?" "

Before the beginning of the term, the teaching staff of some schools need to arrange the corresponding classes for the teachers of each subject in the next semester. Therefore, the table of the following figure is made.

In order to easily view each class, each subject, and the corresponding teacher, he also needs to match the data on the left side of the table below to the corresponding right table.

How should this be done?

Directly use our common matching function, the Vlookup function?

Because the data source table is not a standard one-dimensional table. The Vlookup function is hard to do.

So, let's try the Lookup function.

Function method, let's first take a look at the data source.

For Lookup function query matching, you need to find conditional areas of the same area size (single row / single column), as well as return areas.

As shown in the following figure, we can see that conditional area 2 is not a single-column data area.

So if we integrate conditional region 2 into one column, can we use the lookup function Lookup? 🤔

As shown in the following figure, the conditional region 2 is merged into one column using the Textjoin function.

For the need to match data results, conditional regions, return regions, you can use the classical use of the Lookup function.

The routine formula of Lookup function multi-conditional query (see the end of the article for a specific explanation):

= LOOKUP (condition 1 = condition 1) * (condition 2 = condition 2) * … * (condition n = conditional region n), return region) involves the use of Lookup fuzzy matching, for example, to find out whether there is a 901in 901902903, in the Excel function, we usually use the Find function.

Let's say we need to find the location of class 901.

As shown in the following figure, using the Find function, if the return value is numeric, there is a class 901, and if an error value is returned, it does not exist. Later, we can use the Isnumber function to determine whether there is a numerical value, that is, whether there is a class 901.

PS: Isnumber (value), returns True if the parameter is a numeric value, False otherwise.

Now that the auxiliary columns are done, let's try to use Lookup to find class 901 and the subject is Chinese.

Classic formula:

= LOOKUP (condition 1 = condition 1) * (condition 2 = condition 2), return area) enter the formula for the corresponding region:

= LOOKUP (1JOO / (($ISNUMBER ($H$2:$H$59), $A$2:$A$59) condition 1: language

Conditional area 1: subject column ($B$2:$B$59)

Conditional area 2:ISNUMBER ($H$2:$H$59)

Return region: last name column ($A$2:$A$59)

Merge the Find function column of lookup class 901 into the formula:

= LOOKUP (1JOO / (($Baud2VOBLING Bamboo 59 = "language") * ISNUMBER (FIND ("901", $G$2:$G$59)), $A$2:$A$59)

Enter the complete formula into the right table of the following figure, find the value Chinese and class 901, and replace them with the corresponding mixed references:

= LOOKUP (1 B$2:$B$59=J$1 0 / (($B$2:$B$59=J$1) * ISNUMBER (FIND ($I2 camera / FIND ($I2 camera / FIND)), $A$2:$A$59) replace "Chinese" with junk 1

Replace "901" with $I2

It's done here.

The above is done with auxiliary columns, or if you want a formula to complete. As shown in the picture below, interested partners can do some research on their own.

= LOOKUP (1 B$2:$B$59=I$1 0 / (($B$2:$B$59=I$1) * MMULT (--($C$2:$F$59=$H2), ROW (1:4) ^ 0)), $A$2:$A$59) the PS:MMULT function is a matrix function, which is also used to merge multiple column judgments into one column.

What we did earlier with auxiliary columns and functions, right, is very complicated.

The main reason for the complexity is that our data source is a two-dimensional table, not our favorite one-dimensional table in finding matches.

Wouldn't it be much easier to convert the data source into an one-dimensional table first? 🤔

Next we use PowerQuery, only three steps, move the mouse can be easily done.

PS: PowerQuery (PQ for short) is a plug-in that comes with Excel2016 and above. The Excel2013 backend replies to [plug-in] and uses it after self-installation.

PQ method, let's look at the specific operation.

When it comes to PQ, it is natural to import the data into the PQ editor first.

Select the data source area-under the data tab-click [from Table / region]-create table dialog box and click the [OK] button.

❶ uses inverse perspective columns to convert data sources into one-dimensional data tables.

Select all the class columns and right-click [inverted perspective column].

Delete the property column, and this is the one-dimensional data table.

The operation diagram is as follows:

❷ perspective column, class as row, subject as column, name as value

Select the account column-under the conversion tab-click Perspective column.

Value column: name

Aggregate value functions: do not aggregate

The operation diagram is as follows:

❸ changes the order of columns

We found that the listing at this time is not in accordance with the language, mathematics, which we need to list. In order.

Just change the second parameter in the formula to the subject list, and you can do it!

Original:

= Table.Pivot (deleted column, List.Distinct (deleted column [account]), "account", "name") changed:

= Table.Pivot deleted columns, {"Chinese", "Mathematics", "English", "Physics", "Chemistry", "Politics", "History"}, "subject", "name") the operation diagram is as follows:

When we get here, we're done.

Is it super simple duck?

Finally, this paper explains the table requirements of class-by-class query, which are common to teachers. The main difficulty lies in two-dimensional table query two-dimensional table data sources.

In the Lookup function method, many columns are merged into one column by merging functions, and the Find function is used to find the location, return the value to indicate existence, and return the error value to indicate that it does not exist.

The matching result is queried by using classical function set Lookup (1Power0 / (condition 1 = conditional region 1) * (condition 1 = conditional region 1), return region).

For more information about the classic usage of Lookup, you can read as follows:

Using LOOKUP in this way is a hundred times easier than general search!

In the PQ method, we complete our table requirements with a click of the mouse through [inverse perspective column] + [perspective column].

For the conversion of two-dimensional data to one-dimensional data, the inverse perspective column of PQ is a powerful function. With an one-dimensional table, it is not difficult for us to query and match the data.

Excel is everywhere in life, and Excel is used everywhere.

If you still have a headache for Excel and occasionally stay up late to work overtime for Excel, you feel like you know how to read the tutorial, but you still have a black eye.

At this time, you need to learn Excel systematically!

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