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

Original title: "vomiting blood recommended!" This is an easy to use explosive Vlookup universal search formula that increases your efficiency by 10 times!

Excel has a feature that is easy to master.

Even if you are a regular user of Vlookup, the following three tips may not all be available.

You don't believe me?

Then come and have a look with me ~

Suppose you now have a worksheet, as shown below. There are four worksheets. They are:

Enquiry Form, Sales Division I, Sales Division II and Sales Division III.

Now you need to find the September sales figures of employees in the "Inquiry Form" from each sales department and fill them in the corresponding position. What would you do?

This can be divided into two situations:

You know which department the employee in the enquiry list belongs to.

You don't know.

In the first case, you can use Vlookup to look up and extract data from each table in turn.

If it's the second case, it's a little more complicated.

I recommend a combination function: Iferror+Vlookup.

The complete formula is as follows:

=IFERROR (IFERROR (VLOOKUP (A2, SALES ONE!$ B$2:$C$8,2,0),VLOOKUP (A2, SALES PART II!$ B$2:$C$9,2,0)),VLOOKUP (A2, SALES THREE!$ B$2:$C$10,2,0)) roughly means that if you can't find it in Sales Department 1, you go to Sales Department 2 to find it, and if you can't find it, you go to Sales Department 3 (if you find all the tables or don't have them, it will be displayed as garbled #N / A).

3 tables, with 3 Vlookup, 2 Iferror;n tables, with n Vlookup,(n-1) Iferror nesting.

Super simple have wood have ~

The advantage of this approach is that it is low cost to understand, and only needs to nest Iferror and Vlookup functions according to the number of worksheets.

However, the shortcomings were also obvious. Once there were too many tables, the formula would be hundreds of millions of points longer…

Then recommend to you a universal routine formula, as shown below.

The complete formula is as follows:

=VLOOKUP (A2,INDIRECT (LOOKUP (1,0 / COUNTIF (INDIRECT ({"Sales 1";"Sales 2";"Sales 3"}&"! B:B"),A2),{" Sales Department 1 ";" Sales Department 2 ";" Sales Department 3 "})&"! B:C"),2,0) You can apply the formula as long as you modify the following parameters:

A2: Value of Vlookup;

{} Contents in array: {"Sales Department 1";"Sales Department 2";"Sales Department 3"} Multiple worksheet names separated by semicolons;

B:B: In which column of each table the lookup value is located, it is necessary to determine whether the lookup value exists in this column of each table;

For example, in the above case, the lookup value is in column B of the table, so it is B:B;

B:C: The search area of vlookup;

2: Number of columns that return values, September sales is column 2 in the B:C region.

Note: With this method, the structure of all worksheets must be consistent.

Learned the first two methods, you can basically meet the needs of cross-table lookup.

But I want to share this last method with you.

Because it's so easy to use!

The complete formula is as follows:

=VLOOKUP(A2,VSTACK) B$2:$C$10),2,0) Formula meaning:

First use the VSTACK function to vertically splice the data of B2:C10 in the three tables, and then use the Vlookup function to search in these data.

Is it easy to understand?!

However, there are some precautions to use this function. Remember to take a small notebook and write it down ~

$B$2:$C$10 is based on the worksheet with the most data, and cannot be directly selected. For example, in this case, the search area of Sales Department 1 and Sales Department 2 is B2:C9, and Sales Department 3 is B2:C10.

The VSTACK function is currently only available in Office 365 and not available in other versions.

At the end:

Whenever we encounter a situation where we query and list matching information from a table, we should first consider whether Vlookup can do it.

When Vlookup fails, check these 4 things:

Check that the spelling and match of the function are exactly correct;

Check whether each parameter is filled in as required;

Check whether the reference area contains the search object (especially whether the reference range needs to be locked when filling the formula in batches);

Whether the data source is standardized and consistent.

Learn function formula, do not require success once, the most important thing is to start, while doing observation, debugging, if at the same time there are people to exchange learning, mutual promotion is better.

This article comes from Weixin Official Accounts: Autumn Leaf Excel (ID: excel100), Author: Zhu Lan, Editor: Yali Zi, 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.