Welcome to Office College, xiaoyang visit here waiting for you!
Today, we continue to learn together with you in all commonly used Excel spreadsheet formulas (functions) of the detailed usage and master the formula, many of our work will greatly improve the efficiency, we must learn yo, there do not understand can go to the Office College Questions (ask.officexy.com) here to ask Oh, we have progress, I will be pleased got:)
This lecture, we learn about Vlookup
The number of columns is not required, it is only described to facilitate the inconvenience
Column 1 column 2 3 4 5 6
元月 1020304050
February 8090100110120
March 9769455177
Enter search Month: March
Need to pick out the columns: 4
The result: 45
= VLOOKUP (G11, C6: H8, G12, FALSE)
Features
This function is the line in the table on the left to find specific content of the title
When found, it then pick out the line corresponding to the specified column of the cell contents.
Syntax
VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
Lookup_value for the need to find the first column of the array values. 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 data 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.
Format
There is no specific format
Example 1
The following examples are specified under the name and find a value based on the month.
= VLOOKUP () is used to look down along the first column name specified.
Difficulty is how to find the right month specified.
Solve this problem is to use = MATCH () function.
Function = MATCH () list of names by using the search to find the corresponding month. And projections for the month position in the list.
Unfortunately, because of the month search and find a list of the range of values ranging from wide.
Function = MATCH () function returns a number less than the number we need 1, so the +1 in the formula used to adjust.
Function = VLOOKUP () now use the function = MATCH () get adjusted n, in the name of the line corresponding to the line of the right to find the corresponding n-column input cell.
Function = VLOOKUP () in the final use of FALSE, so the left side do not sort the header row.
January February March
Cheng Hong-zhou 108097
Liu 209069
Cheng Long 3010045
Cheng-kun 4011051
chengxiang 50 120 77
Enter search name: Cheng Long
Enter search Month: March
The result: 45
= VLOOKUP (F60, C54: F58, MATCH (F61, D53: F53, 0) +1, FALSE)
3 = MATCH (F61, D53: F53, 0)
Example 2 This example uses the function = VLOOKUP () to find different car manufacturers in different parts of the value.
Function = VLOOKUP () scan down the column header row F and find the corresponding name in the C line of accessories.
Find the parts, the function VLOOKUP MATCH function to find the location according to the corresponding components to find the price.
Use absolute references in formulas, copying formulas in order to ensure that the function moves = HLOOKUP () and = MATCH () does not change the scope of reference.
Accessories manufacturers to find the value of the form
Toyota Spark Plug Toyota Ford Mercedes-Benz £ 50
£ 600 Mercedes-Benz transmission gearbox 500 450 600
Ford engine, the engine 10001200800 £ 1,200
Mercedes-Benz steering wheel 250 350 275 £ 275
Ford Spark Plug spark plug 507045 £ 70
Ford brake pads brake pads £ 290 300 290 310
Toyota gearbox £ 500
£ 1,200 Ford engine
= VLOOKUP (C80, F74: I78, MATCH (B80, G73: I73, 0) +1, FALSE)
Example 3
The following example is a building materials dealers offer a discount rate of the number of different procurement
Price list shows the brick, wood and glass unit.
Discount table provides a number of different products with different purchasing discount rate.
Procurement is the procurement budget table.
All results are displayed in the procurement budget table.
Name list in the C column.
The unit price is obtained from the price list.
FALSE option that product name in the table is not sorted in price order.
FALSE forced to use an exact match search. If not found, the function displays an error.
Discount is available from the discount table
If the purchase quantity discount table with a value match, the function = VLOOKUP will find in the discount table, the correct match discount.
TRUE option indicates the number of purchases through a discount table in ascending order sorting.
Use TRUE to allow fuzzy matching. If the purchase amount of the discount table does not find a match value, below it a smaller value will be used.
Such as the purchase quantity of 125 and 100 will be down to match the corresponding column and use a discount rate of 100.
Discount Table
Brick Wood Glass Price List
Brick £ 2 1 0% 0% 0%
Wood £ 1 100 6% 3% 12%
Glass £ 3 300 8% 5% 15%
Procurement Table
Project Procurement Quantity Unit Price Discount Total
Brick 125 £ 2 6% £ 235
Wood 200 £ 1 3% £ 194
Glass 150 £ 3 12% £ 396
Brick 225 £ 2 6% £ 423
Wood 50 £ 1 0% £ 50
Glass 500 £ 3 15% £ 1,275
The formula is:
Unit E118: = VLOOKUP (C118, C106: D108, 2, FALSE)
Discount F118: = VLOOKUP (D118, F106: I108, MATCH (C118, G105: I105, 0) +1, TRUE)
Total G118: = (D118 * E118) - (D118 * E118 * F118)
Example 4
The example uses an atmospheric pressure of the air value.
Density, viscosity, temperature,
0.457 3.55 500
0.525 3.25 400
0.616 2.93 300
0.675 2.75 250
0.746 2.57 200
0.835 2.38 150
0.946 2.17 100
1.09 1.95 50
1.29 1.71 0
Formula Description (Result)
2.17 in the A column to find 1 and B from the same row column return value (2.17) = VLOOKUP (1, B128: D136, 2)
100 in the A column to find 1, and C from the same row column return value (100) = VLOOKUP (1, B128: D136, 3, TRUE)
# N / A in the A column to find 0.746. A column because there is no exact match, it returns an error value (# N / A) = VLOOKUP (0.7, B128: D136, 3, FALSE)
# N / A in the A column to find 0.1. A column for the minimum value is less than 0.1, so return an error value (# N / A) = VLOOKUP (0.1, B128: D136, 2, TRUE)
1.71 Find in the A column 2 and column B from the same line in the return value (1.71) = VLOOKUP (2, B128: D136, 2, TRUE)
没有评论:
发表评论