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

The title of the original text: "the 10 most classic uses of Vlookup function, people who have used it say incense, miss regret 100 million!" "

What is the most painful thing for an Excel rookie who suffers from functional phobia?

I don't know which function to use?

I know the name of this function, but I don't know how to write the formula!

For example, in the Excel world, the "dominant" search function Vlookup, the suspension window has long given the answer, but in the small white eye is a bunch of mysterious characters

So today, I've prepared 10 common classic uses of Vlookup to help you master this function more quickly.

Single condition search according to the condition "grape", look for "quantity".

Formula:

= VLOOKUP (E2Magic B2purC21JE2FLSE)

The basic syntax of Vlookup is shown in the following figure:

Hide garbled formulas:

= IFERROR (VLOOKUP (E2MagneB2VOLIZOC21MIRELLSE),)

The formula states: "" means that if there is an incorrect parameter in the previous VLOOKUP function, it is displayed as empty in the cell in which the result is displayed.

Multi-condition search looks for "grades" based on two conditions, "name" and "subject".

Formula:

= VLOOKUP (F3pm G3rect Arange Djor4 false)

Formula description: add auxiliary columns, combine the two conditions with & symbols to form a condition, and then use the basic syntax of the Vlookup function to find it.

Fuzzy search according to the "course abbreviation", find "the full name of the course".

Formula:

= VLOOKUP ("*" & C2 & "*", $Aguil1VOGRAPHY VOLLING 8MAGRING 8MAGNEL false)

Formula description: to find the abbreviation before and after the & connector plus a "*", you can replace the data containing the abbreviation.

Segment statistics according to the "amount", view the "range".

Formula:

= VLOOKUP (C2authentic Fidel2VOLGUE 9pyrrine 2jol true)

Description of the formula:

❶ uses the special use of Vlookup approximate matching, the fourth parameter, True, or 1

The start point of the ❷ interval must be the first column of the second parameter region

The second parameter area of ❸ needs to be locked with $to ensure that it is not offset.

One-to-many search looks for "sales" in January, February and March based on "name".

Formula:

= VLOOKUP ($A15), which is called "Aspell 2", "Draco 12" (B1), 0)

Description of the formula:

The result of Column (B1) is 2. When the formula is copied to the right, it can generate 3pr 4pm 5,.

Reverse search according to the "fruit" of column B, find the corresponding "number" of column A.

Formula:

= VLOOKUP (E2 Magi if ({1jue 0}, B2J B21 Magi A2JV A21), 2Ma false)

Formula description: use the IF function to construct an array, exchange the positions of column B and column A, and then use the Vlookup function to find it normally.

Cross-table lookup according to "fruit", look for "quantity" in multiple tables.

Formula:

= VLOOKUP (LOOKUP (1 INDIRECT ({"Fruit 1"; "Fruit 2"; "Fruit 3"} & "! avut A"), A2), {"Fruit 1"; "Fruit 2"; "Fruit 3"}) & "! avanza B"), 2) ▲ swipe left and right to view

Description of the formula:

Lookup value of A2:Vlookup

{.} contents of array: multiple worksheet names, separated by semicolons

Avatar A: find out which column the value is in each table. You need to determine whether this lookup value exists in that column of each table.

Search area of A:B:vlookup

2: the number of columns that return the value, with the name being the second column in the Apura B area.

Cross-query looks for "name" according to "column number" and "line number".

Formula:

= VLOOKUP (I2 for A2, F11, match (I1 for A1, F1 for F1, 0))

Description of the formula:

The result obtained with MATCH is the third argument of the VLOOKUP function, which returns the column of the searched area.

Find the last value to query the "last sales" of "fruit".

Formula:

= VLOOKUP (COUNTIF ($Cong2VOGRAPHY 11 Magi F2) & F2 recorder BLT1MULLING DLY 11MIT 3MIRELSE)

Formula description: on the use of Countif function as an auxiliary column to find the last value related to the practice, there was a previous article said: no matter how many times to find the data, with the Vlookup function to do so, super simple!

The role of Vlookup is very powerful, I wish you all to master it as soon as possible!

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