Lookup & Reference Functions
-
Retrieve and reference data efficiently using Excel lookup functions.
VLOOKUP (Vertical Lookup)
Purpose:
Searches data vertically (in columns).
It looks for the value in the leftmost column of the table.Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])lookup_value → Value you want to search
table_array → Full data range
col_index_num → Column number from which to return result
range_lookup → FALSE (exact match), TRUE (approximate match)
Example:
=VLOOKUP(101, A2:C10, 2, FALSE)Output: Rahul
Limitations:
Works only left to right
Column number must be given manually
Not flexible if column order changes
HLOOKUP (Horizontal Lookup)
Purpose:
Searches data horizontally (in rows).Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])Example:
=HLOOKUP(101, A1:C2, 2, FALSE)Output: Rahul
Rarely used compared to VLOOKUP.
XLOOKUP (Modern & Powerful)
Available in the latest Excel versions.
It is an improved version of VLOOKUP & HLOOKUP.Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array)Example:
=XLOOKUP(101, A2:A10, B2:B10)Output: Rahul
Advantages:
Works both left and right
No need to specify column number
Built-in error handling
More flexible and powerful
Recommended for modern Excel users.
INDEX & MATCH (Advanced & Professional)
This is a powerful combination used in advanced Excel analysis.
MATCH Function
Finds the position of a value.
=MATCH(lookup_value, lookup_array, 0)INDEX Function
Returns a value based on position.
=INDEX(return_array, row_number)- Combined Example:
=INDEX(B2:B10, MATCH(101, A2:A10, 0))Output: Rahul
Comparison Summary