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

Behind the headlines! Hello everyone ~ here is the workplace struggle satellite paste trying to introduce Excel knowledge in the most easy-to-understand language.

The VLOOKUP function is no stranger to everyone, is it?

We know that its function is to find data by column and return the value corresponding to the query sequence.

Is a very easy to use and commonly used function.

This is not, just learned Excel colleague Xiao Ke, can also be very proficient in using VLOOKUP to work.

"Ah! I can't find out! "

Behind me came the sound of Xiao Ke scratching madly.

Ah, this. Hit me in the face so suddenly...

"the last time you taught me to make a payroll, you also used VLOOKUP to calculate personal income tax. Why do you keep reporting mistakes this time? "

"I have checked it all. The look-up value, the absolute reference and the number of columns are all right. "

= VLOOKUP (B3 recordings Elaine 15Rose Fleming 18pr 2 pas 0)

I leaned over to see her formula.

Well, it turns out that she used the wrong matching condition!

We usually use exact matching, always subconsciously set the last parameter to "0", over time forget its meaning, but it is not good-looking!

The four parameters of VLOOKUP are: lookup value, lookup area, number of columns, and matching criteria. The matching conditions can be divided into two types: exact matching and approximate matching.

In the formula, 0 or FALSE is an exact match, and 1 or TRUE is an approximate match.

This table, in fact, should use approximate matching!

Xiao Ke, I asked you to catch up on Excel lessons, such as Akiba's 3-day Excel training Camp.

Otherwise, if you have learned the formula, but will not use it flexibly, it does not mean learning in vain!

1. The search logic for approximate matching is: if the data cannot be accurately matched, a maximum value less than the lookup value is returned.

For example, in Xiao Ke's task, the formula should be written as follows:

= VLOOKUP (B3 recordings Elaine 15Rose Fine 18pas 2pas 1)

Analysis: take Zhang Lide's salary as an example, the search value is "19000" and the matching area is "$E$15:$F$18". Because there is no value "19000" in the area, the maximum value less than this value, that is, "17000", is returned, corresponding to 20%.

Note: when we use approximate matching, the search area must be sorted in ascending order.

2. Exact match. So when do you use an exact match?

In fact, most of the time when using VLOOKUP is!

Remember the special case of approximate matching, and the rest will be easy to do.

Multi-conditional search, reverse search, check data in different order, one-to-many search, these are all OK!

= VLOOKUP (L2bike M2 precinct if ({1jue 0}, H1VO H11across I1JL J11), 2J0

= VLOOKUP (G22Magi if ({1jue 0}, B22JZ B27 pr A22JI A27), 2Jing 0)

= E31-VLOOKUP (D31ZHANG31JULING BUBTHY 39Magne2Pol 0)

= VLOOKUP (ROW (A1), $Lhasa 12 rime 20 pas 3 pas 0)

3. Fuzzy matching Let's talk about an additional fuzzy matching which is easy to be confused with approximate matching.

Although ambiguity sounds similar to the approximate meaning, the former is a search achieved by using wildcards, which can be of great help when the name entry is not standardized!

For example, look up the price of the goods here:

= VLOOKUP ("*" & O22 & "*", $Lhasa 21, VO22 & "*")

Formula parsing:

"*" is a wildcard that represents multiple arbitrary characters, and the search value "*" & O22 & "*" here is "the contents of cell O22, with any characters on the left and right sides."

In this way, even if the content filled in the sales record is incomplete, the corresponding price can be found.

Besides, "? "is a wildcard that represents a single character and is used according to the actual situation"? ", or only add wildcards on one side of the lookup value, it can also be blurred matching ~

4. At the end of the day, we introduce two matching conditions of VLOOKUP, adding a fuzzy matching ~

Now have you figured out when to use 0 and when to use 1?

In retrospect:

❶ approximate matching uses "1"

❷ exact match uses "0"

❸ fuzzy matching uses wildcards.

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

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.