How to Use VLOOKUP (Vertical Lookup) Function in Excel

The VLOOKUP function in Microsoft Excel searches for a value down the leftmost column of a range.  It then returns a value in the same row from a column number you specify in the range.  You can find an exact match or the nearest match.

Use VLOOKUP when your comparison values are in a column on the left of a table of data, and you want to look across to the right a specified number of cells.  Use it particularly if you want an exact match.

Alternatives:

Use HLOOKUP when your comparison values are in a row across the top of a table of data, and you want to look down a specified number of rows.  Use it particularly if you want an exact match.

Use LOOKUP (Vector form) instead if the row of comparison values is not above the row of data to return (HLOOKUP).  or if the column of comparison values is not left of the column of data to return (VLOOKUP).  or if the comparison values and the data to return are not in the same range, or even the same worksheet. Use it particularly if you can tolerate a nearest match, and have sorted data.

Use the INDEX function with one or two MATCH functions under the same circumstances as LOOKUP (Vector form), but you want an exact match.  For exact matches, the data need not be sorted.

LOOKUP and INDEX(MATCH) both avoid the problem where inserting a column in a VLOOKUP table, or a row in an HLOOKUP table, can corrupt the result.

VLOOKUP Syntax

VLOOKUP(lookup_value, table_array, col_index, [range_lookup])

Arguments:

Lookup_value (Required): The value to find in the leftmost column of the table. Lookup_value can be a value, a reference, or a text string.

Table_array (Required): A range in which to look up data. Use a reference to a range, or a range name.

The values in the leftmost column of table_array can be text, numbers, or logical values.

If range_lookup is TRUE, the values in the leftmost column of table_array must be sorted in ascending order: …-2, -1, 0, 1, 2,… , A-Z, FALSE, TRUE; otherwise, VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

Uppercase and lowercase text are equivalent.

Sort the values in ascending order, top to bottom. For more information, see Microsoft’s Sort data in a range or table.

Col_index (Required): The column number in table_array from which the matching value will be returned.  A col_index of 1 returns the first column value in table_array.  A col_index of 2 returns the second column value in table_array, and so on. If col_index is less than 1, VLOOKUP returns the #VALUE! Error.  If col_index is greater than the number of columns on table_array, VLOOKUP returns the #REF! error.

Range_lookup (Optional): A logical value that specifies whether you want an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will look for an exact match. If it cannot find one, it gives the error value #N/A.

Remarks:

If VLOOKUP can’t find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than lookup_value.

If lookup_value is smaller than the smallest value in the first row of table_array, VLOOKUP returns the #N/A error value.

If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character.  An asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Examples:

Here are some examples using the data above:

In cell A20, 1463 =VLOOKUP(“Gizmo”, A4:E13, 5, FALSE): We look up “Gizmo” in the left column, and return the value at col_index 5.

In cell A21, 1463 =VLOOKUP(“Gizmo”, A4:E13, 5.99, FALSE): If col_index is not an integer, it is truncated (5.99 becomes 5).

In cell A22, Gizmo =VLOOKUP(“Gizmo”, $A$4:$E$13, 1, FALSE): Returning the first col_index (equlas the lookup value, if found).

In cell A23, #N/A =VLOOKUP(“Zircon”, $A$4:$E$13, 1, FALSE): If an exact match (of “Zircon”) is not found, Excel returns #N/A.

In cell A24, Widget =VLOOKUP(“Zircon”, $A$4:$E$13, 1): Here we find an approximate match of  “Zircon” at “Widget” because we omitted range_lookup.

In cell A25, Thingamajig =VLOOKUP(“What is it”, $A$4:$E$13, 1, TRUE ): We find an approximate match of “What is it” at “Thingamajig”. “Approximate” because range_lookup = TRUE

In cell A26, #N/A =VLOOKUP(“What is it”, $A$4:$E$13, 1,FALSE ): An exact match of “What is it” is not found because range_lookup = FALSE.

In cell A27, #VALUE! =VLOOKUP(“Gizmo”, $A$4:$E$13, 0, FALSE): A col_index < 1. produces #VALUE!

In cell A28, #REF! =VLOOKUP(“Gizmo”, $A$4:$E$13, 20, FALSE): A col_index > number of rows in table_array produces #REF

In cell A29, #N/A =VLOOKUP(“Abacus”, $A$4:$E$13, 1, FALSE): A lookup_value less than the lowest value in the left column of table_array produces #N/A..

A Double Lookup (or, “what if you don’t always want year 2022?”)

You may have noticed that col_index is a fixed number in all the above examples.

What if that were variable?

I’m glad you asked!

VLOOKUP doesn’t do a second level of lookup.  No Excel function does.  What we can do is use another function, like MATCH:

In cell A32, we enter or reference the year we want, in this case, the value 2022.  This can change, or we could use a cell reference.

In cell A33, 5 =MATCH(A32, A3:E3, 0)

MATCH looks up an exact match in A3:E3. Note that we included the column heading (A3), which is OK for an exact match. If we hadn’t included column A, we could have used = 1 + MATCH(A32, B3:E3, 0)  to give the correct col_index.  This is because the VLOOKUP table_array includes the column of items.

In cell A34 we use A33, the result of MATCH, as the col_index: 1463 =VLOOKUP(“Gizmo”, $A$4:$E$13, A33, FALSE).

Of course, you could nest the formula from A33 inside A34 instead. Thus: =VLOOKUP(“Gizmo”, $A$4:$E$13, MATCH(A32, A3:E3, 0), FALSE) – but it might be difficult to debug if something goes wrong. Such as looking for a mistyped year 2202 instead!