Excel is a convenient spreadsheet software that many people use on a daily basis. Among them, the VLOOKUP function is extremely useful and helps with efficient data processing. This article explains from the basics to advanced usage of the VLOOKUP function and also introduces handy shortcuts that can be used immediately.
What is Excel VLOOKUP Function?
The VLOOKUP function is an Excel function that searches for a specified value within a table and returns related data. It is mainly used with the following syntax:
| Function | Description |
|---|---|
| =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) | Searches for a value that matches the lookup value in a table and returns the related value. |
For example, if there is a table as follows:
| Product | Price |
|---|---|
| Apple | 100 |
| Banana | 80 |
If you enter “Banana” in cell A1 and use the VLOOKUP function, cell B1 will display “80”.
Excel VLOOKUP Shortcuts
Below are shortcuts to efficiently use the VLOOKUP function in Excel:
Shortcut Keys
1. [Ctrl] + [R]: Retrieves the content of the cell to the right of the selected cell.
2. [Ctrl] + [Shift] + [Enter]: Inputs an array formula.
Automatic Function Input
1. When a cell is selected, typing “=VLOOKUP(” will automatically display the function syntax.
Automatic Cell Range Adjustment
1. Select the entire data table and press [Ctrl] + [Shift] + [→] to easily select the table range when entering the VLOOKUP function.
Utilizing Excel VLOOKUP Function
Finally, here are some examples of how to utilize the VLOOKUP function in Excel:
1. Obtaining Prices from a Product Master List
You can input a product name or code and obtain the corresponding price.
2. Sales Data Aggregation
It is possible to extract data meeting specific conditions from a sales data table and calculate the total sales amount.
3. Database Joins
When combining multiple tables as an Excel database, the VLOOKUP function is very useful.