VLookup – Your “search box” tool
VLookup which stands for vertical lookup, can assist you in finding specific information in large data tables or lists in Excel such as a products inventory list, an employee information list, or a listing of parts. In essence, you are creating a “search box” in your worksheet that allows for you to type in a “search value” and have Excel then display relevant information associated with your search. VLookup searches for the lookup value in the first column of the lookup table and returns a value in the same row from the specified column of the table.
Let’s break it down so that it’s easier to understand.
Here is the syntax or structure of the VLookup formula:
=VLookup(lookup_value, table_array, col_index_num, range_lookup)
Here’s what each step means:
- lookup_value refers to the cell or “search box” containing the value that the function has to find
- table_array is the range of cells in which you are trying to find the lookup_value
- col_index_num is the number of the column from which a value will be returned.
- range_lookup is an optional argument that specifies whether you want to find an exact or approximate match. Typing in “False” gives you an exact match while “True” gives you an approximate match. If you omit this, VLookup assumes that it is “True” – an approximate match.
Now an example to help put together all the information above:
In reference to the screenshot above, here is an example of a VLookup function. The “search box”, A3, contains our value to find in the table sitting just under it. The VLookup formula is typed in B3. Our goal is to find the name of the employee with the ID of DF100 where Bob Smith is the answer. An additional VLookup was used in C3 to find DF100’s Department, which is Marketing.
Let’s break down each step of the formula above:
- The first part of the formula, $A$3, is the lookup_value. The lookup_value is asking which cell contains the value that Excel should find within the table. Look at it as your “search box” in which you enter, in this case, employee IDs. Dollar signs ($) are used in the formula so that the formula can be copied to other cells and keep the formula intact and functioning correctly.
- The second part of the formula, $A$6:$C$45, is the table_array. The table_array is the range of cells that the VLOOKUP searches to find your information. Dollar signs are again used to specify that this is the table range and should not be changed upon copying the formula to another cell.
- The third part of this formula, 2, is the col_index_num. The number 2 indicates the second column in the table_array (the Name column) with which you would like to extract information.
- The final portion of the formula, FALSE, is the range_lookup. We want to find the exact match for ID DF100 in the table, not a person who’s ID is close to DF100.
Tips for making VLookup work for you:
- Avoid leaving blank rows or columns when entering your data in your table_array. Leaving blank rows and columns in data tables can make it difficult to use a number of Excel’s functions – including VLOOKUP.
- Enter your data in columns when possible. When laying out your worksheet, place a title describing the data at the top of the first column of the table with the data below.
- The lookup_value is always located in the first column of the table_array.
- Note: The first column of your table_array does not have to start in column A of your worksheet.
- The table_array must contain at least two columns of data. The first column contains the lookup values. These values can be text, numbers, or logical values.
- It is a good idea to use absolute referencing ($) in your formula when you copy and paste the VLookup formula to other cells. Otherwise, there is a good chance you will receive error messages or incorrect information in the cells to which you pasted.
- When using True for the range_lookup, make sure your table is sorted in ascending order by the first column (your lookup_value column). The True value tells VLookup to look for an approximate match instead of an exact match.
Excel VLOOKUP Error Messages and what they mean:
The following error messages are associated with VLOOKUP:
#N/A error :
- This error is shown when the lookup_value is not found in the first column of the table_array.
- This error will also show if the range for the table_array argument is erroneous.
- This error is displayed if the col_index_num argument is greater than the number of columns in table_array.