Excel VLOOKUP Function
The VLOOKUP function is a premade function in Excel, which allows searches across columns.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Note: The column which holds the data used to lookup must always be to the left.
Note: The different parts of the function are separated by a symbol, like comma , or
semicolon ;
Lookup_value: Select the cell where search values will be entered.
Table_array: The table range, including all cells in the table.
Col_index_num: The data which is being looked up. The input is the number of the column, counted from the left:
Range_lookup: TRUE if numbers (1) or FALSE if text (0).
Note: Both 1 / 0 and True / False can be used in Range_lookup.
How to use VLOOKUP function
- Select a cell (
H5) - Type
=VLOOKUP - Double click the VLOOKUP command
- Select the cell where search value will be entered (
H4) - Type (
,) - Mark table range (
A1:E11) - Type (
,) - Type the number of the column, counted from the left (
2) - Type True (1) or False (0) (
1) - Hit enter
- Enter a value in the cell selected for the Lookup_value
H4(3)
let's take a example
Use the VLOOKUP function to find the names based on their S No.
H5 is where the search result is displayed. In this case, the names based on their S No.
H4selected as lokup_value. This is the cell where the search query is entered
The range of the table is marked at table_array, A2:E11
The number 2 is entered as col_index_number. This is the second column from the left and is
the data that is being looked up.
1 (True) is entered as range_lookup. This is because the most left column has numbers only.
If it was text, 0 (False) would have been used.
The function returns the #N/A value. This is because there have not been entered any value to the
Search S No. H4.
Let enter a value to it, type H4(4):
