How To Do A VLOOKUP In Excel

This Excel Tutorial demonstrates how to use the Excel VLOOKUP Function in Excel to look up a value, with formula examples.

VLOOKUP Function Description:

The VLOOKUP Function Vlookup stands for vertical lookup. It searches for a value in the leftmost column of a table. Then returns a value a specified number of columns to the right from the found value. It is the same as a hlookup, except it looks up values vertically instead of horizontally.

Formula Examples:

vlookup function examples

ExampleFormulaResult
Exact Match – Number=VLOOKUP(F5,$B$5:$C$10,G5,H5)162102
Exact Match – Text=VLOOKUP(F6,$B$5:$C$10,G6,H6)41681
Closest Match=VLOOKUP(F7,$B$5:$C$10,G7,H7)133303

Syntax and Arguments:

The Syntax for the VLOOKUP Formula is:

Function Arguments ( Inputs ):

lookup_value – The value you want to search for.

table_array – The data range that contains both the value you want to search for and the value you want the vlookup to return. The column containing the search values must be the left-most column.

col_index_num – The column number of the data range, from which you want to return a value from.

range_lookup – TRUE or FALSE. FALSE searches for an exact match. TRUE searches for the nearest match that is less than or equal to the lookup_value. If TRUE is chosen the left-most column (the lookup column) must be sorted ascendingly (lowest to highest).

Additional Notes

Use the VLOOKUP Function to perform a VERTICAL lookup. The VLOOKUP searches for an exact match (range_lookup = FALSE) or the closest match that is equal to or less than the lookup_value (range_lookup = TRUE, numeric values only) in the first row of the table_array. It then returns a corresponding value, n number of rows below the match.

When using an VLOOKUP to find an exact match, first you define an identifying value that you want to search for as the lookup_value. This identifying value might be a SSN, employee ID, name, or some other unique identifier.

Next you define the range (called the table_array) that contains the identifiers in the top row and whatever values that you ultimately wish to search for in the rows below it. IMPORTANT: The unique identifiers must be in the top row. If they are not, you must either move the row to the top, or use MATCH / INDEX instead of the VLOOKUP.

Third, define row number (row_index) of the table_array that you wish to return. Keep in mind that the first row, containing the unique identifiers is row 1. The second row is row 2, etc.

Last, you must indicate whether to search for an exact match (FALSE) or nearest match (TRUE) in the range_lookup. If the exact match option is selected, and an exact match is not found, an error is returned (#N/A). To have the formula return blank or “not found”, or any other value instead of the error value (#N/A) use the IFERROR Function with the VLOOKUP.

To use the VLOOKUP Function to return an approximate match set: range_lookup = TRUE. This option is only available for numeric values. The values must be sorted in ascending order.

To perform a horizontal lookup use the HLOOKUP Function instead.

VLOOKUP Examples in VBA

You can also use the VLOOKUP function in VBA. Type:
application.worksheetfunction.vlookup(lookup_value,table_array,col_index_num,range_lookup)
For the function arguments (lookup_value, etc.), you can either enter them directly into the function, or define variables to use instead.

Return to the List of all Functions in Excel

How to use the VLOOKUP Function in Excel:

To use the AND Excel Worksheet Function, type the following into a cell:
=AND(
After entering it in the cell, notice how the AND formula inputs appear below the cell:
vlookup formula syntax
You will need to enter these inputs into the function. The function inputs are covered in more detail in the next section. However, if you ever need more help with the function, after typing “=VLOOKUP(” into a cell, without leaving the cell, use the shortcut CTRL + A (A for Arguments) to open the “Insert Function Dialog Box” for detailed instructions:
how to use the vlookup function in excel

For more information about the VLOOKUP Formula visit the
Microsoft Website.

ADS GO HERE

Posted in