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 cond rows and Suppo 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 databa 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 cond 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 ud.
庐山温泉度假村
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 cond 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 cour, 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 u it, and I'm using it very well. However, there are veral details, we should pay attention to the u 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 parenthes, which are required.
The last parameter, range_lookup, is a logical value. We often enter a 0 word, or Fal, and can actually enter a 1 word, or true. What's the difference between the two? The former reprents a complete arch, and when it fails to find it, it nds back the wrong value N/A; the latter first looks for the exact same, and fails to find the clost 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 u reference address. When you u this parameter, you have two important reminders:
(A) the cell format category of the reference address is consistent with the category of the cell format to be arched. Otherwi, 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 arch 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 ent
ered, and found that the format does not match, it is too late, if you want to catch, you need to re-enter.
B) cond point reminder is the u of a convenient and practical skills, I believe many people have long been aware
