Vlookup is most widely used formula in Excel. Vlookup stands for “Vertical Lookup”. It searches the first column of a table array and then selects value in right hand side columns of table array in the same row.
Syntax:
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value: Lookup value is the value that has to be searched in the first column in left hand side of the tabl array. If lookup_value is found in the first column of the table array, it will look for the data in right hand side columns in same rows. Lookup_value works as a primary key which can be data value or reference. These can be numeric or text values.
Table_array: Table array can be data columns containing two or more columns. First columns in the table array should be the value containing lookup_value. Like lookup_value, these values can be numeric or text values.
Col_index_num: Col_index_num is numeric value which tell formula that from which column data will be returned. It is defined in numbers like 1,2 3 and so on which are identified as column number to right hand side.
Range_lookup: Range lookup is a simple logical value: true or false to specifies whether you want find an exact match or an approximate match. That means if selected “FALSE”, formula will return value on exact match and will return error: #N/A in case of no value to return. In case of approximate match (TRUE), an exact or approximate match is returned and in case exact match is unavailable, next largest value that is less than lookup_value is returned. ’1′ and ’0′ can be also used in place of TRUE and FALSE respectively. Default value for range lookup is TRUE, if left blank.
Examples:
Here we have a table array of data in which we will use vlookup function.
| A | B | |
| 1 | Department Id | Department Name |
| 2 | 7 | Admin |
| 3 | 3 | Finance |
| 4 | 9 | Human Resource |
| 5 | 5 | Technology |
Lets check with few syntax of vlookup on above data.
Formula 1:
=VLOOKUP(5,A2:B5,2,FALSE)Result: The formula will returns Technology because it finds exact match.
Formula 2:
=VLOOKUP(4,A2:B5,2,FALSE)Result: The formula will returns error #N/A because there is no exact value available in column A matching to 4.
Formula 3:
=VLOOKUP(4,A2:B5,2,TRUE)Result: The formula will return Finance because it finds no exact match but since we have used range lookup as true, it returns next largest value that is less than lookup_value (3).
Extra Tips:
- Use of any leading / trailing spaces, inconsistent use of straight or any other nonprinting character in lookup value can return no value
- Make Absolute / Relative / Mixed cell reference in lookup value and table array wherever / whenever required
- If you want to find values in left hand side column, use INDEX and MATCH functions
- Vlookup returns result for both upper and lower case lookup value passed
Priya Bansal is a seasoned education writer with a passion for creating insightful and engaging content. With a background in education and a knack for research, she excels at producing articles, blogs, and resources that inform and inspire. Priya’s work contributes to the enrichment of educational discourse and empowers learners of all ages.
