What are Lookup Functions? #
Lookup functions are used to search for a value in a dataset and return related information.
Example:
Find a student’s marks using their ID
VLOOKUP (Vertical Lookup) #
Definition #
Searches for a value in the first column of a table and returns a value from another column.
Syntax #
=VLOOKUP(lookup_value, table_array, col_index_number, [range_lookup])
Parameters Explained #
| Parameter | Meaning |
|---|---|
| lookup_value | Value to search |
| table_array | Data range |
| col_index_number | Column number to return |
| range_lookup | FALSE = exact match |
Example #
Dataset: #
| ID | Name | Marks |
|---|---|---|
| 1 | Alex | 80 |
| 2 | John | 70 |
| 3 | Emma | 90 |
Formula: #
=VLOOKUP(2, A2:C4, 3, FALSE)
Output: 70
Important Points #
- Works left → right only
- Lookup column must be first
- Use
FALSEfor exact match
HLOOKUP (Horizontal Lookup) #
Definition #
Searches for a value in the first row and returns a value from a specified row.
Syntax #
=HLOOKUP(lookup_value, table_array, row_index_number, [range_lookup])
Example #
Dataset: #
| A | B | C | |
|---|---|---|---|
| ID | 1 | 2 | 3 |
| Name | Alex | John | Emma |
| Marks | 80 | 70 | 90 |
Formula: #
=HLOOKUP(2, A1:C3, 3, FALSE)
Output: 70
Important Points #
- Works top → bottom
- Less commonly used than VLOOKUP
XLOOKUP (Modern Function) #
Definition #
XLOOKUP is a new and advanced version of VLOOKUP/HLOOKUP.
Works in any direction (left, right, up, down)
Syntax #
=XLOOKUP(lookup_value, lookup_array, return_array)
Example #
Dataset: #
| ID | Name | Marks |
|---|---|---|
| 1 | Alex | 80 |
| 2 | John | 70 |
| 3 | Emma | 90 |
Formula: #
=XLOOKUP(2, A2:A4, C2:C4)
Output: 70
Advantages of XLOOKUP #
- No column index needed
- Can search left or right
- Built-in error handling
- More flexible
Example with Error Handling #
=XLOOKUP(5, A2:A4, C2:C4, "Not Found")
If value not found → shows “Not Found”
VLOOKUP vs HLOOKUP vs XLOOKUP #
| Feature | VLOOKUP | HLOOKUP | XLOOKUP |
|---|---|---|---|
| Direction | Vertical | Horizontal | Any direction |
| Flexibility | Limited | Limited | High |
| Ease of Use | Medium | Medium | Easy |
| Modern Usage | Old | Old | New (Recommended) |
Practical Example #
Scenario: Product Price Lookup #
| Product | Price |
|---|---|
| A | 100 |
| B | 200 |
Using XLOOKUP:
=XLOOKUP("B", A2:A3, B2:B3)Output: 200
- Lookup Functions → Find and return data
- VLOOKUP → Vertical search (limited)
- HLOOKUP → Horizontal search
- XLOOKUP → Advanced, flexible, recommended