Excel function

VLOOKUP in Excel: look up values from a table

VLOOKUP searches the first column of a table and returns a value from another column in the same row. It is older than XLOOKUP, but it is still everywhere in real spreadsheets.

What VLOOKUP does

VLOOKUP means vertical lookup. It searches down the first column of a table array and returns a value from a column to the right. The basic syntax is `=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`.

For most beginner and business tasks, use exact match with `FALSE`: `=VLOOKUP(A2,F:G,2,FALSE)`. This looks for A2 in the first column of F:G and returns the value from the second column of that table. `FALSE` tells Excel not to accept a nearby or approximate match.

VLOOKUP is useful for price lists, employee tables, customer IDs, grading tables, and simple reference lists. It works best when your lookup value is in the leftmost column of the table array and the answer is somewhere to the right.

Arguments explained

`lookup_value` is the value you want to find, such as the item code in A2. `table_array` is the full lookup table, such as F:G or `$F$2:$G$20`. VLOOKUP always searches the first column inside that table array.

`col_index_num` is the number of the column to return from within the table array. In F:G, column F is 1 and column G is 2. A common mistake is using the worksheet column number instead of the table-array column number. If your table array starts in F, returning from G still uses `2`, not `7`.

`range_lookup` controls exact or approximate match. Use `FALSE` for exact match when looking up IDs, codes, names, or categories. Approximate match can be useful for sorted tier tables, but it is risky when used accidentally.

Practical examples

A beginner example is returning a price. If A2 contains an item code, F:F contains item codes, and G:G contains prices, `=VLOOKUP(A2,F:G,2,FALSE)` returns the matching price.

A school example is finding a student's house or group from a roster. If B2 contains the student ID and J:L contains ID, Name, and Group, `=VLOOKUP(B2,J:L,3,FALSE)` returns the group. The table array has three columns, so Group is column index 3.

When to use it

Use VLOOKUP when you are working in an older workbook, collaborating with people who expect it, or performing a simple right-side exact match. It is also worth learning because many Excel interview and workplace files still contain it.

Do not use VLOOKUP when the return column is to the left of the lookup column, unless you rearrange the table. Do not use it when inserted columns may break the column index number. XLOOKUP is often clearer in modern Excel, and INDEX with MATCH remains useful for flexible lookup patterns.

Practical examples

Exact match VLOOKUP

=VLOOKUP(A2,F:G,2,FALSE)

Looks for A2 in the first column of F:G and returns the matching value from the second column.

Return the third table column

=VLOOKUP(B2,J:L,3,FALSE)

Searches the first column of J:L for B2 and returns the value from the third column in that table array.

Recommended Excel functions

VLOOKUPXLOOKUPINDEXMATCHIFERROR

Common mistakes

  • 01Omitting FALSE and accidentally allowing approximate match behavior.
  • 02Using the worksheet column number instead of the table-array column index.
  • 03Trying to return a value from a column to the left of the lookup column.
  • 04Breaking formulas after inserting or moving columns inside the table array.

Practice

Practice this skill with interactive exercises on Calcu.

Use Calcu to practice VLOOKUP in realistic spreadsheet exercises with instant feedback.

Open Calcu

FAQ

What does FALSE mean in VLOOKUP?

FALSE tells VLOOKUP to use exact match, which is usually required for IDs, codes, names, and categories.

Can VLOOKUP look left?

No. VLOOKUP searches the first column of the table array and returns values from columns to the right.

Is VLOOKUP obsolete?

No. XLOOKUP is more flexible in modern Excel, but VLOOKUP is still common in existing workbooks and remains useful to understand.