To analyze and visualize data, you need to use different Excel functions.
But what if you need to transfer information from one table to another, and everything is out of order?
Ctrl+C and Ctrl+V won't help.
The authors of Skillbox have prepared a guide for you on how to use the search function Vlookup.
Stage 1: Preparation
Let's imagine that you have a personal car rental business. And you need to call each client to tell them the cost of renting a car selected on the website.
There are two tables: with customer contacts and with car data. Remember that Vlookup works if the tables are located within the same file on different tabs.
First, build the function: select the cells where the values will be transferred. Then add a column with the price and select the cell opposite the first client.
Open the "Formulas" tab and click "Insert Function."
A window for building formulas will appear.
Stage 2: Filling in the function arguments
The lookup value is a cell with the same data for both tables. The function will use it to find the information to transfer.
The car price should be transferred to the table with clients. To do this, place the cursor in the "Lookup value" box and select the first value in the "Make, model" column. This is cell A2.
The value will be transferred to the formula builder and will appear in the reference row formula.
Table — the cells from which the function will take information for the desired value. These are the columns with the desired value and the value that needs to be transferred to the first table: the columns with the car make and price, respectively.
To do this, place the cursor in the "Table" box in the formula builder. Then select the range containing the columns with the make and price: A2:E19. Lock the range: select the range value in the row with links and press F4. The value will be transferred to the formula builder and will appear in the link row formula.
The column number is in the first table, where the value being transferred is located. In the example, this is the fifth column with the price. To select the number in the formula builder tab, enter "5" in the "Column Number" field.
Range lookup will adjust the function's precision. To make the value exact, enter "0" in the "Range lookup" field; if you need an approximate value, enter "1." For this example, we need exact car prices, so enter "0." The value will appear in the formula in the reference row.
Stage 3: result Vlookup
To transfer car prices, click "Done" in the formula builder and drag this value down to the end of the table. That's it! In a few minutes, you can start calling clients and closing deals.
That's the guide! The Skillbox authors hope they've made working with Excel easier for you. Of course, there are many more life hacks for working with the program.
If you want to master everything, take the course"Excel + Google Sheets from Zero to PRO".
46 modules and practical tasks await you. The training will be based on company cases and led by curators from MIF Publishing House, Marriott, Avtomir, and Yandex. And you can study at a convenient time from anywhere in the world.
Hurry!