In daily office work or data processing, we often encounter situations where we need to find data between multiple worksheets. The VLOOKUP (vertical lookup) function in WPS Office下载 tables is a powerful tool to solve this problem. Next, we will introduce in detail the syntax of the VLOOKUP function, applicable scenarios, and how to use this function between multiple worksheets to help you quickly master cross-table search skills.
What is the VLOOKUP function
VLOOKUP is the abbreviation of “Vertical Lookup”, which means vertical search. It can vertically search for matching items in the specified data table area based on the keywords (query values) you enter , and return the values of other columns in the same row.
The basic syntax of the VLOOKUP function is as follows:
excel
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The meaning of each parameter is as follows:
- lookup_value : the value to be looked up;
- table_array : the data area to be searched;
- col_index_num : the column number where the result is located (counting from the first column in the search area);
- range_lookup : Whether to perform fuzzy matching, 0 or FALSE indicates an exact match, 1 or TRUE indicates an approximate match.
Note: VLOOKUP can only be used for “search by column”, that is, your search area should be organized by column, and the search value must be in the first column of the area .
How to use VLOOKUP between multiple worksheets?
Let’s take a practical example to illustrate: In Sheet1 , we have product names, and in Sheet2 we have the sales data of these products. We want to find the sales volume of a product from Sheet2 and display it in Sheet1.
Insert function
- Open the WPS官网 spreadsheet document;
- In Sheet1, click the cell where you want to display the search results;
- Click the “Insert Function” button in the ribbon, enter in the pop-up window VLOOKUPand click “OK”.
Fill in the parameters
Take the example of finding the sales volume of “Macchiato”, assuming it is located in cell B9 and the data range in Sheet2 is A2:B7. Then the function can be written as follows:
excel
=VLOOKUP(B9, Sheet2!A2:B7, 2, 0)
- B9: Lookup value;
- Sheet2!A2:B7: Search range, located in another worksheet;
- 2: Returns the value in the second column of the range;
- 0: Exact match.
Click OK to return to the search results.
Use absolute references to achieve automatic filling
If you want to apply the same search formula to multiple cells, it is recommended to lock the data range (using $the symbol) to avoid reference errors when dragging:
excel
=VLOOKUP(B9, Sheet2!$A$2:$B$7, 2, 0)
Then use the fill handle (the + sign in the lower right corner) to drag downward to complete multiple searches in batches.
Common Problems and Precautions
- In the search area, the search column must be the first column ;
- The third parameter (column number) is relative to the search area, not the entire worksheet;
- If no data is found, the return value will be #N/A, and IFERRORthe package can be used to handle the exception;
- When referencing across worksheets, pay attention to the spelling of the Sheet name. If there is a space, single quotes should be used, for example ‘Sheet 2’!A2:B7.
What is WPS Spreadsheet?
WPS Spreadsheet is one of the important modules in WPS Office suite and is an efficient alternative to Microsoft Excel. It supports more than 100 built-in formulas, pivot tables, advanced charts and other functions, and is fully compatible with Excel format.
Recommended extended learning
Want to learn more about search functions? WPS Office Academy has prepared the following free tutorials for you:
- [LOOKUP function quickly finds data]
- [HLOOKUP function horizontal search example]
- [Excel search and reference function in-depth analysis]
These courses are clear and practical, suitable for beginners to advanced users of Excel and WPS tables.
The best choice for improving skills
Mastering the VLOOKUP function, especially the cross-table usage skills, will greatly improve your office efficiency and data processing capabilities. WPS Spreadsheet is not only easy to operate, but also provides rich teaching resources. It is your best choice to improve your skills. Try to apply it to your worksheet now to make data query fast and accurate!
ALSO READ: The Next Step in Ecommerce: How AI Companions Are Transforming the Post-Purchase Experience
