From rookie to become a master of Excel Vlookup Function

Speaking of whether to use Excel, estimated to have used the computer as long as people have said they will use. Is it really? To the author's many years of experience in technical support for users, many users so-called "will use" is really just confined to "be with" and ourselves. Enter some content, plus some table formatting lines, points about archiving, see the document ready.

If only that, Microsoft would not have gone to great pains to develop any new version. Excel 5.0 for early had more than sufficient.

In fact, Excel is a very powerful data processing system, not just a form used to draw a table drawing tools. If you truly mastered all the features of Excel, you will find a lot of repetitive boredom of data processing, will become very beautiful, you just drag the mouse or drag bit, and then sat back and sip coffee port, would have busy dark halo to the task you have the system to help you deal better. At this time, still too busy looking at a collapse of Hutu colleagues, you have only one feeling: cool ... ...

The power of Excel to really play, you have the soul master Excel - function. Do not use Excel's functions, you are not a real user would use Excel. It is one function of different functions, the composition of different formulas to Excel the only heroes, with the martial arts masterpiece. Each function is a move of his secret, but only just learned to move his operation, it is still not enough, all recruited patients Lianshu only a, and integrated use, together, make move in move in order to really grasp His martial arts masterpiece, so that Excel can not automatically help us to complete the task.

Here, we start learning the heart of his secrets Excel heroes. I will first resort to introduce a move, followed the recipe, and then demonstrate how to apply their recipe integrated, creating new tactics. In practice, the enemy, we need to know, move is dead, people are living, move from the heart, endless.

The first one: needle in the haystack (Vlookup function)

Move to its name. This trick is used in a vast data source, automatically make you a computer to find relevant information and data, fill in the designated areas. Is that, you can let the computer in a table or a specified area to find a specified value, and thus should be returned to the relative value of the column before the row at the specified value. This move also the corresponding changes in, respectively, the two-type lookup and Hlookup. When the search data is horizontally, you can use instead of the function HLOOKUP function VLOOKUP. However, the situation is less used, does not describe here.

For example: you have a working table, above ten thousand items in the name of each item code, price, purchase date. If you need to do a separate report, and some of the goods which have been in this table have data, you can use this trick, just enter the name or code of goods, and the rest let the computer automatically finds and returns the corresponding price, date of purchase and so on.

Syntax

VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

Lookup_value the need to find value. Lookup_value can be a value, reference or text string.

Table_array for the need to find data in which the data sheet. You can use the name of a reference area or areas, such as a database or list.

If range_lookup is TRUE, then the first column of table_array value must be in ascending order: ..., -2, -1,0,1,2, ...,-Z, FALSE, TRUE; otherwise, VLOOKUP function can not return the correct value. If range_lookup is FALSE, table_array do not have to be sorted.

Through the "Data" menu "Sort" and select "Ascending", can be sorted in ascending numerical order.

Table_array values in the first column can be text, numbers or logical values.

Text is not case sensitive.

Col_index_num be returned to table_array matching value in column number. Col_index_num to 1, the return value in the first column table_array; col_index_num 2, the return value table_array the second column, and so on. If col_index_num less than 1, VLOOKUP returns the value of the # VALUE!; If col_index_num greater than the number of columns table_array, VLOOKUP returns the # REF!.

Range_lookup is a logical value that specifies the function VLOOKUP returns an exact match or approximate match. If TRUE or omitted, an approximate match is returned, that is, if you can not find an exact match, it returns the maximum value of less than lookup_value; if range_value is FALSE, VLOOKUP function will return an exact match. If not, it returns the error value # N / A.


Help

If the function VLOOKUP can not find the lookup_value, and range_lookup is TRUE, then use the maximum value less than or equal lookup_value.

If the first column of table_array lookup_value less than the minimum value, VLOOKUP returns the # N / A.

If the function VLOOKUP can not find the lookup_value and range_lookup is FALSE, VLOOKUP function returns the error value # N / A.

Application examples:
To facilitate comparison, I put the original data region in the same worksheet (E1: F5), the actual use, the original data can be in a different worksheet or even a different workbook (ie, different Excel files). When looking for original content in a different worksheet, table_array front need to add the sheet name, written as a "table name!" Regional context, such as "Sheet2! $ A $ 1: $ B $ 12", and if different workbook, have added the file name, such as "[file name] sheet1! $ A $ 1: $ B $ 12".

Explained

The formula "= Vlookup (A2, $ E $ 2: $ F $ 5,2, FALSE)" in A2 that to find the value of A2 cell content, that is "Apple", "$ E $ 2: $ F $ 5" tells the computer , should go to $ E $ 2: $ F $ 5 this data area to find, "2" means to find, it should return the region to the second column values, that is, the number of columns, the last "FALSE" parameter system, look for regional content have not been order, use the exact search, find even, and does not return an approximate match.

To note is that usually we are using the mouse to drag the method to fill the formula, and drag, Excel formulas in the area of the reference approach is not the same. If the reference is relative, that is ranked No. column before there is no "$" symbol, Excel for the relative displacement in the region, as indicated in column is E2: B5, onto the next column after that will automatically become E3: B6, which approach is necessary in many formulas, but in this formula is fatal, because it changed the look of the original data area, resulting in actually contains some data, because the area had not find the escape. This is also in the practical application of many users mistake, causing false search results. To solve this problem, we can use Excel's second approach to regional reference: the absolute reference. That is ranked No. column with a "$", so that the system will not be the relative displacement, no matter how delayed, the regional scope of the same. (In many cases, we will use the "name" instead of directly to the area in the manner specified, more convenient to use. The content will be introduced in other chapters)

Relative reference and absolute reference to the wording, you can let the computer for automatic conversion. Method is to first locate the current cell in the cell you want to modify, and then edit the row in the data, blackened with the mouse (in English theory called Highlight) to convert part of the press "F4" can be.
Can be found, the system has automatically filled the found value, such as Apple & cherry, to find the (Plum & Pear), shows # N / A.