Skip to content
en
Practical GuidesAll tools

Categories

Developer ToolsEncoding & DecodingJSON & DataExcel ToolsHash & ChecksumText ToolsDate & TimeNetwork ToolsFile ToolsMarkdown & Diagrams

Excel VLOOKUP Formula Generator

Fill in four fields and get a VLOOKUP with the right last argument, an optional IFERROR and warnings about the classic pitfalls.

Processed locally in your browser

Options

Usually a cell such as A2. Text you type is quoted for you.

The first column must contain the lookup value.

1 is the first column of the table.

Excel uses ";" when the regional settings use a decimal comma. Decimal numbers are then written with a comma too.

What is Excel VLOOKUP Formula Generator?

VLOOKUP finds a value in the first column of a table and returns a value from another column of the same row. It is the most used lookup in Excel and also the easiest to get subtly wrong: the fourth argument silently defaults to an approximate match, the column index is a bare number, and the key must be in the leftmost column.

This generator asks for the lookup value, the table array and either a column index or the column letter you want back, in which case it works out the index from the first column of the table. You choose exact (FALSE) or approximate (TRUE) matching and whether to wrap the result in IFERROR with your own not-found text. Warnings point out the inserted-column risk and an index beyond the table, and XLOOKUP and INDEX with MATCH versions are shown as safer alternatives.

How does it work?

  1. Enter the lookup value, usually a cell like A2, and the table array such as Sheet2!A:D, whose first column holds the key.
  2. Give the column index (1 is the first column of the table) or type the letter of the column to return, for example D.
  3. Keep "Exact match" unless the table is sorted and you want a range lookup, and tick IFERROR to show your own text when nothing is found.
  4. Copy the formula. Use the XLOOKUP or INDEX and MATCH equivalent if the table may gain or lose columns.

Common use cases

  • Looking up a product price or a customer name by ID in another sheet.
  • Adding a status or department column to a list by matching a code against a reference table.
  • Writing the exact-match form (FALSE) that avoids wrong rows from an approximate match.
  • Turning a #N/A-filled column into a clean one by wrapping the lookup in IFERROR with a friendly message.

Examples

Fill in four fields and get a VLOOKUP with the right last argument, an optional IFERROR and warnings about the classic pitfalls.

Privacy

Excel VLOOKUP Formula Generator runs entirely in your browser. The text or files you provide are processed on your device and are not uploaded, logged or stored on our servers.

Limitations

VLOOKUP can only return columns to the right of the lookup column, and the generator cannot see your data, so it cannot verify that the key exists or that the table is sorted for an approximate match.

Frequently asked questions

Why do I get #N/A even though the value is in the table?

The usual causes are an approximate match on unsorted data, a lookup value with trailing spaces, a number stored as text on one side only, or a lookup value that is not in the first column of the table array. Use FALSE for an exact match and TRIM on the key if the data comes from an import.

What is the difference between FALSE, TRUE and 0 or 1?

FALSE (or 0) asks for an exact match. TRUE (or 1), and leaving the argument out, asks for the closest smaller value and requires the first column to be sorted ascending. Approximate matching suits bands such as commission rates, not IDs.

How does the column letter option work?

VLOOKUP counts columns from the start of the table array, not from column A. If the table is B:F and you want column D, the index is 3. Type D and the generator computes it, and warns when the letter lies left of the table.

Related tools

More tools in Excel Tools →