Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.
The VLOOKUP Function
The Purpose of the VLOOKUP Function
The VLOOKUP function checks the column to the far left of a table for a value to return a value in the same row of a different specified column. This concept is further broken down in the following sections.
The VLOOKUP Function Syntax
The VLOOKUP function needs to be inputted like a formula for it to be able to work. To add a VLOOKUP formula to a cell, a cell needs to be clicked and "=VLOOKUP(" needs to be typed in. After the open parenthesis, this a specific syntax that need to be followed.
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
If the syntax is not followed and error may occur in the field where the formula was written. Each variable need for the VLOOKUP function is described more below.
The Vlookup Value
This indicates what record to abstract information from. The VLOOKUP function will always search the first column of a table for this value. The record that is chosen will contain data to be returned to the cell that the formula is typed in.
The Table Array
This lets the function know where all the data is. The whole table being used should be selected so that each record can be indexed. The VLOOKUP value will always be in the first column of the table so you may want to start this selection in column two if that is where the lookup value can be found.
The Column Index Number
The represents that column that that data to be returned is in. So, the lookup value determines the row and the column index determines the column. What is found when these meet will determine what data will appear in the cell.
The Range LOOKUP
This is the sensitivity measure and specifies whether the match needs to be approximate or exact. This range is optional and can be left out. Approximate and exact match settings are discussed in more detail below.
- Approximate match - To return an approximate match the last input in the formula would need to be TRUE or 1. When this option is chosen the VLOOKUP function will assume that the first column (where the lookup value appears) is sorted alphabetically or numerically and will search for the closest approximate answer.
- Exact match - To return the exact match the the last input in formula will need to be False or 0. This will ensure that the value being looked up will have make an exact match.
VLOOKUP Syntax Examples
In this example amber will be looked for in the first column of a table. The section of the table used for the lookup is A1 to B100. The data that needs to be returned is in column two of the table. An exact match must be made.
In this example what ever value that is entered into cell A1 will become the lookup value. The section of the table used for the lookup is A10 to C20. The data that needs to be returned is in column two of the table. An approximate match must be made.
In this example what ever value that is entered into cell A2 will become the lookup value. The section of the table used for the lookup is located in column A to F in the clients worksheet. The data that needs to be returned is in column three of the table.
Inserting a VLOOKUP Function
The VLOOKUP function can be inserted into a worksheet cell with the use of an insert tool. To insert the function one must first select a cell for the formula by clicking in it. Next, the formulas tab needs to be clicked, as well as the "Lookups & References" button on the Excel ribbon. The VLOOKUP option should be selected from the list.
The functions argument window will allow you to add variables to the function. The arrows to the right of each field can be click to find cell references or ranges.
There are dozens of ways that the VLOOKUP function can be used. Explore the following example if you want to know how this function is typically used in a form.
Example - Using VLOOKUP in a Form
The data in the illustration below shows accidental drug related deaths from 2012 to 2018. For the example, I would like to enter a record ID number in a field and have two pieces of data returned that are an attribute from that record.
The mock form that I will create will be in another worksheet in the same workbook. The ID Number will need to be an input to return data from the record for sex and cause of death.
The illustration below shows in detail of what was used to determine the value for sex. The lookup value is the value that that was entered in E10. That table used for the lookup was located in the range A1 to W891 in the worksheet titled "Accidental_Drug_Related_Deaths_." Next by entering 5, the sex was of the record was return for that column.
The cause of death data was similarly returned by using column 7 instead of column 5. Basically, if you want to return multiple data in different fields you can copy the same formula and change the column number depending on what piece of data that you want to appear.
To learn more about other types of lookup and reference function in Excel I recommend the following book. I have been using the Excel Bible for years to improve my understanding of all aspects of this Microsoft product.
The Excel 2019 Bible
Microsoft. (n.d.). VLOOKUP function. Retrieved January 5, 2020, from https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1.
This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.
© 2020 Joshua Crowder