To give an example of a special scenario where the Vlookup function formula is used in the work
For example, on the left is the simulated data, and only the two columns of employee name and salary are retained
There is a special case where some of the data in the original data is blank
Now you need to find a salary that matches some of the employees
1. Vlookup formula
We all know to use the VLOOKUP formula to match, and the formula used is:
=VLOOKUP(D2,A:B,2,0)
You'll notice that the original data source was originally blank
But the result of the search became the number 0
Our need is what the original table looks like, and what kind of results need to be returned
2. The number 0 is not displayed
Some friends may use the trick of not displaying the number 0, that is, select the data column E column, and then right-click to format the cells (shortcut key CTRL+1)
Then set the custom in the number, using:
G/Common Format; G/Common Format;
The number format is 3 semicolons, and when positive and negative numbers are applied, the general format is used, and 0 is not displayed
But this method will also bring a problem, that is
Originally, the original table had 0 in it, and it will also be displayed as a blank cell
If the original table was originally 0, it should also be displayed
3. Simple method
In fact, the method is very simple, we only need to use the conjunction&, followed by two double quotation marks in the English state, and the formula used is:
=VLOOKUP(D2,A:B,2,0)&""
In fact, it is to forcibly convert the search result to text format, so that the blank value will not be forcibly converted to 0, and then displayed as blank
Have you learned this little trick? Try it out!