Joshua is a graduate student at the USF. He has interests in business technology, analytics, finance, and lean six sigma.
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 is a specific syntax that needs to be followed.
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
If the syntax is not followed an error may occur in the field where the formula was written. Each variable needed 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 last input in the formula will need to be False or 0. This will ensure that the value being looked up will have to 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, whatever 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, whatever value that is entered into cell A2 will become the lookup value. The section of the table used for the lookup is located in columns 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 insertion 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.
Inserting VLOOKUP Function From Formulas Tab
The functions argument window will allow you to add variables to the function. The arrows to the right of each field can be clicked 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 example, I would like to enter a record ID number in a field and have two pieces of data returned that is 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 what was used to determine the value of 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 of the record was returned for that column.
The cause of death data was similarly returned by using column 7 instead of column 5. 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.
VLOOKUP Function Components
Microsoft. (n.d.). VLOOKUP function. Retrieved January 5, 2020, from https://support.office.com/en-us/article/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1.
- How to Use the PERCENTILE Function in Excel
Here I cover in detail how to use the PERCENTILE function in Excel with the correct syntax.
- How to Use the TREND Function in Excel
Covers how to use the TREND function in Microsoft Excel with the correct syntax and an example.
- How to Use the RAND Function in Excel
Covers how to use the RAND function in Microsoft Excel with examples on the correct syntax to use.
- How to Use the TYPE Function in Excel
Covers how to use the TYPE function in Microsoft Excel with examples on the correct syntax to use.
- How to Use the ABS Function in Excel
Covers how to use the ABS function in Microsoft Excel with examples on the correct syntax to use.
- How to Use the AVERAGE Function in Excel
Shows how to use the AVERAGE function in Excel with application examples.
- How to Use the COUNT Function in Excel
Shows a Microsoft Excel user how to use the count function in a spreadsheet.
Learning More About Excel Functions
To learn more about using functions in Excel I recommend purchasing Excel Formulas & Functions For Dummies.
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