# Lookup and Reference Functions in Excel: VLOOKUP, HLOOKUP, INDEX, MATCH, and XLOOKUP

Finding and retrieving information from large datasets is one of the most common
tasks performed in Microsoft Excel. Lookup and reference functions allow you to
search for specific values and return related information quickly and
accurately, making them essential for data analysis, reporting, and
decision-making.

Excel provides several lookup functions, including VLOOKUP, HLOOKUP, INDEX,
MATCH, and the newer XLOOKUP. While each function serves a similar purpose, they
differ in flexibility and use cases. Understanding these functions helps you
build efficient and scalable spreadsheets.

Learning Objectives

 * Understand lookup and reference functions

 * Use VLOOKUP to retrieve data vertically

 * Use HLOOKUP to retrieve data horizontally

 * Use INDEX and MATCH together for flexible lookups

 * Use XLOOKUP for modern Excel lookups

 * Identify the advantages and limitations of each function

 * Apply lookup functions to real-world datasets

What Are Lookup Functions?

Lookup functions search for a value in a table and return a corresponding value
from another row or column.

Common applications include:

 * Employee records

 * Product pricing

 * Sales reports

 * Student results

 * Inventory management

 * Financial reporting

VLOOKUP Function

The VLOOKUP function 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])

Example

Employee ID

Name

Department

101

John

Sales

102

Priya

HR

103

David

IT

Formula:

=VLOOKUP(102,A2:C4,2,FALSE)

Result:

Priya



HLOOKUP Function

The HLOOKUP function searches across the first row of a table and returns a
value from a specified row.

 

Syntax

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Example

Jan

Feb

Mar

Sales

1200

1500

1800

Formula:

=HLOOKUP("Feb",A1:D2,2,FALSE)

Result:

1500

INDEX Function

The INDEX function returns the value at a specific row and column within a
range.

Syntax

=INDEX(array,row_num,column_num)

Example:

=INDEX(B2:B4,2)

Result:

Priya

MATCH Function

The MATCH function returns the position of a value within a range.

Syntax

=MATCH(lookup_value,lookup_array,0)

Example:

=MATCH(102,A2:A4,0)

Result:

2

INDEX + MATCH

Combining INDEX and MATCH provides greater flexibility than VLOOKUP because the
lookup column does not need to be the first column.

Example:

=INDEX(B2:B4,MATCH(102,A2:A4,0))

Result:

Priya



XLOOKUP

XLOOKUP is the modern replacement for VLOOKUP and HLOOKUP in Microsoft 365.

Syntax

=XLOOKUP(lookup_value,lookup_array,return_array)

Example:

=XLOOKUP(102,A2:A4,B2:B4)

Result:

Priya

Advantages of XLOOKUP:

 * Searches in any direction

 * No column index required

 * Built-in error handling

 * Easier to read

 * More flexible than VLOOKUP

Comparing Lookup Functions

Function

Best Use

VLOOKUP

Vertical lookups

HLOOKUP

Horizontal lookups

INDEX

Return values by position

MATCH

Find positions

INDEX + MATCH

Flexible lookups

XLOOKUP

Modern all-purpose lookup

Best Practices

 * Use exact match (FALSE) for most business lookups.

 * Prefer INDEX + MATCH or XLOOKUP for new workbooks.

 * Use structured tables for better readability.

 * Keep lookup tables organized.

 * Verify lookup values before applying formulas.

Common Mistakes

 * Using approximate match unintentionally.

 * Incorrect column index numbers.

 * Missing absolute references when copying formulas.

 * Using VLOOKUP when the lookup column is not the first column.

 * Forgetting that XLOOKUP is only available in newer Excel versions.