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

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

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

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.