Sharing practical Excel skills: Eliminating Vlookup 'BUG'

In the previous article "Practical Excel Skills Sharing: Four Rating Formulas", we learned how to write the four rating formulas. Today we will talk about Vlookup and see how to eliminate the "BUG" of Vlookup and make the empty return empty. Let's take a look!

Today a student happily told me that he found a major BUG in vlookup. After listening to this, I was stunned, shouldn’t this be right?

After listening to this student’s detailed description, I finally understood. The "BUG" she refers to is that during the operation of the Vlookup function, if the cell containing the return value of the third parameter is empty, the result returned by the function is not empty but 0. As shown in the table below, the student searches for the corresponding salary deduction details based on the employee's job number. When the E4 cell corresponding to the 9003 job number in the source table is empty, the output result in the right table is 0, not empty.

#Students said that this situation may lead to statistical errors and cause a lot of trouble. So how can we make a blank cell return a blank cell?

This question is very simple. We only need to judge the operation result of the original vlookup function formula. If the operation result is 0, return a null value. If the operation result is not zero, return the operation result.

First let me show you the results after using the new function formula:

We use the function formula: =IF(ISNUMBER(VLOOKUP(I2,A :E,5,0))=FALSE,"",VLOOKUP(I2,A:E,5,0)) completes "air-to-air".

Students expressed confusion after reading the formula. How can they clarify the logical relationship with so many brackets? Besides, there is also an ISNUMBER function that has never been used before!

Don’t be afraid when we encounter a very long function, we can figure it out just by dismantling it step by step.

Now we will break down the function formula for this student.

The first step of dismantling:

VLOOKUP(I2,A:E,5,0) I believe this part of the function formula is often read by us Friends of the excel tutorial article are familiar with it. Its meaning is to return the row number of cell I2 in column A corresponding to the cell content of column 5. “A picture is worth a thousand words.” With a picture, everyone will understand at a glance.

Note: 1. The general usage of vlookup is that the search value must be in the first column of the selected area. 2. The third parameter column number cannot be less than 1 and cannot be greater than the total column value of the selected cell area. For example, after selecting the area A:E, there are only 5 columns in the area. If you enter 6, the cell reference error message "#REF" will be returned.

The second step of dismantling:

ISNUMBER(VLOOKUP(I2,A:E,5,0) This part of the function formula looks like Strange, it is actually easier to understand than the first step. It just adds an ISNUMBER function in front. We only need to figure out this function.

The ISNUMBER function can be disassembled into IS NUMBER, so it can be disassembled Everyone should understand that it is actually "whether it is a numerical value". Its function is to determine whether a cell is a numerical value.

Let me give you a simple demonstration below:

We can see that in the above example, the E6 cell is blank, and the ISNUMBER judgment result is FALSE. The same is true for the "E4 cell corresponding to the 9003 job number is empty" described at the beginning of the article, ISNUMBER( VLOOKUP(I2,A:E,5,0) determines the salary deduction for job number 9003 as FALSE.

The third step of disassembly:

This part mainly involves a very commonly used function - IF. IF does not need to be explained too much. Its function is very powerful. It is mainly used to determine whether a certain condition is met. If it is met, it returns a value. If it is not met, it returns another value. A value.

Let me give you a simple demonstration below:

We can easily understand the above table=IF (F6=FALSE,"",E6)Function formula. Then we can directly use ISNUMBER(VLOOKUP(I2,A:E,5,0) instead of F6, without any characters between the double quotes Indicates blank, VLOOKUP(I2,A:E,5,0) replaces E6. Finally, the function formula that appears at the beginning of our article is formed: =IF(ISNUMBER(VLOOKUP(I2,A :E,5,0))=FALSE,"",VLOOKUP(I2,A:E,5,0))

Related learning recommendations: excel tutorial

The above is the detailed content of Sharing practical Excel skills: Eliminating Vlookup 'BUG'. For more information, please follow other related articles on the PHP Chinese website!