Won't these operations, feel embarrassed say perfectness Office (one)

Common saying says well, excel is used well, next meeting early, in the job the most commonly used the tool that arrive is Excel, how to use it to improve its efficiency? Share a few groovy operation method of Vlookup today first:

Vlookup is called the king of the efficiency in Excel, but the person that major Excel is used cannot very good use Vlookup. But, vlookup function is the masses lover in Excel. Platform once had been done " if can choose to learn a function in Excel only, you can choose which " investigation, vlookup function resides the 2nd high actually.

The groovy operation method of VLOOKUP:

1, date is searched

The result that searchs when searching date can be a string of number normally, to make date can return corresponding form, need to cooperate TEXT function ability to finish so search demand.

Formulary =TEXT(VLOOKUP(E3 is inputted in F3 unit case, $A$2:$C$9, 2, 0) , "Yyyy/m/d") , by Enter key is finished. Following plan institute show:

Note: If return a format to be 2018/12/03, criterion the format of the 2nd parameter of TEXT can be installed for " Yyyy/mm/dd " can.

2, convention is searched

Search the sale of full name correspondence. Formulary =VLOOKUP(E3 is inputted in F3 unit case, $A$2:$C$9, 3, 0) , by Enter key is finished. Following plan institute show:

3, searched value is when sky

It is in the value that should search when sky, normally the meeting below the circumstance returns eventuate 0, so if yield a result,return blank, settlement method is in namely at the back of formula " " .

Formulary =VLOOKUP(E3 is inputted in F3 unit case, $A$2:$C$9, 3, 0)&"" , by Enter key is finished. Following plan institute show:

4, how is skimble-scamble times fault solved when searched target format

(1) if search target cost is text format, and numeric format is in data area.

Following plan institute are shown, a lists medium employee number number to be worth a format, and the employee number in F3 unit case is text format.

Formula inputs in G3 unit case: =VLOOKUP(- - F3, $A$2:$D$9, 4, 0) , by Enter key is finished.

Note: - - for two minus, decrease negative meaning namely, understandable lose to lose, a text is here compulsive changeover is numerical value, so the problem was solved very easily.

(2) if search target cost is numeric format, and text format is in data area.

Following plan institute are shown, a lists medium employee number to be text format, and the employee number number in F3 unit case is worth a format.

Formula inputs in G3 unit case: =VLOOKUP(F3& "