Lookup Functions in Excel
Lookup Functions are used to search for specific data in a table and return related information from another column or row. These functions are widely used in Employee Databases, Student Records, Product Lists, Inventory Systems, and Sales Reports.
Important: VLOOKUP searches vertically (column-wise), while HLOOKUP searches horizontally (row-wise).
VLOOKUP Function
VLOOKUP (Vertical Lookup) searches for a value in the first column of a table and returns a value from another column in the same row.
Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Arguments Explained
| Argument | Description |
|---|---|
| lookup_value | The value to search for. |
| table_array | The complete table containing data. |
| col_index_num | Column number from which data should be returned. |
| range_lookup | FALSE = Exact Match, TRUE = Approximate Match. |
Sample Student Table
| Student ID | Name | Course | Fees |
|---|---|---|---|
| 101 | Rahul | Excel | 5000 |
| 102 | Priya | Python | 7000 |
| 103 | Amit | Tally | 6000 |
Example 1: Find Student Name
=VLOOKUP(102,A2:D4,2,FALSE)
| Formula Part | Meaning |
|---|---|
| 102 | Student ID to search |
| A2:D4 | Student table range |
| 2 | Return value from 2nd column |
| FALSE | Exact match required |
Result: Priya
Example 2: Find Course
=VLOOKUP(103,A2:D4,3,FALSE)
Result: Tally
Example 3: Find Fees
=VLOOKUP(101,A2:D4,4,FALSE)
Result: 5000
Important Rules for VLOOKUP
| Rule | Explanation |
|---|---|
| Lookup value must be in first column | VLOOKUP searches only in the first column of the selected table. |
| Use FALSE for exact match | Most commonly used in real-world reports. |
| Column number starts from 1 | First column = 1, Second column = 2, etc. |
HLOOKUP Function
HLOOKUP (Horizontal Lookup) searches for a value in the first row of a table and returns data from a specified row.
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Arguments Explained
| Argument | Description |
|---|---|
| lookup_value | Value to search in first row. |
| table_array | Table range containing data. |
| row_index_num | Row number from which result should be returned. |
| range_lookup | FALSE = Exact Match. |
Sample Table
| Product | Laptop | Printer | Scanner |
|---|---|---|---|
| Price | 45000 | 12000 | 8000 |
Example
=HLOOKUP("Printer",A1:D2,2,FALSE)
Result: 12000
Difference Between VLOOKUP and HLOOKUP
| Feature | VLOOKUP | HLOOKUP |
|---|---|---|
| Search Direction | Vertical | Horizontal |
| Search Area | First Column | First Row |
| Returns Data From | Specified Column | Specified Row |
| Most Commonly Used | Yes | Rarely |
