What are the usage methods and examples of the vlookup function?

Make two products of the same kind in exactly the same order.

F2 cell formula =iferror(vlookup($b 2,$b$12:$d$16,column(a1),)"")

Formula description: The iferror function is used to mask the wrong value.

vlookup function tutorial.

Illustrate. Example.

vlookup(10003,a2:b26,2,false()) syntax. Range.

Index. The column index of the value to be returned, with the first column in the range numbered 1. If the index is not between 1 and the number of columns in the range, value! 。

Sorted - [Optional].

The default value is true() to indicate whether the column to be searched (the first column of the specified range) has been sorted. In most cases, it is recommended to set it to false(). It is recommended to set Sorted to false.

If set to false, an exact match will be returned. If there are multiple matching values, the contents of the cell corresponding to the first value found will be returned, and if no matching value can be found, n a will be returned. If Sort is set to true or omitted, the closest match (less than or equal to the search key value) is returned.

Actual combat. <>

vlookup is used to find another value corresponding to the specified value. For example: find a certain product**, a certain student's grade in a certain subject, etc.

vlookup(lookup_value,table_array,col_index_num,range_lookup)

lookup value is the value that needs to be found in the first column of the array. The lookup value can be a numeric value, a reference, or a text string.

The table array is the data table in which you need to find the data. You can use a reference to a region or region name, such as a database or a list.

If Range Lookup is true, the values in the first column of the table array must be 、... in ascending orderz、false、true；Otherwise, the function vlookup will not return the correct value. If the range lookup is false, the table array does not have to be sorted.

Hello, I have seen your question and am sorting out the answer, please wait a while

Questions. Is it out.

The same data can be matched using the vlookup function. 1.Click the cell that you want to match the data to.

2.Click on the formula in the menu bar and click Find & Apply. 3.

Click on the vlookup function formula. 4.Select the lookup value, data table, column ordinal, and match criteria separately.

5.Finally, press enter and fill it down. 6.

Follow the steps above to use the vlookup function.

Make two products of the same kind in exactly the same order.

F2 cell formula =iferror(vlookup($b 2,$b$12:$d$16,column(a1),)

Formula description: The iferror function is used to mask the wrong value.

The following describes how to use the vlookup function and how to use it:

Operating environment: Dell Inspiron 5000, Windows 10, WPS Office, etc.

1. Vlookup function [one-to-many query]: query the employee's position.

Function formula: =vlookup(e2,a:b,2,0).

How to use: Select the F2 cell first, and enter the function formula in the column: =vlookup; Then enter the first parameter of the vlookup function:

e2, which is the employee's name; The second parameter: a: cell b range, which is the query area; The third parameter:

2, representing the second column of the query area; The fourth parameter: 0, which represents an exact match; Finally, press the Enter key to end the confirmation, and you can use the vlookup function to query the position of the employee [Diao Chan]: [Employee].

2. Vlookup function [positive order query]: Lingdua answers to query the monthly salary of employees.

Function formula: =vlookup(e2,a:b,3,0).

How to use: According to the above method, you only need to change the third parameter of the vlookup function: to 3; Because the monthly salary is in column 3 of the query area, not column 2.

3. Vlookup function [reverse order query]: query from right to left.

Function formula: =vlookup(e2,if,1,0,b2:b9,a2:a9,2,0).

How to use: Although the vlookup function only supports normal order query, you can use the :if function to reset the query area to achieve the effect of reverse order query.

4. Vlookup function [wildcard query]: query the position of the employee surnamed Lu.

Function formula: =vlookup(e2,a:c,2,0).

How to use: ? in E2 cell? is a wildcard that represents any character; With the vlookup function, you can query the position of the surname of Lu in column A.

5. Vlookup function [Error Value Query]: Error message will be prompted when there is no employee information.

Function formula: =iferror(vlookup(e2,a2:c9,2,0),"This employee was not queried, please confirm!" ”）

How to use: The iferror function in the formula is used to output the prompt text when the name is not in the query range: This employee is not queried, please confirm!

The following table describes how to use the vlookup function and how to use it

Device: Lenovog400

System: Windows10 Professional Edition Qingying.

Software: Word

1. Single-condition forward search.

The vlookup function is applied to a single-condition forward lookup, as shown in Figure 1, to find the corresponding salary of an employee based on his name, and its formula is: =vlookup(h2,a1:f8,6,) or =vlookup(h2,a1:).

f8,6,false) or =vlookup(h2,a1:f8,6,0). When the fourth parameter is omitted, the comma cannot be omitted, otherwise an error will occur, and both 0 and false indicate an exact match.

2. Single-condition reverse search.

The vlookup function cannot be directly applied to reverse lookups, and requires the help of the if function. As shown in Figure 2, the formula for querying the corresponding employee name is =vlookup(h2,if(,f1:

f8，a1：a8)，2，0)。

3. Multi-condition forward search.

The vlookup function is used for multi-condition lookup, and needs to be used with the if function, of course, it can also be used in the form of adding auxiliary columns. As shown in Figure 3, the corresponding salary is found according to the two conditions of "Li Si" and "Female", and the formula is as follows: then press the three keys of the array Ctrl+Shift+Enter.

4. One-to-many query.

The so-called one-to-many query is to return multiple results by looking up one value, and the easiest way for vlookup to implement one-to-many query is to build a secondary column. Here we want to find the marketing department and return all the names.

For the multi-condition reverse query of the vlookup function, you also need to borrow the if function to construct a forward multi-condition region, as shown in Figure 4, which is as common as the positive formula and then press the three keys of the array Ctrl+Shift+Enter.

5. Abbreviated search.

According to the abbreviation to find the target data, you need to combine wildcard characters, and the asterisk indicates that any number of characters are matched, and the corresponding salary is queried according to "Ge Liang", as shown in Figure 5, the formula is: =vlookup("*h2,a1:f8,6,0), and the corresponding salary is queried according to "Ge", and the formula is:

vlookup(“*h5&“*a1：f8，6，0)。

Summary. Hello pro vlookup function uses as follows vlookup function is a longitudinal search function in excel, it belongs to a class of functions with lookup function and hlookup function, which are widely used in work, such as can be used to check data, quickly import data between multiple ** and other function functions. The function is to search by column, and finally return the value corresponding to the query column order required by the column; The hlookup counterpart is found by row.

Hello pro vlookup function uses as follows vlookup function is a longitudinal search function in excel, it belongs to a class of functions with lookup function and hlookup function, which are widely used in work, such as can be used to check data, quickly import data between multiple ** and other function functions. The function is to search by column, and finally return the value corresponding to the query column order required by the column; The hlookup counterpart is found by row.

The use of the vlookup function is as follows: 1. The function of vlookup is used to find another value corresponding to the specified value. For example:

Find a product's **, a classmate's grade in a subject, etc. 2. The vlookup function represents: vlookup (the value to be found, the search area, the column number where the return value is located, exact match or approximate match).

1. The value to be found: you can refer to the value of the cell, such as b6; It is also possible to enter directly, such as "red t-shirt". 2. Search area:

It is used to specify the search range, for example, A2: the column number where the return value is located: It is used to specify which column the return value is in, and the column number must be counted from the specified range; For example, the specified range is b2:

e8, column b is the first column, and if the return value is in column number 3, the value is returned from column d. 4. Exact matching or approximate matching: exact matching is represented by 0 or false; Approximate matches are denoted by 1 or true; is an "optional" item, which can be filled in or omitted; If not, the default value is approximate matching.

Hello dear, the above is the use and use of the vlookup function, for your reference <><