How to Use VLOOKUP in Excel: A Complete Step-by-Step Guide

Understanding the VLOOKUP Syntax

Before writing a formula, it helps to understand what each part does. The vlookup syntax follows this structure:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Written out in full: lookup_value table_array col_index_num range_lookup are the four arguments that every VLOOKUP formula uses. Here is what each one means:

• lookup_value: The value you want to search for. This could be a name, employee id, product id, or any other identifier.
• table_array: The data range or table range containing your data. VLOOKUP always searches the first column of this range.
• col_index_num: The column index number, which tells Excel which column's value to return. If the first column holds the ID and the second column holds the name, entering 2 returns the name. The third column would be 3, and so on.
• range_lookup: The last argument, which controls match behaviour. Enter FALSE for exact match or TRUE for approximate match.

VLOOKUP requires the lookup column to be the first column in the table array, and it can only retrieve data from columns to the right of this column. It cannot look at columns to the left of the search key.

The vlookup function is used to search for information in a specified table and returns the value in the overlapping cell based on the provided row and column indices.

Method 1: Writing a Basic VLOOKUP Formula (Exact Match)

The most common way to use vlookup is with exact match mode. This is the safest option when you have a unique key such as an employee id or product id, and you need a precise result.

Step 1: Organise your data so that the lookup column is the first column in your table array. VLOOKUP works with vertical data and requires a table organized vertically, meaning each row represents one record.

Step 2: Click the cell where you want the result to appear.

Step 3: Type the formula. Using an employee table as the example:

=VLOOKUP(G2,A2:D9,2,FALSE())

Here, G2 is the lookup value (the employee ID you are searching for), A2:D9 is the table array (the cell range containing all your data), 2 is the col_index_num (returning the name from the second column), and FALSE specifies exact match.

Step 4: Press Enter. The formula returns the exact value matching your search key in the same row.

To avoid returning incorrect data, always use FALSE for exact matches in VLOOKUP. VLOOKUP defaults to approximate match mode if the range_lookup argument is omitted, which can result in unexpected and incorrect results if an exact match is needed.

In most cases, it is recommended to use vlookup in exact match mode (FALSE) when you have a unique key to ensure accurate results.

Method 2: Retrieving Data from Different Columns

Once you understand how the column index number works, you can retrieve data from any column in the table array by changing that single number.

Using the same employee table:

• =VLOOKUP(G2, A2:D9, 2, FALSE) returns the name (the second column in the range)
• =VLOOKUP(G2, A2:D9, 3, FALSE) returns the department (the third column)
• =VLOOKUP(G2, A2:D9, 4, FALSE) returns the salary (the fourth column)

The number of the column is always counted from the left edge of your table array, not from column A of the sheet. So if your table array starts at column C, the first column counted is column c itself.

VLOOKUP can only retrieve data from columns to the right of the lookup column, which limits its flexibility in data retrieval.

Method 3: Using Approximate Match for Ranges

Approximate and exact matching serve different purposes. The approximate match mode (range_lookup set to TRUE) is designed for situations where you are looking up a value within a range, not a specific record. A tax bracket table or a commission tier system are common examples.

When range_lookup is TRUE, VLOOKUP performs an approximate match, meaning it matches a range of values rather than a single exact value. VLOOKUP has two match modes: exact match and approximate match, both controlled by the last argument called range_lookup.

Important: For approximate match mode, the data table must be sorted in ascending order by the first column to avoid incorrect results. If the table provided to VLOOKUP is not sorted in ascending order, using approximate match mode can lead to incorrect results. For approximate match mode, the data table must be sorted in ascending order by the first column to avoid incorrect results. Vlookup supports approximate matching only when this sort requirement is met.

Example formula:

=VLOOKUP(B2, $E$2:$F$6, 2, TRUE)

This would find which bracket the value in B2 falls into and return the rate from the second column of the range.

Method 4: Using VLOOKUP with Absolute References When Copying Formulas

