VLOOKUP In Microsoft Excel
Among the rich library of search and reference functions offered in Microsoft Excel is the VLOOKUP function. VLOOKUP is useful when searching a database stored in a worksheet by the value in the left-most column of a user-defined range.
Excel developers create Excel applications using the VLOOKUP function to search a number of different types of databases. Human resources workers may design spreadsheets with functions used to search databases and pull details of employee’s files. Stock analysts may use VLOOKUP to comb through databases looking for unusual data. Heath care professionals might use VLOOKUP to scan databases containing data related to the trial of a new pharmaceutical.
VLOOKUP ranges are defined by two locations, marking the upper-left and lower-right corners of the matrix. The VLOOKUP function only searches the left-most column for values that match, which may require that users rearrange data with creative cutting or copying and pasting to suit their particular application – one of Excel’s particular strengths.
The syntax when using VLOOKUP is as follows:
VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
- VLOOKUP (2015, B3:E7, 3, TRUE)
- VLOOKUP (“THOMAS”, A5:K32, 2, FALSE)
Variables and Usage
- lookup_value: This is the value that the VLOOKUP function will search for in the left-most column of the array. With the first sample above, VLOOKUP (2015, B3:E7, 3, TRUE), the function will be searching for values in column B.
- Strings are entered with quotations marks: “THOMAS”
- Numbers are entered without quotation marks: 2015
- table_array: Using two values, the user defines a matrix by specifying the upper left and lower right corner cells: B3:E7.
- col_index_num: This specifies the column of the ARRAY to find the data to return. Not to be confused with the column of the spreadsheet, which is static. The col_index_num of the array will change with different arrays in the same spreadsheet.
- range_lookup: May be set to TRUE or FALSE. If set to TRUE the VLOOKUP searches for the closest match. When set to FALSE it searches for exact matches. This variable also assumes that data is sorted numerically or alphabetically when using the TRUE setting. If the data is not ordered when using TRUE unexpected results will be encountered.
Using VLOOKUP To Search Historical Stock Prices
|Date||Open Price||Close price|
The table above represents hypothetical stock prices over a period of seven days. Readers looking to practice this functionality can copy and paste the table into Excel in the upper-right-most corner of a worksheet.
Choose another cell beside or below the table and copy and paste the following formula:
- =VLOOKUP(“Tuesday 2”, A2:C8, 2, FALSE)
Excel will return the value $123.45.
Users may practice using the functions by altering the settings and observing the effects For instance, replacing the above formula with the following formulas. The result that Excel should return is included.
- =VLOOKUP(“Wednesday 3”, A2:C8, 2, FALSE)
Excel will return the value $119.25.
- =VLOOKUP(“Wednesday 3”, A2:C8, 3, FALSE)
Excel will return the value $120.01.
- =VLOOKUP(“Tuesday 9”, A2:C8, 1, FALSE)
Though it might seem redundant, Microsoft includes the functionality to return the same value or string that VLOOKUP searches for. This is done by specifying column 1 with col_index_num variable.
The last variable range_lookup is different than an analogous Google search and at first may seem obtuse. When the range_lookup variable is set to FALSE, VLOOKUP searches for an exact string or numerical value match. When it is set to TRUE however, it will hit on strings that are shorter only.
With more modern search functions, when a range_lookup is specified engines seek instances where the search term is a smaller or equal part of the target term. With Excel, when set to TRUE it searches for instances that are only partially contained within the search term. The following examples illustrate.
- =VLOOKUP(“Monday 1”, A2:C8, 2, TRUE)
This returns $125.68.
- =VLOOKUP(“Monday 12”, A2:C8, 2, TRUE)
This returns $125.68.
- =VLOOKUP(“Monday”, A2:C8, 2, TRUE)
This returns #N/A. Un-similarly, an analogous Google search using this logic would hit on “Monday 1” and return $125.68, where VLOOKUP finds nothing and returns #N/A.
Using VLOOKUP With Excel IF Function
The Excel IF function has the usage:
IF(Data Comparison, If True Display, If False Display)
The VLOOKUP function may be nested inside the IF functions in the Data Comparison variable. Some examples, again using the above table:
- =IF(VLOOKUP(“Tuesday 9″,A1:C8,3,FALSE)>100,”Higher”,”Lower”)
This returns “Higher” because the third column from “Tuesday 9” is greater than 100.
- =IF(VLOOKUP(“Tuesday 9″,A1:C8,3,FALSE)>B8,”Higher”,”Lower”)
This returns “Higher” as well, because the third column over from the date column contains the value $145.50, which is greater that the value in cell B8: $144.65
- =IF(VLOOKUP(“Tuesday 9”,A1:C8,3,FALSE)>VLOOKUP(“Tuesday 9″,A1:C8,2,FALSE),”Higher”,”Lower”)
This returns “Higher” as well, because the value in the third column is greater than the value in the second column. The second and third entries compare the same table entry, but the second object in the logical comparison is accessed in a different way, using VLOOKUP.
Finally, to show that we can get “Lower” to appear as well:
- =IF(VLOOKUP(“Tuesday 9″,A1:C8,3,FALSE)<100,”Higher”,”Lower”)
To make a logical comparison with a string make sure to use quotation marks:
- =IF(VLOOKUP(“Tuesday 9″,A1:C8,1,FALSE)=”Tuesday 9″,”Match”,”No Match”)
For example, returns “Match”.
- =IF(VLOOKUP(“Tuesday 91″,A1:C8,1,FALSE)=”Tuesday 91″,”Match”,”No Match”)
And this comparison returns #N/A because there is no entry that matches “Tuesday 91”.
The Excel INT Function and VLOOKUP
Some stock traders like to focus on round numbers, and find fractions involved with stocks distracting. One way a trader with this preference could use Excel to display data to their liking is by combining the VLOOKUP function with the INT function.
The INT Function takes a decimal number and truncates the numbers following the decimal place. Its usage is as follows:
Practice using the INT and VLOOKUP functions together with the data from the above table:
- =INT(VLOOKUP(“Tuesday 9”,A2:C8,2, FALSE))
This returns $144.00.
- =INT(VLOOKUP(“Tuesday 9”,A2:C8,3, FALSE))
This returns $145.00.
Regularly Encountered Error Messages When Using VLOOKUP in Excel
- #NAME? returned: Most often, this indicates that VLOOK up is expecting a string but quotations marks have not been used. Add quotations marks as per the examples above.
- Incorrect number or string: This may be caused by attempting to search unsorted data with the range_lookup variable assigned to TRUE. Solutions include changing the value to false and using different search parameters or sorting the data.
- #VALUE! returned: this is uncommon but may result if the table_array variable is set below 1.
- #N/A returned: the value searched for with VLOOKUP must be greater than the smallest record in the search data, or #N/A will be returned.
- #REF! returned: If the variable col_index_num is outside the defined matrix the #REF! error will be returned.
Wildcards Searches With VLOOKUP in Excel
As is common with some other Microsoft products, VLOOKUP allows users to search using wildcards. The three wildcards are:
- $: The dollar sign replaces any one letter, number or other character.
- *: The asterisk replaces whole groups of numbers down to just one.
- In the case where searching for actual dollar signs or asterisks is required, precede the wildcard with a tilde “~$” or “~*”.
Check For Errors In Both Data And VLOOKUP Variables
When developing formulas using VLOOKUP and all Excel functions, errors and unexpected results will be encountered from time to time. Making sure cells are properly formatted for numbers, strings or currency, as well as being properly sorted when using the range_lookup variable to TRUE. Make sure strings have quotations and that all opening and closing brackets are used correctly, as per the examples above.