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

In the field of Excel functions, the most famous one is to find the reference function V.

About it, there is also a folk saying that it is loved by everyone, the flowers bloom, and the car sees a flat tire.

For example, the most commonly used data lookup reference at work must be it.

1. The description of the problem is shown in the following figure: it is a promotion and salary increase table for a group company.

Now it is necessary to quote the post salary, salary scale, post subsidy and total of some people in the group company into a new table.

Normally, we would write the formula like this:

[L2] enter the following formula for the cell:

= VLOOKUP ($K2) ($K2)

Formula parsing:

Take the contents of the cell [K2]: "Wusheng" as the search value, look in columns B to H of the data source area, and return the corresponding value of column 4 if found.

If you drag a cell to the right of the formula, COLUMN (D1) (the number 4) becomes COLUMN (E1) (the number 5), and so on.

However, have you noticed that he only returns the value that each person appears for the first time? what if we only need to return the data after the promotion and raise?

At this time, it was his second brother, Brother Pig (Hlookup), to play.

Let's take a look at its performance.

2. Solve the problem

Hlookup function and Vlookup function are twin brothers.

Vlookup is a vertical lookup that returns the corresponding value of the column.

Hlookup, on the other hand, looks horizontally and returns the value corresponding to the row.

We enter the following formula in [L2] cell:

= HLOOKUP (Lady1 recordings Bent2purge) Hand14match ($K2menmeng Bron2purge Blossom 14Phone0) + 1JEI 0)

Formula parsing:

This function also takes four parameters, exactly the same as vlookup.

The syntax is as follows:

The ❶ parameter is the lookup_value lookup value

The ❷ parameter is the table_array lookup zone

The ❸ parameter is the corresponding row value returned by row_index_num.

The ❹ parameter is range_lookup for precise lookup.

The search value is: [L1] cell content [post salary]

The lookup area is: $B$2:$H$14, the first column, which must contain the lookup value.

Returns the corresponding row value, which is matched by a Match

MATCH ($K2) ($K2)

The first parameter looks for the value "Wusheng" in the cell of value [K2].

The second parameter looks up the field [$B$2:$B$14], which looks in the range of cells in which the name is located, and returns the number of cells in that range. For example, if you rank 4, you will return the number 4.

The third parameter is 0, which means exact search.

Because the MATCH function also returns the number corresponding to the value found in the first check, letting its return value + 1 returns the number of rows after the promotion and salary increase. That is to say, 4: 1: 5.

So the result of the Hlookup function is as follows:

= HLOOKUP ("salary for the post", $Bath2VOV 14pl 5pl 0)

As shown in the following figure:

Note: the Match function also counts the blank cells in the merged cells.

3. Knowledge expansion some friends may wonder whether the Vlookup function must not be used to solve this problem.

Or some friends will be obsessed with using the Vlookup function, be sure to use it. This is not impossible.

However, here you need to combine the IF function and reconstruct the data region. Only if the lookup requirements of Vlookup are met can it return the value correctly. As shown below:

= VLOOKUP ($K2 Magi if ({1 min0}, $Baud 3 Vortex 13 recorder 14), 2 Magi 0)

Formula parsing:

Unlike the second parameter of a regular Vlookup function,

Here the IF function is used to reconstruct the two regions.

The second parameter of IF is the area to look for, that is, $B$3:$B$13

The third parameter of IF is the returned region, that is, E$4:E$14

And it's arranged in the wrong line. Exactly meet the requirement for the Vlookup function to return the first lookup value.

In addition: the third parameter of IF is: the returned area, that is, E$4:E$14, is a row absolute reference and a column relative reference, so that when you drag the formula to the right, you can return the correct column.

Because this formula is an array formula, you also need to press the triple key [Ctrl+Shift+Enter] to end (PS.Office 365 press [Enter]).

The above problem is solved here.

But, leaders' ideas can change from time to time.

For example, the requirements of current leaders are:

You need to see not only the detailed data after the promotion and salary increase, but also the data before the promotion and raise at the same time. What are we going to do? It looks like this:

In this case, if you want to use a function, you really need Brother Pig to play.

And the original formula basically does not need to be changed, only need to add a residual function + a function that returns the line number.

The formula is as follows:

Formula parsing:

Add MOD (ROW (A2), 2) to the third parameter. Where:

ROW (A2), which returns the line number 2 (that is, ROW (A2) = 2) as a function argument to MOD.

MOD (2), and then find the balance of 2: 0. (i.e.: MOD (2) = 0)

Then the value found by MATCH plus the value of 0 returns the value of MATCH.

Drag the formula down to return: MOD (ROW (A3), 2). Where: ROW (A3), returns the line number 3 (that is, ROW (A3) = 3) as the function argument of MOD.

MOD (3), and then find the balance of 3: 1. (i.e.: MOD (3) 2) = 1)

The return value of the final MATCH is + 1.

The key point here is:

Use the MOD function to replace the value of + 1 in the original formula, and adjust to return the numbers 0 and 1.

4. At the end of the day, we shared the usage of the Vlookup function second brother, Brother Pig Hlookup function.

In most cases, the search for Vlookup is very convenient and practical, but in some cases, it is not as good as its younger brother Hlookup function.

In addition, in the above example, although the Vlookup function combined with the IF function to reconstruct the data region can also solve the problem, in practice, it is not recommended.

Because, it is an array formula, in the case of more data, it will cause the table to be very stuck.

Therefore, try not to use array formulas, only use the combination of simple formulas, and the fewer combinations the better, the less the number of calculations, the better, this is the goal to be achieved in the work.

Well, that's all for today. If you like this article, you are welcome to like it and forward it!

In addition to the Hlookup function described above, there are many functions in Excel, such as Lookup, Xlookup, Sumif, and so on.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Der Spiegel in Heart

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.