Detailed description of the EXCEL function VLOOKUP Application

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.

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.

 

 

Switch to VLookup

Recently, I fell in love with VLOOKUP, and someone updated it. Because of its vulnerability, only repeated values can be returned. The following is a detailed description!
VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
Lookup_value is the value to be searched in the first column of Table_array.
It can be a numeric value, reference, or text string. Note that the type must be consistent with the type of the first column of table_array.
Text is case-insensitive when searching for text. Wildcards "*" and "?" can be used.

Table_array is the data table in which data is to be searched.
You can use a reference to the region or region name, a constant array, and a calculated memory array.
When you reference a region, You can reference the entire column. excel automatically determines the region to be used.
The first column of this parameter must contain the searched content. Other columns must contain the content to be returned. The column sequence number of the returned content is specified by the next parameter.

Col_index_num is the column number of the matched value to be returned in table_array.
For example, if the value is 1, the value in the first column of table_array is returned; if the value is 2, the value in the second column of table_array is returned, and so on.
If col_index_num is less than 1, The VLOOKUP function returns an 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 !.

Range_lookup is a logical value that specifies whether the VLOOKUP function returns exact or approximate matching.
If it is TRUE or omitted, the approximate matching value is returned. That is, if no exact matching value is found, the maximum value less than lookup_value is returned;
Approximate match queries are generally used for numeric queries. The first column of table_array must be sorted in ascending order; otherwise, the correct results cannot be returned.
If range_value is FALSE (or 0), the VLOOKUP function returns the exact match value.
In this case, table_array does not have to be sorted. If no error is found, the error value # N/A is returned. You can use if to determine whether to remove the error message after isna detects the error.
========================================================== ==================================
VLOOKUP is often prone to errors # N/A. Below are several possibilities:
There are spaces or inconsistent data types.
You can use TRIM () to remove spaces before lookup_value.
If the format is inconsistent, You can forcibly convert the value to text. After lookup_value, it is an empty string expressed by & and.
Convert the text to a numeric value. Then, perform the lookup_value * 1 operation.
========================================================== ==================================

 

When processing data in some Excel files, you always need to search for the results you need. Therefore, the VLOOKUP function is frequently used. This is because you are also frequently asked about the use of this function. Here I will talk about VLOOKUP with my own experience. Two points are worth special attention.

VLOOKUP has four parameters:

VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)

First: lookup_value the value you want to search;

Second, table_array indicates the range to be searched in the Table. Note that the value of the first column in the range must be the column where the value to be searched is located;

Third: columns in the range of values to be returned by col_index_num. 1 indicates the first column of table_array and 2 indicates the second column;

Fourth, range_lookup is a logical value, indicating whether the VLOOKUP function returns an exact match or an 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. Note that if the value is TRUE, you need to search for the range (table_array). The values in the first column must have been sorted in ascending order.

Note that using this function in the areas marked with the "red background" above will not cause any problems.

For example: = VLOOKUP (C100, G: H, 2, FALSE). In this cell formula, the following parameters mean that C100 is the value of the C100 cell, that is, the value to be searched. G: H is our search range, that is, the range from G column to H column. 2 indicates that if we find the value in the range, that is, the value in C100, then we will return the value of 2nd columns in the same row in the search range.