VLOOKUP Function

Summary

The Excel VLOOKUP function searches for a value in the first column of a table and returns a value from another column in the same row. It is one of the classic lookup functions in Excel and is still widely used in existing workbooks.

VLOOKUP works well when your table is vertical: the lookup key is in the leftmost column, and the values you want to return are in columns to the right. Typical examples include employee IDs, product codes, and sorted threshold tables.

Its main limitation is structural: VLOOKUP can only look to the right. If the return column is to the left of the lookup column, another approach such as XLOOKUP or INDEX with MATCH is usually better.

Syntax

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

lookup_value is what you want to find in the first column, table_array is the full lookup table, col_index_num is the return column number inside that table, and range_lookup controls whether the match is exact or approximate.

The column index is counted relative to the selected table, not the worksheet. If the table is A2:C5, then column 1 means A2:A5, column 2 means B2:B5, and column 3 means C2:C5.

Arguments

• lookup_value - The value to search for in the first column of the table.
• table_array - The range containing both the lookup column and the return columns.
• col_index_num - The column number inside the selected table to return from.
• range_lookup - [Optional] Use FALSE for exact match or TRUE for approximate match.

Exact match is the safer default for IDs, names, and codes. Approximate match should be used only when the first column is sorted in ascending order and the table represents ranges or thresholds.

VLOOKUP vs Other Functions

VLOOKUP is often compared with other lookup functions because the right choice depends on table layout and flexibility requirements.

Use VLOOKUP when the table already matches its required layout. If the table structure is different, it is usually better to choose a function that fits the layout rather than force the layout to fit VLOOKUP.

Using VLOOKUP

VLOOKUP is most commonly used for exact-match joins. For example, an employee ID can return a name, a product code can return a price, or a status code can return a label. In these cases, FALSE is usually the correct match mode.

It can also be used in sorted threshold tables with approximate match. In that pattern, VLOOKUP returns the row for the largest value less than or equal to the lookup value. That is why it often appears in tax bracket or discount tier examples.

One common weakness is the hard-coded column index. If the table changes, a fixed index such as 3 may start returning the wrong column. Using MATCH or COLUMNS() can make that part of the formula easier to maintain.

• Use FALSE for exact matching unless you are working with sorted threshold data.
• Make sure the lookup key is in the first column of the table.
• Check whether a hard-coded column index could become fragile if the table layout changes.

Example 1 - Exact Match Record Recovery

This formula searches for ID 102 in the first column and returns the value from column 2 of the same row. It is the standard VLOOKUP pattern for ID-based retrieval.

This is a good beginner example because it shows the core shape VLOOKUP expects: key on the left, return value on the right. When the table follows that layout, the formula is very direct.

=VLOOKUP(102, A2:C5, 2, FALSE)

Example 2 - Fuzzy Wildcard Search

In exact-match mode, wildcards such as * can be used in text lookups. This is useful when you only know part of a name or code and want the first matching row.

This makes the example practical for partial names and partial codes. You do not always need the full text, as long as the stable part is enough to identify the record.

=VLOOKUP("John*", A3:C4, 3, FALSE)

Example 3 - Automated Tax Tier Logic

This formula uses approximate match on a sorted income table. For an income of 85,000, the lookup lands on the 80,000 row and returns its associated rate.

This is useful because it shows a different style of VLOOKUP. Instead of finding one exact label, it places a value into the right bracket of a sorted table.

=VLOOKUP(85000, H2:I6, 2, TRUE)

Example 4 - Relative Dynamic Index Audit

Using COLUMNS(A3:B3) returns 2, so the formula retrieves the second column of the selected table. This pattern can make a VLOOKUP easier to maintain than a fixed number if the formula is copied or expanded.

That helps reduce one of the common weaknesses of VLOOKUP: the hard-coded return column. A dynamic index can make the formula a bit safer when the layout changes.

=VLOOKUP(102, A3:C3, COLUMNS(A3:B3), FALSE)

Conclusion Recap

VLOOKUP is still one of the most common lookup formulas because the basic idea is easy: search the first column, then bring back something from the same row. In this lesson, that covered exact ID lookups, wildcard text searches, sorted tax brackets, and ways to make the return column more stable.

The main habit to build is setting the table up correctly before you type the formula. Put the lookup key in the leftmost column, use FALSE for normal exact matches, and be careful with hard-coded column numbers if the table may change later.

• Summary: VLOOKUP searches the first column of a table and returns a value from a later column in the same row.
• Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
• Core setup: Put the lookup key in the leftmost column and use the correct match mode.
• Best use: Vertical lookup tables such as ID lists, price tables, and sorted bracket schedules.