The VLOOKUP function of Google Sheet makes it easier to find precise information in tables with a lot of data. VLOOKUP, also called VLOOKUP in Google Sheets, performs a search in a column, from top to bottom, respecting the cells you specified and the values you gave themvez assigned. Even if the files contain large amounts of data, the vertical search function allows you to find the data quickly. We will explain how to use this function, and we will justify our explanation using several examples.
- What exactly is the VLOOKUP function of Google docs?
- Which syntax to respect with VLOOKUP from Google Sheet?
- VLOOKUP in Google Sheets: a point-to-point example
What exactly is the VLOOKUP function of Google docs?
Using spreadsheets, users can process data in tables quickly and efficiently. Advanced users are familiar with a whole series of very practical functions which make working with Excel or Google Sheet easier, and make it even more efficient. VLOOKUP is a function that allows you to find information and values in a short time, and in the middle of a bigquantity of data, contained in large tables.
How do you use VLOOKUP?
Even Microsoft's spreadsheet software offers this function. The VLOOKUP in Excel function works the same as the Google variant.
Let's take an example: you have a large array that contains customer names, customer numbers and addresses . A column specifies for each customer, the product category in which the customer generally orders items on your online sales site. You call the customer on the phone, and you would like to know very quickly which product category he hassigned. Using the customer number and the VLOOKUP function of Google Doc, you will quickly be able to find the value corresponding to the search criterion, without having to manually browse the entire list in search of the desired information.
Which syntax to respect with VLOOKUP from Google Sheet?
As with any other function, VLOOKUP must follow a precise syntax. If the formula is not written correctly, Google Sheets will not be able to perform the automatic return of the value. The VLOOKUP function looks like this:
= VLOOKUP (Searchkey; range; index; [is_sorted])
The " Search key " represents the value you are looking for. In the " range " you define the part of the array in which you want to search for the value. " Index " represents the column index of the value to display. In the formula, the index is simply indicated by a number. If your search key is in column C, and you are looking for a matching value in column F, you should save the value 3 for the index, because column F is three columns away from column C.
With the Google spreadsheet, you can use either the English function" VLOOKUP "or its French equivalent" RECHERCHE V ": depending on the language setting of your Google account, the software will automatically translate the name of the function.
The switch “[is_sorted]” indicates whether the column in which the search will be performed is an already sorted column or not. If the column is not sorted, we enter “FALSE”, and if it is already sorted, we enter the option “TRUE.” If nothing is specified, Google Sheets assumes that it is “TRUE.” Although this parameter is optional, it has a large importance for the result.
- FALSE : the function searches for an exact match and returns the firstfirst match found.
- TRUE : The function doesn't necessarily search for an exact match, but returns you the closest match to the search key.
Let's take the following VLOOKUP in Google Sheets as an example:
= VLOOKUP (A10; A2: C10; 3; FALSE)
With this formula, you are asking Google Sheets to perform a vertical search in the column that is 3 columns away from column A. The range in which the search is performed is A2 to C10. The search criterion is specified in cell A10. Google Sheets should return you the corresponding value from column C.
VLOOKUP dans Google Sheets: a point-by-point example
Things will be simpler if we take a real example of VLOOKUP. Let's use the following table as a starting point for this.
This Google spreadsheet lists all the customers of a store in line.
Step 1 : insert, at a location of your choice, below or next to the table, a workspace in which you will perform your research. Just enter "Key" and "Result" in the header. In the first line, below "Key", type the cell value, for which you want to find the matching result.
You would like to know the product category in which customer no. "785490351" orders the most items. Enter as a search key, the customer number in cell A9.
Step 2 : type the VLOOKUP formula in Google Docs, where you want your search results to appear. In our example, this is cell B9. Enter the following formula:
= VLOOKUP (A9; $ A $ 2: $ E $ 6; 5; FALSE)
With this formula, you request the return of the value , five columns further than the value corresponding to the search key in column A. Google Sheets then performs a vertical search, from top to bottom, and searches for the value “785490351.” It then reports the result of this search and display it in column B.
Step 3 : in the column where you typed the VLOOKUP formula, the result“ Mode ”is displayed.
The result of the search key is then displayed.
Step 4 : any customer number from column A can be specified as a search key. Adapt the formula to the corresponding location, or click in the "Mode" field, and drag the small blue rectangle downwards, in the cell just below. The formula is thus automatically adjusted. and gives you the desired result.
Depending on the search key you enter in the formula, and the index you save, the program will display the corresponding results. On line 11, for example, the search was for the customer's postal code number "184390092". On line 12, the search was for customer name number 236849356. To do this, we had to define the range of the table that had to be scanned.