How to do VLOOKUP in Excel with advanced examples and tricks

Have you ever encountered Navigating between rows and columns in Excel trying to combine data from different tables and not knowing how to speed up the process? If you work with complex spreadsheets, you know how tedious it can be to manually search for and relate information. Fortunately, there's a fundamental function that solves this task and saves you a huge amount of time: the VLOOKUP function.

In this article, I'll show you in detail how to use VLOOKUP in Excel, its advantages, how to write the formula correctly, various practical cases, and how to solve the most common problems, all with clear and practical examples. In addition, you will discover Tricks so you never get lost in cells again and get the most out of your data, both in Excel and in Google Sheets.

What is the VLOOKUP function in Excel?

VLOOKUP, called VLOOKUP in Spanish, is a function of Microsoft Excel and Google Sheets which allows you to search for a value in the first column of a table and return related data from another column in the same row. The term "vertical search" refers to searching down a column.

This feature is essential for those who need to relate data from different lists without having to compare them one by one. For example, you can search for the price of a product based on its code, the name of a student based on their registration number, or the department of an employee based on their ID.The key is that the lookup column contains unique values ​​that identify each item.

Imagine you have a sheet with ingredient orders for your restaurant and another sheet with the suppliers who supply those same ingredients. With VLOOKUP, you can retrieve the supplier's name, phone number, or delivery date for each ingredient in seconds, without having to copy anything by hand.

How does the VLOOKUP formula work?

The VLOOKUP formula is structured as follows:

=BUSCARV(valor_búsqueda; intervalo; índice_columna; )

Each element of the function has a specific function:

• search_value: This is the data you want to search for, usually a cell that contains the unique key, for example a product code or a person's name.
• interval: It is the range of cells where the data is located. The column in which the data is search_value must ALWAYS be the first column in that range.
• column_index: This is the column number, within the range, from which you want to return the result. Remember that the search column is number 1.
• : This is optional. Indicate if you want an exact match (FALSE or 0) or an approximate one (TRUE or 1). By default, it's an approximate match, but in practice, you'll almost always want to use an exact match.

A basic example to find the price of a fruit in a table would be:

=BUSCARV("Manzana"; A2:C10; 3; FALSO)

With this formula, Excel will search for "Apple" in the first column of the range A2:C10. If it finds it, it will return the value in the third column of that row (for example, the price).

Remember: VLOOKUP always searches from left to right. It can't search in columns to the left of the lookup column. If you need to search in reverse, you'll need to reorganize your data or use more advanced formulas.

What is VLOOKUP used for? Practical examples

VLOOKUP is extremely useful when you manage large volumes of data and need to cross-reference information between different tables. Here are some examples of typical usage:

• Relational employee data: You have a list of shifts and another list of names and positions. VLOOKUP helps you automatically fill in the position in the shift table using the employee number as the key.
• Match inventories with prices: From a list of products in stock, you can add the price of each one by searching for it in the price table.
• Update data automatically: Whenever information in the reference table changes (for example, suppliers), the data you bring in with VLOOKUP will be automatically updated.
• Search for information about students, books, clients, products, etc. quickly and automatically.

VLOOKUP is your best ally to stop copying and pasting and automate repetitive processes in Excel, dramatically increasing your productivity.

Step by step to create a VLOOKUP formula

Let's see how to create a VLOOKUP formula from scratch using a real-life scenario. Let's say you manage ingredient orders in a restaurant and have two tabs:

• Ingredient Orders: List of what to buy.
• List of suppliers: Include the supplier's name, phone number, delivery date, and other information related to each ingredient.

We want to add three columns to the order list: supplier name, phone number, and delivery date. To do this:

