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

Original title: "Goodbye, VLOOKUP!" This function, which was blown to the altar by countless people, almost cost me my job? "

Vlookup has a characteristic that it is easier to get started than to master it.

We often use it for data search, it is no exaggeration to say that learning and learning it well can solve about 50% of the work.

It can not only improve work efficiency, but also become a great god in the eyes of colleagues.

But it was such a function that almost cost me my job.

What's going on?

A few days ago, I received a report. I need to find the corresponding amount of the "assets" item from the [subsidiary report] and fill in the [consolidated report] worksheet.

I think it's not easy. It would be nice to use the VLOOKUP function V, but I didn't expect that the famous VLOOKUP made a mistake in the actual use.

Who is the original killer?

Today we will reveal the secret together.

Analyze the problem [consolidated report] icon:

[subsidiary report] the chart shows:

The formula is as follows:

= VLOOKUP (report form of A4 subsidiary! $Avid subsidiary BJI 2pl 0) Formula Analysis:

Use the V function to find the contents of [A4] cell (that is, currency funds) in column An of the subsidiary report, and return the corresponding data in column B of the subsidiary report.

At first glance, the project names of the two tables are almost the same, so why is there a garbled # Nhand A?

In such a situation, don't worry, let's check it out in the following way.

Use the equal sign (=) to compare the contents of the two cells. For example, compare the contents of [A4] cell in [consolidated report] with [A5] in [subsidiary report].

TRUE is returned if the two contents are the same, and FALSE is returned if they are different.

The result in the figure returns FALSE, indicating that the contents of the two cells are not exactly the same.

At this point, we can further check the contents of the [A4] cell edit bar in the merge report:

Sure enough, the data in the A4 cell is not as simple as it seems! There are some space characters in it!

Let's take a look at the contents of the [A5] cell edit bar in the subsidiary report:

There are also varying numbers of space characters.

At this time, the cause of the problem has been found out, and it will be easy to solve later!

To solve the problem, the most common way to solve such space characters is to use the TRIM function to remove them.

Method ❶ adds a TRIM function in front of [A4] cell and [subsidiary report! avatar B] cell range at the same time, which is used to remove spaces in these two cells at the same time. The Vlookup function won't go wrong! As shown below:

The formula is as follows:

= VLOOKUP (TRIMA4), TRIM subsidiary report! avatar B), 2Phone0) however, because the TRIM function is used here, the result returned becomes a text number.

If you want to become a real number, you need to do another four-point operation.

For example, if we add two minus signs (- -), it becomes a real number!

Method ❷ method 01 uses the TRIM function in both the first parameter and the second parameter, which leads to a large number of operations, and in the case of a lot of data, it will lead to table stutters.

We can also solve this problem by using the combination of [TRIM function + wildcard] only in the first parameter. At this time, the operation efficiency will be higher!

The formula is as follows:

= VLOOKUP ("*" & TRIM (report of A4 subsidiary! Avatar Bjor0) Formula Analysis:

Use the TRIM function to remove extra spaces from [A4] cells, and then connect a wildcard to match any number of characters (including spaces).

No matter how many spaces there are in front of those cells in column An in the subsidiary report, wildcards can match.

In this way, the V function can smoothly find out the data you want!

And what is found in this way is the real number, and there is no need for the next mathematical operation.

In most cases of knowledge expansion, the above problem is to manually enter spaces for indentation alignment of project names, and the number of spaces entered each time is not exactly the same, which will lead to search errors!

In order to avoid this kind of problem, we can achieve the indentation alignment of the project name through the "increase indent" function in the start tab.

For example:

You can use the method of find and replace to remove all the extra spaces in the two tables.

Then select these items and click the "increase indent" button in the start tab.

It's convenient and fast!

And there will be no search errors, which standardizes all forms!

At the end of today, we shared a very common cause of errors in the famous V function lookup-manually entering extra spaces, resulting in lookup reference errors.

In addition to spaces, there are some invisible characters (non-print characters), such as tabs, line feeds, and so on.

To remove these non-print characters, we usually use the CLEAN function.

In addition, there is some data derived from the web page or from the system, the use of TRIM function and CLEAN function may not solve the problem.

In this case, you can use the text intercept class function (MID,LEFT,RIGHT) to extract the data you need, and then look it up as the first parameter of the V function, and you won't go wrong!

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