vlookup使用方法（Vlookup usage）
VLOOKUP function
In the first column of an array of numerical tables or numerical find specified, and then return to the table or array in the row in the numerical value at the specified column.
The "array" here can be interpreted as an area in the table. Column number array: an array of "first", is the first column of this area, this column on the right were second rows and Suppose that an array region is B2:E10, then B2:B10 is first columns and C2:C10 is
Syntax:
VLOOKUP (lookup values, regions, columns, ordinal numbers, logical values)
Lookup value: a numeric value that can be numeric, reference, or literal string for a value that needs to be found in the first column of the array.
"Region": the area in which the array is located, such as "B2:E10", or a reference to a region or region name, such as a database or data list.
The "column": that area (array) to return the matching value of the column number, 1, returns a value in the first column, 2, returns a value, in the second column and so on; if the column number is less than 1, the VLOOKUP function returns the error value of #VALUE; if the number of columns is larger than the
area! The VLOOKUP function returns the error value of #REF!.
Logical value: for TRUE or FALSE. It indicates whether the function VLOOKUP returns exactly matches or matches approximately. If TRUE or omitted, returns the approximate matching value, that is to say, if you can not find the exact matching value is less than the maximum value returns the lookup value "; if the" logical value "for FALSE, VLOOKUP will return the accurate matching value function. If not, the error value #N/A is returned. If the lookup value is text, the logical value should normally be FALSE. In addition:
If the lookup value is less than the minimum value in the first column of the zone, the function VLOOKUP returns the error value #N/A.
If function VLOOKUP cannot find the lookup value and the logical value is FALSE, the function VLOOKUP returns the error value #N/A.
The following example illustrates how the VLOOKUP function is used.
Assume that in Sheet1 stored wheat, rice, corn, peanuts and other agricultural products sales unit price:
A B
1 unit price of agricultural products
2 wheat 0.56
3 rice 0.48
4 corn 0.39
5 peanuts 0.51
.......................................
100 soybean 0.45
Sheet2 is the list of sales, and each time the list is filled with different requirements: enter the name and quantity of the agricultural product in Sheet2, and automatically generate unit price and sales according to the data of Sheet1. Set the table to Sheet2:
A B C D
1 name, quantity, unit price and amount of agricultural products
2 rice 10000.48480
3 corn 20000.39780
.........................................................
Enter the formula in the D2 cell:
=C2*B2;
Enter the formula in the C2 cell:
=VLOOKUP (A2, Sheet1, A2:B100, 2, FALSE).
As in language, this is to find the value of the Sheet2 table cell A2 in the first column of the Sheet1 table A2:B100 region, and then return the value of the second column of the row after it is checked.
Thus, when the name entered in the Sheet2 table A2 cell changes, the unit price in the C2 changes automatically. Of course, as the single values in Sheet1 change, the corresponding values in Sheet2 change as well.
The formulas for other cells can be written in the form of fill.
VLOOKUP function usage notes
When it comes to the VLOOKUP function, I'm sure everyone will use it, and I'm using it very well. However, there are several details, we should pay attention to the use of good.
A.VLOOKUP syntax
The full syntax of the VLOOKUP function is this:
VLOOKUP (lookup_value, table_array, col_index_num,
range_lookup)
1., there are four arguments in parentheses, which are required.
The last parameter, range_lookup, is a logical value. We often enter a 0 word, or False, and can actually enter a 1 word, or true. What's the difference between the two? The former represents a complete search, and when it fails to find it, it sends back the wrong value N/A; the latter first looks for the exact same, and fails to find the closest value, but also fails to find it, and returns the error value N/A. It doesn't really mean anything to us, just to satisfy curiosity,
Interested friends can experience the experience.
2.Lookup_value is an important parameter that can be numeric, literal, string, or referenced. We often use reference addresses. When you use this parameter, you have two important reminders:
vlookup和column(A) the cell format category of the reference address is consistent with the category of the cell format to be searched. Otherwise, sometimes the information is visible, but it cannot be caught. Especially when the value of the reference address is numeric, the most obvious is that if the cell format of the search is text, though it looks like 123, it won't be able to catch anything.
And format categories in the data is not entered, we must first determine the good, if the data are enter
ed, and found that the format does not match, it is too late, if you want to catch, you need to re-enter.
B) second point reminder is the use of a convenient and practical skills, I believe many people have long been aware

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。