How to use vlookup function in Excel table?

The VLOOKUP function is a vertical lookup function in Excel. It belongs to the same class as lookup function and HLOOKUP function. It has been widely used in work, for example, it can be used to check data and quickly import data between multiple tables. Many friends can't learn to use the vlookup function. Next, I would like to share with you the comprehensive usage of VLOOKUP function, hoping to help you quickly learn to use vlookup function in ~Excel tables: 1. The writing of VLOOKUP formula =VLOOKUP(Lookup_value, Table_array, Col_index_number, Range_lookup) parameter ①Lookup_value. Parameter ②Table_array: the range of values to find. Note the second parameter of the function (when selecting the data source). The value to be searched must be at the far left of the selected data source range. Parameter ③Col_index_number: the column number containing the return value in the region. Parameter ④Range_lookup: exact match or approximate match _ is specified as 0/FALSEor 1/TRUE. Second, the use of vlookup function 1 Use VLOOKUP function to complete quick filling (1). Open the Excel sample source file and find the "VLOOKUP" file. Now fill in the email address and telephone number of each employee in the data source table with VLOOKUP function after the corresponding name in the 1VLookup table. (2) Press F2, enter the formula =VLOOKUP, and then press Tab, and Excel will automatically display its conditional left parenthesis, which will become =VLOOKUP(→ click the fx button on the left side of the edit bar → pop up the function parameter dialog box. (3) According to the usage of VLOOKUP function, fill in the parameters in the function parameter dialog box in turn. ① When the cursor stays at Lookup_value, click Select: B2 (name column), and the display effect will be B2. (2) When the cursor stays in Table_array, click Select: columns A to C in the data source table, and the display effect is: data source! A: C. Note: When selecting a data source, the column "Name" is required, and the starting column must be on the far left, so the selected area is from column A to the right, that is, column A: C in the data source table. (3) When the cursor stays at Col_index_num, click and enter the number: 2, that is, the searched data is the second column from left to right in the searched data source table, that is, the "mailbox" column. (4) When the cursor stays in Range_lookup, click and enter the number: 0, which means to conduct one-to-one exact matching search according to the parameters of Name Column. Finally, click OK to complete the function input. Next, just move the cursor to cell B2. When the cursor becomes a cross handle, double-click the mouse to complete the automatic filling of the entire column of formulas. However, this filling method will copy the formats of B2 cells together, so just move the mouse to the lower right corner of the last cell of the D column filling formula, click the auto-filling option button, and select the radio button of unformatted filling to complete the search of the mailbox column formula. (4) Statistics: Continue to search the mobile phone number with the VLOOKUP function: When the cursor stays at Lookup_value, click and select B2 (name column), and the display effect will be B2. When the cursor stays in Table_array, click and select column A-C in the data source table, and the display effect is: data source! A: C. When the cursor stays at Col_index_num, enter the number "3", that is, the data we are looking for is located in the second column from left to right in the searched data source table, that is, the "mobile phone number" column. When the cursor stays in Range_lookup, enter the number "0", which means to conduct one-to-one exact matching search according to the parameters of Name Column. Finally, click OK to complete the function input. After completion, we can see the complete entry effect of the formula in the edit bar. Next, move the cursor to the B2 cell → when the cursor becomes a cross handle → double-click the mouse to complete the automatic filling of the entire column formula, and then change the automatic filling option → select the radio button for unformatted filling to complete the search of the formula of the mobile phone number column. 2. Use VLOOKUP function to link tables. Next, we will simulate a "gadget" for employee lottery and redemption. That is, in the J: M column of the form, according to the employee number, find out his name, position, email address and exchange code, and make the exchange code into a bar code style. Moreover, the template of this coupon requires "three copies". In practical work, using employee numbers to manage employee information can effectively avoid data reading errors caused by duplicate names (for example, there are n employees named Ling Zhen in the whole company) or input errors (for example, Zhang Shengming is recorded as "Zhang Shengming"). Some companies also use card readers to automatically read the information of "employee number" in employee badges to improve the efficiency of information entry. In this example, the data validity rule of the cell "employee number" (K 1) is set in advance to prevent form users from arbitrarily entering numbers that do not exist in the form. Next, use the VLOOKUP function to compile this "gadget": (1) Select the table name M 1 cell and enter the formula =VLOOKUP(K 1, A: G, 2,0). Translating the lookup logic of VLOOKUP function into human language means: according to the cell K 1 (employee number, see the yellow box area in the table below), searching the data column of column A: G in the table (see the red box area in the table below) will return the second column from left to right in the data column. Moreover, this search method is exact search (the last parameter of VLOOKUP function is written as 0). (2) Write the formula of the post-prize level redemption code in the same way: as shown in the figure, the formula of the post-K2 cell is =VLOOKUP(K 1, A: G, 3,0). As shown in the figure, the formula of the award M2 cell is =VLOOKUP(K 1, A: G, 5,0). As shown in the figure, the formula of cell K3 of redemption code is =VLOOKUP(K 1, A: G, 4,0). As shown in the figure, the formula of cell K4 of redemption code is =K3. It can be displayed as a barcode, because we set the font as Code 128. Tip: Readers, if the font Code 128 is not installed on your computer, you can download the corresponding font through Baidu search. After the font is installed, the effect shown in this example can be achieved. Next, using the "camera" function of Excel, you can quickly copy the table and achieve the effect of "one table in three copies". The specific operation is as follows: (1) Enable the camera function: click the options button under the File tab → pop up the Excel options dialog box → select the quick access toolbar option → select the command that is not in the ribbon from the following location to find the camera → click the Add button → click the OK button when you are finished. (2) Select the form area to link (shoot), such as J 1: M6 cell area in this example → Call the camera function, that is, click the camera button → then click any blank space to complete the quick copy of the form. (3) In the same way, take another photo of the cell area of J 1: M6. After taking the photo, adjust the two "photos" and put them in a proper position. About "How to use vlookup function in Excel table?" We will share a lot of content. I hope you can use the knowledge points described in the tutorial to learn the operation! Finally, it is impossible to learn the vlookup function of Excel tables just by looking at it. Combining practice is the fastest way to master, so be quick ~