1. On the Ingredient Orders sheet, navigate to the cell where you want the supplier name to appear.
2. Press “=” to start typing the formula.
3. Write VLOOKUP( o VLOOKUP( if your Excel is in English.
4. Select the cell with the name of the ingredient you want to search for (for example, B5).
5. Type a comma and select the range where the supplier data is (for example, the table in the sheet List of Suppliers, from A3 to G13).
6. Press F4 to make the range an absolute reference (the $ signs will appear).
7. Write a comma, indicate the column number that contains the data you want to bring (for example, the supplier's name is in column 2, the phone number in column 7, the delivery date in column 5...)
8. Finally, write FALSE to search for exact matches only and close the parenthesis.

Your final formula for the supplier name might look like this: =BUSCARV(B5,'Lista de Proveedores'!$A$3:$G$13,2,FALSO)

For the phone, simply change the column number (e.g., 7), and for the delivery day, enter the corresponding index.

A little trick: if you copy and paste the formula below, make sure the reference to the lookup table is locked to avoid errors (that's why we use F4 and the $).

Key details about VLOOKUP syntax and arguments

Let's break down each part of the argument so we don't make mistakes and fully understand what we're introducing:

• Value to look for: It can be a text, a number, a reference to another cell… The important thing is that it is only in the first column of the range. Example: “102” or B5.
• Search range: Include the column with the data you want to search for and all the columns from which you want to extract information. Example: A2:D10.
• Column index: It is an integer and always starts counting from the leftmost column of the range (1 = lookup column, 2 = next, etc.). Cannot be less than 1 or greater than the number of columns in the range.
• Exact or approximate match: It's ALWAYS recommended to set FALSE to avoid surprises. Only use TRUE if your search column is sorted and you're looking for ranges.

And in Google Sheets? All of the above is 100% applicable, although in Sheets the arguments sometimes have slight variations, such as is_sorted.

Very useful examples of VLOOKUP

Here are several examples for different scenarios:

• Text search: =BUSCARV("Manzana";B4:D8;3;FALSO) → Returns the price of the apple
• Search by cell reference:=BUSCARV(G9;B4:D8;3;FALSO) → Find the value of G9 in the list
• Search by approximate match: =BUSCARV(102;A4:D8;2;VERDADERO) → If 102 does not exist, it gives you the closest value less than 102
• With variable column index: =BUSCARV(G3;B4:D8;2;FALSO) → Find the quantity according to the value of G3
• Combining criteria (in Google Sheets): If you need to search by first and last name, you can create a helper column that links the two and use it as a unique key.

If you have multiple rows that could match your search, VLOOKUP will always return the FIRST match found.

Common mistakes and how to fix them

The most common VLOOKUP error is #N/A, which means that the lookup value does not exist in the first column of the range. Let's look at the most common reasons and how to fix them:

• Duplicate data: If you have multiple records with the same key, only the first one will be displayed. Remove duplicates from your search column to avoid confusion.
• Leading/trailing spaces: If there are invisible spaces before or after your data, Excel won't return a match. Use the SPACES function to clean up your data.
• Incorrect table reference: If copying the formula shifts the range, the search will fail. Solution: Use absolute references (with $).
• Column index out of range: If you enter a number greater than the columns you have selected, the error #REF! appears.
• wrong order: If you use approximate matching without sorting the search column from lowest to highest, you may get erroneous results. Always specify FALSE unless you're sure of what you're doing.

You can customize the #N/A error by combining VLOOKUP with IFNA:

• =SI.ERROR(BUSCARV(...), "No encontrado") in excel
• =SI.ND(BUSCARV(...), "No encontrado") in Google Sheets

This will display a friendlier message than the usual error, which is useful if you share your spreadsheets with others.

Advanced tips and tricks to master VLOOKUP

1. Use absolute references in the range
Whenever you copy formulas down, make sure the search range doesn't change. So, after selecting the range, press F4 to enter the $ sign. This ensures that Excel/Sheets doesn't change it when you copy the formula.

2. Always sort the search column if you use approximate matching
If you really need to search for ranges (e.g., calculate a commission by range), sort the column where VLOOKUP searches from lowest to highest.

3. Work with clean data
Before using the function, remove any spaces and make sure numbers or dates are not saved as text. This prevents unexpected results.

4. VLOOKUP only looks to the right
If you need to look up values ​​on the left, you'll need to rearrange your columns or use functions like INDEX and MATCH, or switch to XLOOKUP, which is available in newer versions of Excel.

5. Use wildcards for partial matches
If you want to search for names that begin with the same name but don't know how they end, you can use asterisks (*) or question marks (?) in your search value with VLOOKUP, always using exact match (FALSE). Example: BUSCARV("La*";...; FALSO) will return the first data that begins with "La".

6. VLOOKUP on different sheets or books
You can search in tables located in another tab (sheet) or even in another Excel file. Just specify the sheet name and range like this: 'Hoja2'!A1:F20For other workbooks, open them first and select the range; Excel will automatically add the path.

VLOOKUP in Google Sheets: similarities and differences

If you are a Google Sheets user, the VLOOKUP function works almost the same as Excel, although there are slight changes in the argument names.:

• search_value: what you want to search for.
• interval: the range to search for and bring the result.
• hint: the column where to bring the data, within the interval (1 is the first column of the selected range).
• is_sorted: whether you are looking for an exact match (FALSE) or an approximate match (TRUE).

Additionally, Google Sheets includes the function SI.ND() to customize messages when the searched value is not found and SI.ERROR() for other general errors.

Another interesting detail is that you can name ranges instead of using cells, which simplifies your formulas and makes them more readable.

Limitations and alternatives to VLOOKUP

While VLOOKUP is very powerful, it has some limitations:

• You cannot search to the left of the search column.
• Returns only the first matching value.
• It can become slow with large volumes of data.
• It does not allow you to search for values ​​in descending order if your range is sorted differently.

In current versions of Excel, you can use SEARCHX (XLOOKUP), which solves many of these problems: it allows you to search both left and right, finds results in any column, and is more flexible.

Best practices for working with VLOOKUP

• Use unique keys: If your lookup column contains duplicates, clean the data before applying VLOOKUP.
• Keep the range sorted only if you use approximate matching. It's not necessary for an exact match, but it never hurts to have clean data.
• Lock range references with $ before copying the formula to other cells, this will prevent errors and unwanted shifts.
• Make sure dates and numbers are formatted correctly (not as text).
• Use IF.ERROR, IF.ND or IFNA to customize error messages, especially if you share the sheet with more users.

Related article:

How to Use VLOOKUP and XLOOKUP in Excel: Complete Guide and Practical Examples

Isaac

Passionate writer about the world of bytes and technology in general. I love sharing my knowledge through writing, and that's what I'll do on this blog, show you all the most interesting things about gadgets, software, hardware, tech trends, and more. My goal is to help you navigate the digital world in a simple and entertaining way.