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. Health 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)
Variables and Usage
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.
[caption id="attachment_34218" align="alignnone" width="670"]
Fig 1. VLOOKUP basic set up.
Choose another cell beside or below the table and copy and paste the following formula:
Excel will return the value of $123.45.
[caption id="attachment_34219" align="alignnone" width="670"]
Fig2. VLOOKUP Simple Query
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.
Excel will return the value of $119.25.
Excel will return the value of $120.01.
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 the 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.
This returns $125.68.
This returns $125.68.
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:
This returns "Higher" because the third column from "Tuesday 9" is greater than 100.
[caption id="attachment_34220" align="alignnone" width="660"]
Fig3. VLOOKUP Advanced Query
This returns "Higher" as well because the third column over from the date column contains the value $145.50, which is greater than the value in cell B8: $144.65
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:
To make a logical comparison with a string make sure to use quotation marks:
For example, returns "Match".
And this comparison returns #N/A because there is no entry that matches "Tuesday 91".
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:
This returns $144.00.
This returns $145.00.
Regularly Encountered Error Messages When Using VLOOKUP in Excel
Wildcards Search With VLOOKUP in Excel
As is common with some other Microsoft products, VLOOKUP allows users to search using wildcards. The three wildcards are:
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.
Jane is news writer presenting the latest trending information as it's released. She's spends most of her time sourcing premium news from our top sources bringing fresh updates to her loyal subscribers. She loves ice-cream and her dog Sally!