Remembering VLOOKUP and using it safely is not so simple, especially with so many complicated instructions.
Or maybe not?
With examples drawn from everyday life, you will have mastered the VLOOKUP function by the end of this article and made a great leap forward on the journey to becoming proficient in Excel.
1) What does VLOOKUP mean?
VLOOKUP is called “VLOOKUP” in English, which could have been better translated as “search” or “lookup”.
VLOOKUP = search/lookup
That already sounds more intuitive than VLOOKUP.
2. where do we use VLOOKUP in everyday life?
Example: web search
Where do we already conduct searches every day?
For instance on Google or Wikipedia.
Imagine we want to search for the population of France.
1) We go to Google and enter our search term “France” and the sought information “population”. We land on a Wikipedia article that presents the population by country in a table. For example here: Wikipedia
2) We now look in the table for the entry for “France”, then we move to the column where the information for France is located, for example, in the sixth column. We now have the result we were looking for or consulting.
This is exactly what the VLOOKUP function in Excel does, which means that if we can search for something online and extract values from tables, we can also safely use the VLOOKUP function.
The only problem is that Excel is not really intuitive and we have to remember what it expects from us for the different functions to work.
3. VLOOKUP in Excel: What do the different parts of the formula represent?
Now let’s look at what the different parts of the VLOOKUP formula represent.
=REFERENCE(Lookup criterion; Array; Column index; [range_lookup])
VLOOKUP = represents the vertical search from top to bottom
There is also a
HLOOKUP = represents the horizontal search from left to right (if we want to see, for example, which subject is taught on Wednesday in the third period. We first look for the correct day of the week in the column headers before moving down 3 rows).
Lookup criterion = represents the term we are searching for.
Array = represents the area in which we are looking for our information.
Column index = represents the column in which the sought information is located
[Range_lookup] = indicates whether we are looking for an exact or approximate match (this parameter is optional.)
Optional parameter
By default, VLOOKUP is set to approximate match, meaning TRUE or 1.
However, we often need an exact match, which is why it is important to enter FALSE or 0 at the end of the VLOOKUP function!
TRUE / 1: approximate match
FALSE / 0: exact match
4. application of VLOOKUP in Excel
1) Where do we place VLOOKUP?
Under a field in which we enter a search term. Here, the name “Martin” is our search term.
On a table to which we want to add a column.
The advantage is that it is very easy to extend the formula downwards.
2) What should I search for?
You have the option to write the search term directly into the formula or, better yet, link it to a cell. In the case of a link, it is often recommended to fix it with the $ sign. It is best to jump into the formula with F2, select the reference, and scroll through the different types of references with the shortcut F4.
3) Where should I search?
We now need to select the domain in which VLOOKUP should perform its search. The search term is looked up in the first column.
The range must also include all columns where we are looking for the information.
It is often recommended to extend the area to the entire table if we are looking for further information afterwards. This way, it is not necessary to adjust the search range again.
4) What do I want to know?
We now need to see in which column the information is found.
Here, we have the option to either enter a number directly into the formula or get the column number from a cell using a link. In our example, the information about salary amount is in the 2nd column of the search area.
If you want to spare yourself the manual counting of columns, you can also combine the VLOOKUP function with the MATCH function.
5) Exact or approximate match?
Exact match
In case of an exact match, the exact lookup term is searched for. For this, we need to enter FALSE or 0 at the end of the VLOOKUP function. If the lookup term cannot be found, the error message #N/A (not available) is displayed.
Approximate match
For approximate match, you must enter TRUE or 1 at the end of the VLOOKUP function. In the following example, we want to see what salary level the salary belongs to. For this, the salary levels must be sorted in ascending order.
The VLOOKUP goes through each entry from top to bottom and checks if the entry in the lookup array is greater than the value sought. If it is, the VLOOKUP moves up one entry.
Example: If we are looking for Heinze’s salary (€42,128), VLOOKUP goes through all entries of the lookup range until it finds the value €44,000. This value is greater than €42,128. Then, the VLOOKUP moves up one entry to €42,000 and returns the rate A2.
The VLOOKUP interprets the entries of the lookup range as follows:
€40,000 or more = A1; €42,000 or more = A2, etc.
5. what should you be aware of with VLOOKUP?
Frequent error messages:
#N/A
This error message appears when the lookup term could not be found. This may be for the following reasons:
The lookup term does not really exist.
There was a mistake in the lookup term, or the term contains spaces that need to be removed beforehand. The TRIM() function can help in this case. It removes invisible spaces at the beginning and end of a text.
The lookup term is a number, but Excel interprets it as text. For this, we need to change the format of the cell containing the lookup term to “number”. It is also possible to multiply the lookup term by 1 (*1), which makes Excel recognize the lookup term as a number.
The lookup range is not correctly defined. Note: VLOOKUP always searches in the first column of the lookup range.
#REF!
This error message appears if we have indicated a column in the column index that is outside the lookup range. For example: for a lookup range with only two columns, we indicate the number 3 as the column index.
#VALUE!
This error message appears if we enter 0 or a negative number in the column index. VLOOKUP always looks to the right, so it is not possible to search to the left of the first column.
#NAME?
This error message appears if we have made a mistake in the function and entered for example “=serweis” instead of VLOOKUP.
6. VLOOKUP sample file
To see the formulas, you can either download the file at the bottom right or view the workbook in full size and then go to “Edit Workbook”.
7. conclusion
We have now covered everything important regarding the VLOOKUP in Excel and you have come to know one of the most widely used formulas in Excel. Try to apply the formula right away because practice makes perfect!