How to use vlookup function in WPS table How to use vlookup function in WPS table

How to use vlookup function in WPS form? Recently, a user asked this question. WPS is an easy-to-use office software. You can use various function formulas when editing. So how to use the vlookup function? In response to this problem, this article brings detailed usage methods to share with everyone, let’s take a look.

Question: How to use VLOOKUP function in WPS table? Simple explanation: The VLOOKUP function is used to find a specific value in a specified column and return the corresponding value in another column. Read the details below, PHP editor Xiaoxin will guide you step by step on how to use the VLOOKUP function, including: Syntax and parameters of the VLOOKUP function Examples of using the VLOOKUP function in WPS tables Use techniques of relative references and absolute references to handle the lookup value that does not exist Error

1. Call up method:

1. First select the table position of the output data, then find the formula in the menu bar above and click to enter, then select Insert formula in the upper left corner, Select Enter, enter vlookup in the pop-up menu and click Confirm.

2. You can also select the table position of the output data, just enter =vlookup.

2. Basic usage

Parameter introduction:

VLOOKUP (what to look for, where to look for it, and return to the corresponding right side after finding it) Which column of data, precise or fuzzy search)

Note:

One thing to note is that the help information in Excel also has errors, such as in The description of the fourth parameter of VLOOKUP in the insert function is wrong. Please be careful not to be misled! As shown below:

3. Single condition search

In cell E2, enter =VLOOKUP (D2, $A$2: $B$12, 2, 0 )

Parameter explanation:

=VLOOKUP (the salesperson to be found, the data source area containing the salesperson and his sales volume, return to column 2 after finding, precise search)

The first parameter: what to look for (or what to search for), search by salesperson, so enter D2

The second parameter: where to look, the data source area is in column A:B, so enter $A$2：$B$12

The third parameter: which column to return after finding it. What we are looking for is the sales volume. The sales volume is located in column B, which is the second column in the second parameter, so enter 2

Fourth parameter: You need to search accurately here, so enter 0

3. Use the name box to search

Select the A2:B12 area to define it as the search area, in unit E2 Cell input = VLOOKUP (D2, search area, 2, 0)

Note:

Use the name box to grab the data area, so you don’t have to select the area manually (the name box is suitable for workbooks All worksheets in the table)

4. Search returns multiple columns of data

Requirements:

The table on the left is the data source area. The corresponding data needs to be retrieved based on the name in the right area. The yellow area is the area where the formula needs to be filled in.

Select the H2:K5 cell range, enter the following formula =VLOOKUP ($G2, $A$2: $E$9, COLUMN (B1), 0) and press Ctrl+Enter key combination.

5. Multi-condition search (one-to-many search)

First add a help column in column A, use COUNT and & combination, enter = in A2 C2&COUNTIF($C$2:C2,C2)

Select cells F9:H15 and input =VLOOKUP($F$2&ROW($A1),$A$2:$D$15,COLUMN(B$1),0 ), then press Ctrl+Enter

Finally, in order to avoid incorrect values, add the IFERROR function outside, =IFERROR(VLOOKUP($F$2&ROW($A1),$A$2:$D$15, COLUMN (B$1), 0),)

6. Search from right to left (reverse search)

Requirements:

You need to find the corresponding number according to the given number The name of the salesperson, enter the formula

in the yellow area When the data source format does not allow changes

Enter =VLOOKUP(E2,IF({ 1, 0}, $B$2: $B$12, $A$2: $A$12), 2, 0)

Here, the IF function reconstructs a region and divides $B$2: $B Put $12 in the first column, $A$2:$A$12 in the second column, find and return the corresponding data

When the first parameter of IF is {1, 0}, it is equivalent to establishing the condition and those that are not true are put together and constructed as a memory array,

1 represents when the condition is true, 0 represents when the condition is not true, {1, 0} forms 2 columns, equivalent to column B and column A Swap positions

7. Interval query

Requirements:

It is necessary to divide the scores into their corresponding grades according to the grade classification rules

Create a help column

Note: Must be in ascending order

The last parameter of this function can be hidden (not entered), Or enter 1

Enter =VLOOKUP(B2, $I$2:$J$5,2) in cell C2. Expand this formula like this: =VLOOKUP(B2, {0, D;60, C; 80, B; 90, A}, 2)

8. Wildcard fuzzy search

This method is only applicable when there is only one value that meets the conditions, otherwise it is better to use SUMIF.

The role of vlookup: vlookup is used to find another value corresponding to the specified value. For example: find the price of a certain product, the score of a certain classmate in a certain subject, etc.

　vlookup function representation:

　=vlookup (value to be found, search area, column number of the return value, exact match or approximate match)

Parameter description ：

1. The value to be found: You can refer to the value of the cell, such as B6; you can also enter it directly, such as a red T-shirt.

2. Search area: used to specify the search range, such as A2:D10.

3. The column number where the return value is located: used to specify which column the return value is in. The column number must start from the specified range,

For example, the specified range is B2:E8, then column B is the first column. If the column number of the return value is 3, then the value is returned from column D.

4. Exact match or approximate match: Exact match is represented by 0 or False, approximate match is represented by 1 or True,

is optional, you can fill it in or not, if not, The default value is an approximate match.

The above is the detailed content of How to use vlookup function in WPS table How to use vlookup function in WPS table. For more information, please follow other related articles on the PHP Chinese website!