When you copy a vlookup formula across other cells, the table array reference shifts unless you lock it. When copying a VLOOKUP formula, lock the table range with $ signs to create an absolute reference. This ensures the table array does not shift when the formula is copied. This keeps the entire table reference fixed while the lookup value cell adjusts automatically.

=VLOOKUP(A2, $C$2:$F$50, 2, FALSE)

The dollar signs before C2 and F50 create absolute references and ensure the table array does not shift as the formula is copied down.

Method 5: Combining VLOOKUP with the MATCH Function

The match function can be nested inside VLOOKUP to create a dynamic column lookup. Instead of typing a fixed column number, MATCH finds the position of a column header automatically.

Using the match function within the VLOOKUP formula allows for a dynamic column index, enabling the retrieval of data from a column that can change position within the table.

=VLOOKUP(H2,A2:E6,MATCH(H3,A1:E1,0),FALSE())

Here, MATCH(H3, A1:E1, 0) looks up the column name stored in H3 within your header row and returns its position number. VLOOKUP then uses that number as the col_index_num.

When VLOOKUP is combined with MATCH, it enhances flexibility by allowing the user to specify the column to search based on a dynamic reference rather than a static number. The integration of MATCH with VLOOKUP helps to prevent errors that occur when the structure of the data changes, such as adding or removing columns.

The full vlookup lookup_value table_array col_index_num argument set still applies here; only the third argument becomes dynamic.

Using VLOOKUP with an Excel Table

If your data is formatted as an excel table (Insert > Table), VLOOKUP can use structured references instead of plain cell addresses. Structured references make formulas easier to read and automatically expand when new rows are added to the table.

=VLOOKUP([@EmployeeID], EmployeeTable, 3, FALSE)

Structured references also reduce the need to update absolute references manually and work well in large datasets where the row count changes regularly. Pivot tables can complement this approach by summarising the vlookup result data once it has been retrieved.

Common Issues and Troubleshooting

=IFNA(VLOOKUP(G2, A2:D9, 2, FALSE), "Not Found")

Quick Reference: VLOOKUP Modes and Use Cases

Vlookup lookup_value table_array arguments are always required. The last argument is technically optional, but omitting it can produce incorrect results in practice.

Limitations of VLOOKUP to Keep in Mind

VLOOKUP is a reliable excel function for most everyday tasks, but it has a few built-in constraints worth knowing before you rely on it in production models:

• It can only search the first column of the table array, meaning your search is limited to one column per lookup
• It returns only the first match, making it unsuitable when large datasets contain duplicate keys
• The col_index_num is a static number, which means inserting a new column into your table can shift the count and break formula returns
• Text values and numbers must match in type, or the search will fail silently

For better functionality, consider using XLOOKUP instead of VLOOKUP in newer excel versions. XLOOKUP is a new function available in Microsoft 365 and Excel 2021 that resolves the left-column and first-match limitations. That said, VLOOKUP remains widely supported across all excel versions and continues to work correctly for the vast majority of everyday lookups.

For Developers: Reading and Looking Up Excel Data with IronXL

If you are a .NET or C# developer and need to replicate VLOOKUP-style lookups programmatically, IronXL gives you a clean API for reading excel spreadsheets, iterating rows, and retrieving cell values without requiring Microsoft Office or Interop.

Install IronXL via NuGet:

Install-Package IronXL.Excel

The example below loads a workbook, reads a table of employee id and name pairs from an excel table, and performs a lookup equivalent to =VLOOKUP(lookupId, A2:D9, 2, FALSE):

using IronXL;

// Load the workbook
WorkBook workBook = WorkBook.Load("vlookup_demo.xlsx");
WorkSheet sheet = workBook.WorkSheets[0];

// Define the data range (equivalent to table_array in VLOOKUP)
string lookupId = "E004";
string lookupColumn   = "A"; // first column (Employee ID)
string returnColumn   = "B"; // second column (Name)
int dataStartRow = 2;
int dataEndRow   = 9;

