**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.

** **

**=VLOOKUP($A$3,$A$6:$C$45,2,FALSE)**

**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.

- Note: The first column of your

**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.

**#REF! error:**

- This error is displayed if the
*col_index_num*argument is greater than the number of columns in*table_array*.