Skip to content
Excel

VLOOKUP function in Excel

Looks in the first column of an array and moves across the row to return the value of a cell

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

VLOOKUP syntax and parameters

Four arguments, three required.

  • lookup_valueanyRequired

    The value you want to look up. The value you want to look up must be in the first column of the range of cells you specify in the table_array argument.For example, if table-array spans cells B2:D7, then your lookup_value must be in column B.Lookup_value can be a value or a reference to a cell.

  • table_arrayrangeRequired

    The range of cells in which the VLOOKUP will search for the lookup_value and the return value. You can use a named range or a table, and you can use names in the argument instead of cell references.The first column in the cell range must contain the lookup_value. The cell range also needs to include the return value you want to find.

  • col_index_numnumberRequired

    The column number (starting with 1 for the left-most column of table_array) that contains the return value.

  • range_lookupbooleanOptional

    A logical value that specifies whether you want VLOOKUP to find an approximate or an exact match:Approximate match - 1/TRUE assumes the first column in the table is sorted either numerically or alphabetically, and will then search for the closest value. This is the default method if you don't specify one. For example, =VLOOKUP(90,A1:B100,2,TRUE).Exact match - 0/FALSE searches for the exact value in the first column. For example, =VLOOKUP("Smith",A1:B100,2,FALSE).

VLOOKUP examples

Formulas you'll actually reuse.

  1. The price for the SKU in E2 — FALSE forces an exact match:

    =VLOOKUP(E2, A2:C200, 3, FALSE)

    Result24.99

  2. A tier from spend with an approximate match — thresholds sorted ascending:

    =VLOOKUP(B2, {0, "Bronze"; 1000, "Silver"; 5000, "Gold"; 20000, "Platinum"}, 2, TRUE)

    Result"Gold"

VLOOKUP in Google Sheets

Same name — your formula ports as-is.

Try VLOOKUP in the playground

Edit the example — nothing to install.

Preloaded with the VLOOKUP formula from Example 1 — change anything and watch it respond.

Loading the editor…

VLOOKUP errors

What they mean — and the fixes.

  • #N/A

    No exact match — check for trailing spaces, numbers stored as text, or a lookup value that isn't in the FIRST column of the range.

  • #REF!

    The column index is larger than the number of columns in the range.

Related functions

More ways Excel gets this done.