VLOOKUP function usage "Lookup" means "Lookup" in Chinese. In Excel, there are three functions related to "LOOKUP": VLOOKUP, HLOOKUO, and Lookup. The following describes how to use the VLOOKUP function. I. Functions Search for the specified data in the first column of the table and return the data in the specified column in the row where the specified data is located. Ii. Syntax Standard Format: VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) Explanation: VLOOKUP ', 'true or false parameters really indicate that the queried table has been sorted, and false indicates that no sorted ') Example: VLOOKUP (A2, Sheet2! $ A1: $ B10, 2, FALSE) Description: retrieves the content in A2 in the current table in Table SHEET2. if the content is retrieved, the content in B2 in Table SHEET2 is returned, because B2 is the second column in table SHEET2, therefore, the third parameter VLOOKUP uses 2 to indicate that if the condition is met, the second column of the query table is returned. The final parameter FALSE indicates 'false', which indicates the table to be queried, no sorting. In this case, the query ends until the first row of the table to be queried. Iii. Syntax explanation VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) can be written as follows: VLOOKUP (the data to be searched in the first column, the data table to be searched for, the column number of a column value, logical value True or False) 1. Lookup_value is "data to be searched in the first column of the data table", which can be a value, text string, or reference. 2. Table_array is "The data table in which data needs to be searched". You can use the cell region or region name. (1) If range_lookup is TRUE or omitted, the values in the first column of table_array must be listed in ascending order. Otherwise, the VLOOKUP function cannot return the correct values. If range_lookup is FALSE, table_array does not have to be sorted. (2) the value in the first column of Table_array can be text, number, or logical value. Text is case-insensitive. 3. Col_index_num is the column number of the matched value to be returned in table_array. When Col_index_num is 1, the value in the first column of table_array is returned; When Col_index_num is 2, the value in the second column of table_array is returned, and so on. If Col_index_num is smaller than 1, The VLOOKUP function returns the error VALUE # VALUE !; If Col_index_num is greater than the number of columns in table_array, the function VLOOKUP returns the error value # REF !. 4. Range_lookup is a logical value, indicating whether the VLOOKUP function returns exact or approximate match. If TRUE or omitted, the approximate matching value is returned. That is, if no exact matching value is found, the maximum value smaller than lookup_value is returned. If range_value is FALSE, the VLOOKUP function returns the exact matching value. If no value is found, an error value # N/A is returned. Iv. Application Example A B C D 1 No. Name Wage Department 2 2005001 Jay Chou 2870 Office 3 2005002 xiaoyaxuan 2750 Personnel Section 4 2005006 Zheng Zhihua 2680 supply department 2005010 tu honggang 2980 Sales Section 6 2005019 sun Nan 2530 Finance Department 2005036 Meng Tingyi 2200 MEETING Column A is sorted (the fourth parameter defaults to TRUE) VLOOKUP (2005001, A1: D7, 2, TRUE) is equal to "Jay Chou" VLOOKUP (2005001, A1: D7, 3, TRUE) is equal to "2870" VLOOKUP (2005001, A1: D7, 4, TRUE) equals to "office" VLOOKUP (2005019, A1: D7, 2, TRUE) is equal to "Sun Nan" VLOOKUP (2005036, A1: D7, 3, TRUE) is equal to "2200" VLOOKUP (2005036, A1: D7, 4, TRUE) is equal to "work meeting" VLOOKUP (2005036, A1: D7, 4) is equal to "work meeting" If column A is not sorted and the correct result is obtained, the fourth parameter must use FALAE. VLOOKUP (2005001, A1: D7, 2, FALSE) equals to "Jay Chou" VLOOKUP (2005001, A1: D7, 3, FALSE) equals "2870" VLOOKUP (2005001, A1: D7, 4, FALSE) equals to "office" VLOOKUP (2005019, A1: D7, 2, FALSE) equals "Sun Nan" VLOOKUP (2005036, A1: D7, 3, FALSE) equals "2200" VLOOKUP (2005036, A1: D7, 4, FALSE) is equal to "work meeting" 5. Applications of TRUE and FALSE For example, if you want to search for a person with a given number in a table with tens of thousands of records, if the number is sorted in ascending order, you can easily find this person. If the numbers are not sorted, you have to search from top to bottom. This is also the case when you use VLOOKUP to search for data. When the first column is sorted and the fourth parameter is set to TRUE (or indeed saved), Excel can easily find data with high efficiency. When the first column is not sorted and the fourth parameter is set to FALSE, Excel searches from top to bottom, which is less efficient. I think that to precisely search for data, because the computation speed of the computer is very fast, you can omit the sorting operation and use the fourth parameter to use FALSE. VLOOKUP Show all Hide all Search for the value in the first column of the table array, and then return the value of other columns in the current row of the table array. V in VLOOKUP indicates the vertical direction. When the comparison value is in the column on the left of the data to be searched, you can use VLOOKUP instead of HLOOKUP. Syntax VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) Lookup_value is a table array (array: used to create a single formula that can generate multiple results or calculate a group of parameters arranged in rows and columns. The array area shares a formula. An array constant is a group of constants used as parameters .) The value in the first column. Lookup_value can be a value or a reference. If lookup_value is smaller than the minimum value in the first column of table_array, VLOOKUP returns the error value # N/. Table_array is two or more columns of data. Use a reference to a region or a region name. The value in the first column of table_array is the value searched by lookup_value. These values can be text, numbers, or logical values. Case Insensitive. Col_index_num is the column number of the matched value to be returned in table_array. When Col_index_num is 1, the value in the first column of table_array is returned; col_index_num is 2, and the value in the second column of table_array is returned, and so on. If col_index_num: If the VALUE is less than 1, VLOOKUP returns an error VALUE # VALUE !. If the number of columns is greater than that of table_array, VLOOKUP returns an error value # REF !. Range_lookup is the logical value and specifies whether to query the exact or approximate value in VLOOKUP: If it is TRUE or omitted, the exact or approximate matching value is returned. That is to say, if no exact match value is found, the maximum value smaller than lookup_value is returned. Values in the first column of table_array must be sorted in ascending order; otherwise, VLOOKUP may not return the correct values. You can select the "sort" command on the "data" menu, and then select "increment" to sort these values in ascending order. For more information, see Default sorting order. If it is FALSE, VLOOKUP will only search for exact matching values. In this case, the values in the first column of table_array do not need to be sorted. If two or more values in the first column of table_array match lookup_value, the first value is used. If the exact match value is not found, the error value # N/A is returned. Description When you search for text values in the first column of table_array, make sure that the data in the first column of table_array does not contain leading spaces, trailing spaces, inconsistent direct quotation marks ('or "), Bent quotation marks ('or') or non-printable characters. In the above cases, VLOOKUP may return incorrect or unexpected values. For more information about the functions used to clear text data, see text and data functions. When searching for a number or date value, make sure that the data in the first column of table_array is not saved as a text value. Otherwise, VLOOKUP may return incorrect or unexpected values. For more information, see convert numbers saved as text to numeric values. If range_lookup is FALSE and lookup_value is text, you can use wildcards and question marks (?) in lookup_value (?) And star number (*). Question marks match any single character; asterisks match any character sequence. If you want to find the actual question mark or Asterisk, enter the waveform character (~) before the character (~). Example 1 In this example, the density column of the atmospheric feature table is searched for the values in the viscosity and temperature columns. (This value is the result of the determination of air at sea level 0 degrees Celsius or 1 atmospheric pressure .) 1 2 3 4 5 6 7 8 9 10 A B C 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) = VLOOKUP (1, A2: C10, 2) use an approximate match to search for value 1 in column A. in column A, locate the maximum value 0.946 that is less than or equal to 1, then return the value of Column B in the same row. (2.17) = VLOOKUP (1, A2: C10, 3, TRUE) use an approximate match to search for value 1 in column A. in column A, locate the maximum value 0.946 that is less than or equal to 1, then return the value of column C in the same row. (100) = VLOOKUP (. 7, A2: C10, 3, FALSE) use exact match to search for value 0.7 in column. Because column A does not have A precisely matched value, an error value is returned. (# N/) = VLOOKUP (0.1, A2: C10, 2, TRUE) use the approximate match to search for the value 0.1 in column. Because 0.1 is smaller than the smallest value in column A, an error value is returned. (# N/) = VLOOKUP (2, A2: C10, 2, TRUE) use an approximate match to search for value 2 in column A. in column A, locate the maximum value 1.29 that is less than or equal to 2, then return the value of Column B in the same row. (1.71) Example 2 In this example, search for the "item ID" column in The Baby Products table and find the matched value in the "cost" and "increase" columns to calculate the price and test conditions. 1 2 3 4 5 6 A B C D Item ID, item cost increase ST-340 stroller $145.67 30% BI-567 bib $3.56 40% DI-328 diapers $21.45 35% WI-989 soft wet wipes $5.12 40% AS-469 suction generator ¥2.56 45% Formula description (result) = VLOOKUP ("DI-328", A2: D6, 3, FALSE) * (1 + VLOOKUP ("DI-328", A2: D6, 4, FALSE) plus cost, calculate the retail price of diapers. (¥28.96) = (VLOOKUP ("WI-989", A2: D6, 3, FALSE) * (1 + VLOOKUP ("WI-989", A2: D6, 4, FALSE ))) * (1-20%) retail price minus the Specified discount to calculate the sales price of soft wet wipes. (¥5.73) = IF (VLOOKUP (A2, A2: D6, 3, FALSE)> = 20, "increase:" & 100 * VLOOKUP (A2, A2: D6, 4, FALSE) & "%", "cost less than ¥20.00") if the cost of a product is greater than or equal to ¥20.00, the string "nn %" is displayed; otherwise, the displayed string "cost less than ¥20.00 ". (Up 30%) = IF (VLOOKUP (A3, A2: D6, 3, FALSE)> = 20, "increase:" & 100 * VLOOKUP (A3, A2: D6, 4, FALSE) & "%", "¥" & VLOOKUP (A3, A2: D6, 3, FALSE) if the cost of a product is greater than or equal to ¥20.00, the string "increase is nn %"; otherwise, the string "cost is ¥ n. nn ". (Cost: ¥3.56) Example 3 This example searches for the ID column of the employee table and finds matching values in other columns to calculate the age and test the error condition. 1 2 3 4 5 6 7 A B C D E ID, surname, name, title, date of birth 1 Li Xiaoming sales representative 12/8/1968 2 Lin caiyu sales department Vice President 2/19/1952 3 Wang Zhidong sales representative 8/30/1963 4 Pan Jin Sales Representative 5. Lin Dan Sales Manager 3/4/1955 6 Su Shiping Sales Representative Formula description (result) = INT (YEARFRAC (DATE (2004, 30), VLOOKUP (5, A2: E7, 5, FALSE), 1) for the fiscal year, find the age of the employee with ID 5. Use the YEARFRAC function to subtract the birthdate from the end date of the fiscal year, and then use the INT function to display the result as an integer. (49) = IF (ISNA (VLOOKUP (5, A2: E7, 2, FALSE) = TRUE, "employee not found", VLOOKUP (5, A2: E7, 2, FALSE )) if an employee with ID 5 exists, the last name of the employee is displayed. Otherwise, the message "employee not found" is displayed ". (Lin) When the VLOOKUP function returns an error value # NA, The ISNA function returns TRUE. = IF (ISNA (VLOOKUP (15, A3: E8, 2, FALSE) = TRUE, "employee not found", VLOOKUP (15, A3: E8, 2, FALSE )) if an employee with ID 15 exists, the last name of the employee is displayed. Otherwise, the message "employee not found" is displayed ". (Employee not found) When the VLOOKUP function returns an error value # NA, The ISNA function returns TRUE. = VLOOKUP (4, A2: E7, 3, FALSE) & "& VLOOKUP (4, A2: E7, 2, FALSE) &" yes "& VLOOKUP (4, A2: e7, 4, FALSE )&". "For employees with ID 4, connect the values of the three cells into a complete sentence. (PAN Jin is the sales representative .) Note that the first formula in the above example uses the YEARFRAC function. If the function is unavailable, an error # NAME? is returned ?, Install and load the "analysis tool library" to load macros. Procedure On the Tools menu, click load macro ". In the "available macro loading" list, select the "analysis tool library" box and click "OK ". If necessary, follow the instructions in the installation program. |