Lookup Functions
The Lookup functions are listed under FORMULAS
INSERT FUNCTION and under FORMULASLOOKUP & REFERENCE option.
Lookup Functions in Excel 2013
VLOOKUP
VLOOKUP (Vertical Lookup): Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default the table must be sorted in an ascending order.
Lookup_value: It is the value to be found in the first column of the table, and can be a value, a reference, or a text string.
Table_Array: It is a table of text, numbers, or logical values, in which data is retrieved. Table_array can be a reference to a range or a range name.
Col_Index_num: It is the column number in table_array from which the matching value should be returned. The first column of value in the table is column 1.
Range_Lookup: It is a logical value, to find the closest match in the first column (stored in ascending order) =TRUE or omitted; find an exact match = FALSE.
The above definitions have been sourced from www.Microsoft.com.
For our example below, we are taking two tables; Department and Employee.
VLOOKUP Example
Department Table has two columns; Dept No and Dept Name; Col_index_num value of DeptNo is 1. Col_index_num value of Dept Name is 2.
Employee table has 3 columns; First Name, Dept No, Dept Name and Dept Name does't have any values.
Our intention to use VLOOKUP is to take each value in Employee.Deptno and find whether that value is present in Department.DeptNo. if tDepartment.DeptNo value is present, then the corresponding Department.DeptName has to be populated in Employee.DeptName. Now we are examining this data with few records. If there are thousands of records, then it will be difficult for anyone to manually check and populate the data in Employee.DeptName. This can be done easily with VLookup function.
- Sort the Department table by DeptNo.
- Place the cursor in Employee.Deptname (first record). This is where the data will be populated.
- Click VLookup function from Lookup & Reference group in Formula Tab. A new screen "FUNCTION ARGUMENT" pops up and by default cursor goes to LOOKUP_VALUE.
- Place the cursor in Employee.DeptNo (first record). The corresponding cell value E3 is displayed.
- Click the cursor in TABLE_ARRAY. Go to Department table and select those two records by dragging. Those two records are the Table_Array. (A3:B4 and the cell value are displayed in Table_Array. Press F4; Now the value changes to $A$3:$B$4
- Click the cursor on COL_INDEX_NUM. Type 2. The reason for typing two is we need the value of the second column Dept.DeptName. Click OK.
- Since the value (20)of the first record in Employee.Deptname is found in Dept.DeptName, corresponding value of FINANCE is populated in Employee. DeptName.
- Drag the small arrow found in right bottom corner of the cell (Finance) until the end of all records.
- The reason for dragging is that formula will be copied to all cells and corresponding data will be populated.
- a. For Melinda, you will not find DeptName since she doesn't have any department number; b. For Shane, you will not find DeptName, since there is a typo in the department no. Instead of 20, it has been typed as 2 0; c. For Cris, you will not find DeptName, since his deptno 30 is not present in Dept.Deptno.
- FORMULA: =VLOOKUP(E3,$A$3:$B$4,2,FALSE). Refer the diagram below:
DATE and TIME Functions