VLOOKUP FORMULA
INTRODUCTION
VLookup and HLookup are most prominent functions in Microsoft Excel and in VLookup, V stands for 'Vertical' and this function is used to find a particular value by lookup key with the help of column index number.
VLOOKUP SYNTAX
VLOOKUP(lookup_value, table_array, col_index_num)
Explanation: In the above syntax 'Vlookup' is function's name and lookup_value is a key value that is used to find the desired value, table_array is a complete address of range, in which we want to perform the lookup operation.
col_index_number: This is a column index number, in which we actually find the value.
Let's start with an Vlookup example...
Vlookup Example Explanation: In the above example, Vlookup function applying on above data item, selected by a green outline.
In this example, we are finding the department with help of 'Emp_Code' and to do this task we have to implement a formula =VLOOKUP(E14, A1: D11,3)
VLOOKUP FUNCTION ARGUMENTS
E14 is lookup value cell address (103).
A1: D11 is a complete address of table array (Emp. Code,....., Basic Salary)
3 is column index number (Department column)
VLOOKUP FUNCTION WITH DEFINED NAME
When we apply Vlookup formula across the worksheet, then the address of table array can be become more complicated, just like the below example...
$A$1:$D$11
So its better to create (define) a name for the address of table array.
Let's have an example...
Step 1: Select the data item, which you want to add a name.
Step 2: Click on the 'Formulas' menu.
Step 3: Click on 'Define name' tool.
Step 4: Insert any name and click on 'Ok' button.
Step 5: Now open the sheet in which you want to apply the Vlookup.
Step 6: Insert the Vlookup formula with the defined name.
FUNCTION GUI MODE
Functions can be applied either manually written or its GUI mode.
GUI (Graphical User Interface): Its an easy interface that provides more easy to use, to insert a function. Actually, this mode consisted of text, textbox, and proper guidelines, which h to the user about any function.
CTRL + A SHORTCUT KEY
By default the 'Ctrl + A' key used to select the entire worksheet contents, but in function, it is used to open GUI mode of any selected function.
Let's have an example...
Now we are going to perform above example with help of Vlookup GUI mode instead manually writing.
Step 1: First add the required data item.
Step 2: Define name, if required.
Step 3: Add or write the 'Vlookup' function.
Step 4: Press the 'Ctrl + A' shortcut key.
Step 5: Now fill the required details in the following text box.
Lookup_value: In this text box, fill the address of lookup value.
Table_array: Table array is an address in which you want to perform the actual search operation and in this text box, fill the address of table array or fill an already defined name of table array.
Col_index_num: In this text box, fill the number of column index.
Range_lookup: This is an optional argument and it contains the following two options.
True: It's for the approximate match and it applies, when we leave blank the range lookup.
False: It's for exact match and in this example, it should be avoided.
0 Comments