Excel VLOOKUP (VERT.ZOEKEN) explained step by step
Excel VLOOKUP (VERT.ZOEKEN) explained: From beginner to expert
The VLOOKUP function, known as VERT.ZOEKEN in the Dutch version of Excel, is one of the most widely used and powerful functions in Microsoft Excel. With this function you can automatically look up data in large tables, which can save you a huge amount of time. In this comprehensive guide, we explain step by step how VLOOKUP works and how you can use the function yourself.
What exactly is VLOOKUP?
VLOOKUP stands for Vertical Lookup. The function searches for a value in the first column of a table and then returns a value from another column in the same row. Imagine you have a phone book: you look up a name (first column) and get the phone number (another column) back.
When do you use VLOOKUP?
This function is particularly useful in many practical situations:
Searching price lists: You type in an item number and Excel automatically looks up the matching price.
Looking up employee data: Enter an employee number and instantly see the name, department or other details.
Creating invoices: Automatically link product numbers to product descriptions and prices.
Merging reports: Combine data from different sources based on a common key.
The structure of VLOOKUP
The VLOOKUP function consists of four parts, which are called arguments. Each argument has a specific purpose:
The four arguments explained
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Argument 1, Lookup value: This is the value you want to look up. It can be a number, text or a cell reference. Excel searches for this value in the first column of your table.
Argument 2, Table array: This is the range in which Excel should search. It includes the column with lookup values and the columns you want to pull results from.
Argument 3, Column index: This number indicates which column of the table array you want the result from. Column 1 is the first column of your range, column 2 the second, and so on.
Argument 4, Range lookup: Here you enter FALSE or 0 for an exact match, or TRUE or 1 for an approximate match. In most cases you want to use FALSE.
Creating your first VLOOKUP formula
Let's get started with a practical example. Say you have a price list with products.
Step 1: Prepare your data
First create a table with your data. For example:
| Product number | Product name | Price |
|---|---|---|
| 1001 | Laptop | 899 |
| 1002 | Monitor | 299 |
| 1003 | Keyboard | 79 |
| 1004 | Mouse | 39 |
Make sure the column you want to search on (in this case Product number) is always the first column of your table.
Step 2: Set up the lookup
Create a cell somewhere in your worksheet where you can enter a product number (for example cell F2). In another cell (for example G2) you place the formula.
Step 3: Enter the formula
Click the cell where you want to see the result and type the following formula:
=VLOOKUP(F2,A2:C5,2,FALSE)
Let's break this formula down:
- F2: The cell where you enter the product number
- A2:C5: The range of your data table
- 2: You want the product name (column 2 of the range)
- FALSE: You want an exact match
Step 4: Test it
Now type product number 1002 in cell F2. Excel looks up this number and returns Monitor as the result.
Practical examples of VLOOKUP
To really understand the function, let's look at a few more real-world examples.
Example 1: Looking up a discount percentage
Say you have a table with customer tiers and their discounts:
| Customer tier | Discount |
|---|---|
| Bronze | 5% |
| Silver | 10% |
| Gold | 15% |
| Platinum | 20% |
With the formula =VLOOKUP("Gold",A2:B5,2,FALSE) you get 15% as the result.
Example 2: Looking up employee data
You have an employee list and want to quickly find an employee's department:
| Employee no. | Name | Department | Start date |
|---|---|---|---|
| P001 | Jan de Vries | Sales | 15-01-2020 |
| P002 | Maria Bakker | Administration | 03-05-2019 |
| P003 | Piet Jansen | Marketing | 22-08-2021 |
The formula =VLOOKUP("P002",A2:D4,3,FALSE) returns Administration as the result.
To look up the start date, change the column index to 4:
=VLOOKUP("P002",A2:D4,4,FALSE)
Common errors and how to avoid them
When working with VLOOKUP, you may run into various error messages. Here we explain what they mean and how to fix them.
The #N/A error
This error means Excel can't find the lookup value. Possible causes:
Typos: Check that the lookup value exactly matches the value in the table, including spaces.
Wrong range: Check that your table array is correct and includes the lookup column.
Case sensitivity: VLOOKUP is not case-sensitive, but extra spaces can cause problems.
The #REF! error
This error appears when the column index is larger than the number of columns in your table array. If your range contains three columns, the column index can be at most 3.
Tips to prevent errors
Use absolute references: When you copy the formula, the range can shift. Use dollar signs to prevent this: $A$2:$C$5.
Use IFNA: Catch the #N/A error with: =IFNA(VLOOKUP(F2,A2:C5,2,FALSE),"Not found")
VLOOKUP with approximate match
So far we have used FALSE for exact matches. There are situations where TRUE is useful.
When should you use approximate match?
Approximate match works well for lookup tables with ranges, such as tax brackets or tiered discounts:
| Minimum order | Discount |
|---|---|
| 0 | 0% |
| 100 | 5% |
| 500 | 10% |
| 1000 | 15% |
With =VLOOKUP(750,A2:B5,2,TRUE) you get 10%, because 750 falls between 500 and 1000.
Important: With approximate match, the first column must be sorted in ascending order!
Alternatives to VLOOKUP
Although VLOOKUP is very useful, there are newer functions that remove some of its limitations.
XLOOKUP (X.ZOEKEN)
XLOOKUP is available in newer versions of Excel. This function can also search to the left and has built-in error handling.
INDEX and MATCH
This combination of functions is more flexible than VLOOKUP and can search in any direction. The formula is more complex, though.
Tips for effective use
Organize your data: Make sure the lookup column is always to the left of the columns you want to pull data from.
Avoid duplicates: VLOOKUP only returns the first result when there are multiple matches.
Document your formulas: Add comments so you still know later what the formula does.
Test thoroughly: Check your formulas with different lookup values before you put them into production.
Frequently asked questions
Can VLOOKUP search to the left?
No, VLOOKUP can only return values from columns to the right of the lookup column. If you want to search to the left, use the INDEX/MATCH combination or the newer XLOOKUP function, which is available in Excel 365 and Excel 2021.
Why do I get an #N/A error even though the value is in the table?
This is often caused by invisible spaces or different formatting. Check that there are no extra spaces before or after the text. You can use the TRIM function to remove extra spaces. Numbers stored as text can also cause problems.
How do I make VLOOKUP case-sensitive?
VLOOKUP is not case-sensitive by default. If you want to distinguish between uppercase and lowercase letters, you can combine the INDEX and MATCH functions with the EXACT function, or create a helper column with the actual comparison.
Can I use VLOOKUP with multiple criteria?
Not directly, but there are workarounds. You can create a helper column that combines multiple fields, or use the INDEX/MATCH functions with multiple conditions. In newer Excel versions, XLOOKUP with arrays also offers options for multiple criteria.
0 van 0 vonden dit nuttig
