The VLOOKUP function, also known as VLOOKUP (if the sheet settings are in English) is typically used in Google Sheets to retrieve data from one table and add it to another. Apparently it seems complicated to apply however, once you understand how it works, it usually becomes one of the most used functions.
Let's see immediately what it does by applying it to a practical example.
From the table shown in the video, let's say we want to know the number of inhabitants of specific countries. Vertical search searches for a key across the first column of a defined range and returns the value of a specified cell in the found row.
This is the formula:
=VLOOKUP(G3, $B$3:$D$234.3, FALSE)
The expected parameters are therefore:
- the search key, which represents the value to be searched (explicitly defined, as "Italy" or with the reference to the relative cell);
- the second parameter is the interval to be considered for the search. Remember that the key will be searched in the first column of that range;
- then we have the index of the column of the value to be returned, where the first column has index 1, the second 2, the third 3 and so on;
- finally, we can specify if the list is sorted or not. A Boolean value (TRUE or FALSE) that indicates whether the column to be searched (the first column of the specified range) is sorted.
If set to FALSE:
the function verifies the exact correspondence with the value to search for;
it does not distinguish between uppercase and lowercase;
returns the content referring to the first match found even in the case of values repeated subsequently;
and if no match is found it returns N / A
If, on the other hand, the parameter is set to TRUE:
the function finds the closest match (less than or equal to) the search key.
Let's see what this concept means. Let's say we have the same table just created and that through an indicated value we want to find the country that has the closest number of inhabitants to this value. I invert the columns since VLOOKUP allows us to return data that is to the right of our reference value. In fact, this function does not natively allow you to search for values to the left of the first column of the range, however soon I'll show you how to overcome this limit with a little trick.
Returning to the example, I sort the values (it is important to underline that the sorting must be ascending, that is from the smallest to the largest value, otherwise an error will be returned, N / A) and at this point let's say we want to find the country with the number closest to 20 million.
The formula is as follows:
=VLOOKUP(K12, $G$13:$H$17, 2, TRUE)
Virtually identical to the previous one but with the fourth parameter set to TRUE. The result is precisely Chile, as it is the country on my list with the closest number of inhabitants to the one I was looking for.
A note: the parameter in question, the fourth note, can be omitted. In this case it would be interpreted as TRUE. However, because the vast majority of VLOOKUP use cases use FALSE, this value should be specified. 0 can also be used to indicate FALSE (hence 1 represents the numerical equivalent of TRUE).
If you are interested in learning more about the Boolean variables, I leave you the link to the dedicated lesson of my free course on simplified JavaScript for Google tools: • JavaScript Semplificat...
The last example that I show you, as anticipated, is how to be able to search for a value to the left of the first column, without having to physically invert the columns, as we did a little while ago and which is not a very convenient practice since it can make my table unintuitive starting point. It goes without saying that the country column form followed by the population column is certainly clearer than the one that sees the number of inhabitants before other information.
With a little trick we can tell the function to virtually sort the columns according to our needs. This is possible by creating a custom array using the braces {}.
I mean like this, where I'm telling the Sheet to create a range of 2 columns where the first column is the D and the second is the B.
={$D$ 2:$D$234, $B$2:$B$234}
However, inside VLOOKUP, I can insert it in place of the parameter relating to the interval, and this defines in a completely transparent way the structure of the new table in which to search, without the need to create a support table. So I can refer to a column that is after, in terms of position, compared to the one in which to retrieve the value and get the result. A trick that allows you to search with VLOOKUP to the left leaving the source table intact.
#GoogleSheets #Spreadsheet #cercavert
18 сен 2024
