Microsoft Excel VLOOKUP Function Help
EMAGENIT Home page

Call us at:   1.805.498.7162     1.805.558.9277--

Learn about how we can help your company or yourself achieve your Microsoft Excel training or self-study goals Visit our free Microsoft Excel and Microsoft Excel VBA resource center Learn how we can help save your company money with our innovative software solutions Visit out VBA code center for code that can save your company money Learn about our Excel Add-In software that can save your company time and money View our customer list from our 12 years in business Phone numbers, request form and email address Learn about EMAGENIT View our training schedule for Microsoft Excel and Microsoft Excel VBA

 

VLOOKUP Function Examples

VLOOKUP Function Definition (Examples at Bottom)

Searches for a value in the first column of a range (also called a table array, table or data) and returns a value in the same row from another column in the range. The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

Vlookup Function Syntax

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

[] bracket means argument is optional which means you do not have to fill the slot.

VLOOKUP Argument Definitions

lookup_value: The value to search for in the first column of your data. Lookup_value can be a value, a cell reference or a formula. Be sure there are no repeated values in this column. If there are, VLOOKUP will lock onto the first one encountered and ignore the rest. These values searched for in the first column can be text, numbers, or logical values. If text is present, they are not case sensitive.

table_array (range or data): Two or more columns of data, in other words a range. You can identify this range by range reference (i.e. A1:B4) or use a range name like sales. You can include the headers of the data in the reference as long as they do not interfere with the lookup_value search.

col_index_num:  The column number in your range that contains the value you want to return. For example, you have two columns of data located in columns C and D. The lookup column is C, it contains the values to scan and column D contains the values to return. C is considered column 1 and D is considered column 2. If the VLOOKUP finds the value in column C at row 4 and you tell it to return column 2, then it would return the value of D4. Remember the column number system that you use is relative to the range of data, not worksheet column location. So if you data starts in column G that is column 1 to VLOOKUP. If you do not indicate the proper column sya, you put 4 instead of 2 and there are only 2 columns of data, an error will be returned.

range_lookup:  VLOOKUP can find an absolute match or approximate match in the first column of data it is looking in. Use TRUE for this argument if you want an approximate match or FALSE if you want an absolute match.

  • If you use TRUE or do not include it, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. The values in the first column of your data must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. You can put the values in ascending order by sorting the data.
  • If you use FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of data do not need to be sorted. If there are two or more values in the first column that match the lookup_value argument, the first value found is used. If an exact match is not found, the error value #N/A is returned.

VLOOKUP Function Tips

  • When searching text values in the first column of your data, ensure that the data in the first column does not have leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP may give an incorrect or unexpected value. For more information on functions that you can use to clean text data, see the Text functions.
  • When searching number or date values, ensure that the data in the first column of data is not stored as text values. In this case, VLOOKUP may give an incorrect or unexpected value. For more information, see Convert numbers stored as text to numbers.
  • If the range_lookup argument is FALSE and the lookup_value argument is text, then 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 (~) preceding the character.