string result = "Not Found";

for (int row = dataStartRow; row <= dataEndRow; row++)
{
    // Read the cell value from the lookup column
    string cellValue = sheet[$"{lookupColumn}{row}"].StringValue;

    if (cellValue == lookupId)
    {
        // Return the corresponding value from the return column
        result = sheet[$"{returnColumn}{row}"].StringValue;
        break; // Return the first match, same as VLOOKUP
    }
}

Console.WriteLine($"Employee Name: {result}");
// Output: Employee Name: David Lee

using IronXL;

// Load the workbook
WorkBook workBook = WorkBook.Load("vlookup_demo.xlsx");
WorkSheet sheet = workBook.WorkSheets[0];

// Define the data range (equivalent to table_array in VLOOKUP)
string lookupId = "E004";
string lookupColumn   = "A"; // first column (Employee ID)
string returnColumn   = "B"; // second column (Name)
int dataStartRow = 2;
int dataEndRow   = 9;

string result = "Not Found";

for (int row = dataStartRow; row <= dataEndRow; row++)
{
    // Read the cell value from the lookup column
    string cellValue = sheet[$"{lookupColumn}{row}"].StringValue;

    if (cellValue == lookupId)
    {
        // Return the corresponding value from the return column
        result = sheet[$"{returnColumn}{row}"].StringValue;
        break; // Return the first match, same as VLOOKUP
    }
}

Console.WriteLine($"Employee Name: {result}");
// Output: Employee Name: David Lee

Imports IronXL

' Load the workbook
Dim workBook As WorkBook = WorkBook.Load("vlookup_demo.xlsx")
Dim sheet As WorkSheet = workBook.WorkSheets(0)

' Define the data range (equivalent to table_array in VLOOKUP)
Dim lookupId As String = "E004"
Dim lookupColumn As String = "A" ' first column (Employee ID)
Dim returnColumn As String = "B" ' second column (Name)
Dim dataStartRow As Integer = 2
Dim dataEndRow As Integer = 9

Dim result As String = "Not Found"

For row As Integer = dataStartRow To dataEndRow
    ' Read the cell value from the lookup column
    Dim cellValue As String = sheet($"{lookupColumn}{row}").StringValue

    If cellValue = lookupId Then
        ' Return the corresponding value from the return column
        result = sheet($"{returnColumn}{row}").StringValue
        Exit For ' Return the first match, same as VLOOKUP
    End If
Next

Console.WriteLine($"Employee Name: {result}")
' Output: Employee Name: David Lee

This approach mirrors how vlookup works in Excel: it iterates the first column of the data range and returns the corresponding value from the specified column on the same row. For large datasets, you can extend this into a dictionary-based lookup for faster performance.

IronXL also supports reading column e and any other column using cell address notation, iterating through structured references in named tables, and exporting results back to XLSX without any Office dependency.

Start with a free trial to test IronXL in your own project. Full documentation is available at ironsoftware.com/csharp/excel/.

Further Reading:

• Read Excel Files in C# with IronXL
• Fetch Data from an Excel Sheet in C#
• C# Excel Get Cell Value Tutorial

Wrapping Up

Knowing how to use vlookup in excel opens up a wide range of practical workflows: pulling names from an ID list, matching prices to product ids, retrieving department data from an employee directory, and much more. For everyday lookups with a unique key, exact match (FALSE) is almost always the right starting point. When you need a range-based result, approximate match (TRUE) works well as long as the data is sorted in ascending order. For more flexibility, the index match combination or nesting the match function inside excel vlookup removes the left-column restriction entirely.

If you are building .NET applications that work with excel data at scale, IronXL makes it possible to read, search, and retrieve data from excel spreadsheets without a single line of Office Interop code. Grab a free trial and see how quickly you can add spreadsheet intelligence to your application.