VLOOKUP in excel stands for vertical lookup. This simple function is extremely useful if you have large lists of data. With this function, you can easily search through a list of data and match specific criteria. In the example below, we will use this function to match a batting average with a specific player.
To start off, lets take a look at the VLOOKUP syntax.
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
lookup _value:
This is the value you want to match. The lookup _value can be a text, a logical value (TRUE or FALSE only), a number, or a cell that references a value.
table_array:
This is the range of data range that our function uses to perform our search. The array used in VLOOKUP must contain at least two columns. The first column of data contains the lookup_values.
* It is best to use an absolute cell reference for the table_array. By using “$” in the range, this keeps a cell reference fixed on a certain cell, or cells.
col_index_num:
Enter the column number of the table_array from which you want data returned from. For example:
- if the col_index_num is 1, it returns a value from the first column in the table_array;
- if the col_index_num is 2, it returns a value from the second column in the table_array.
range_lookup:
True or False. The range_lookup must be a logical value.
- If TRUE or if this argument is omitted, VLOOKUP will use an approximate match if it cannot find an exact match to the lookup_value. If an exact match is not found, VLOOKUP uses the next largest lookup_value.
- If FALSE, VLOOKUP will only use an exact match to the lookup_value. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, an #N/A error is returned.
VLOOKUP Example
In the image below, we are telling Excel in Cell D1 to look at the range E2:F6 and find Ty Cobb. Once Ty Cobb is found, we want VLOOKUP to show Ty’s batting average listed in cell D1
Sources:
Microsoft.com About.com
Is it possible to combine VLookup with a copy/paste function. For instance, in your example, if there were additional columns of info after the batting average (like previous 5 years averages, one in each column), could you look up “Ty Cobb” and then pull the info from several colums to put into another area of the workbook? I want to be able to look up one value but then get all of the info to the right of it so I don’t have to re-type the same entries (the entries are dependent on the initial looked-up value). Thanks!
In case of “TRUE”, the values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value.
If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted.
[...] spreadsheet above:To view named ranges: Under the Insert menu, select Name Define.Related Posts Excel VLOOKUP COUNTIF Function – Cell Counting Tips [...]