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

Today, I hope to teach you how to use Vlookup to achieve data query through a small case. Let's take a look at it.

The boss often gives me a lot of data and asks to find a few specific people in it, which is really distressing. What can I do about it?

If you don't know how to use the function formula, you will be the last person in the office to get off work.

Since said here, that might as well let Xiao an take you to learn the application of the Vlookup function, from this query data, only need a formula can be done!

For example, the above is the company's personnel sales statistics.

A lot of data are stacked together, but only four people are screened out. It's troublesome to use [Ctrl+H] to find them one by one. What should I do?

Don't worry, let's take a look at how the Vlookup function formula works.

Function principle

Now that the formula is mentioned, it must be very troublesome to learn!

NoNoNo! absolutely wrong! In fact, as long as you know the parameters and operating mechanism, the doubt will be easily solved!

Here, Ann has sorted out how Vlookup works.

Let's take a look at the basic syntax of the Vlookup function:

= VLOOKUP (lookup_value,table_array,col_index_num, [range_lookup]) ▲ swipe left and right to view

Parameter 1: the value to find

Parameter 2: the area to find

Parameter 3: returns the column number of the data in the lookup area

Parameter 4: exact match / approximate match.

Then take a look at the picture ~

Let me explain to you how Vlookup works:

❶ determines the search object in the red box-- "May Day Fan".

❷ found the location of "May 1st Fan" in the blue box.

❸ specifies the number of columns in which the data is returned-- here is the third column, the sales column

❹ chooses an exact match-- because names and names correspond exactly.

The data involved in the above four steps is the source of the four parameters of Vlookup.

Function usage

Through the picture above, we understand the principle of using the Vlookup function, so let's enter the formula!

The formula is as follows:

= VLOOKUP (F2 Borel DJO) Formula parsing:

The cell of ❶ May Day is F2, so the first parameter is F2.

The data region of the ❷ lookup object is in columns B to D, so the second parameter is BRV D.

The sales volume that ❸ needs to return is in the third column in bvd, so the third parameter is 3

There is an exact one-to-one correspondence between ❹ names and names, so entering 0 represents an exact match.

Finally, double-click the fill formula.

The moving picture is shown as follows:

This is the end of the study of this case. I wonder if it will be of any help to you.

Of course, there are some things to pay attention to in this function.

Follow in the footsteps of Xiao an, let's have a look!

Matters needing attention

Selection of ❶ data regions:

The query object must be in the leftmost column of the search area

For example, if you select the ARV D column here, an error will occur when the formula runs.

❷ returns the judgment of column number:

Some students may have such a doubt:

The column of sales is column D, and column D is not the fourth column, so why write 3?

In fact, the column number returned here is the relative position.

The data area selected in this case is column BRV D, so column B is column 1, and column D is column 3.

So here the value of the Vlookup parameter is 3.

Selection of ❸ matching rules:

Why enter 0 for the last parameter? What does this mean?

0 means an exact match: since the name of May Day is exactly the same in column B, it is an exact match.

What is a fuzzy match?

Here, Xiao an offers another case.

You can see that the score on the right has no explicit counterpart in the data judgment rule on the left.

Therefore, we need to use fuzzy matching to query the score 22 in the data interval, and the result is returned to the data type corresponding to the minimum value of the interval.

This is the difference between exact matching and fuzzy matching.

PS: if fuzzy matching is used, the second parameter must be ascending.

The summary of this chapter

❶ Vlookup (find object, find area, return column number, match type)

The ❷ lookup object needs to be in the leftmost column of the lookup zone data

The ❸ return column number is actually determined relative to the lookup area.

The type of ❹ matching needs to be selected according to the actual situation.

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