🚀 Excel Formulas Fundamentals — Part 9

🔍 Lookup & Reference Functions (VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP)

Lookup functions are among the most frequently asked topics in Excel interviews. They help you search for data in one table and return related information from another.

📌 Common use cases:

Employee databases, Product catalogs, Customer records, Sales reports, Inventory management

🧠 1. What is a Lookup Function?

A lookup function searches for a value and returns a related value from another row or column.

Example:

You have Employee ID, Name, Department data.

Rows: E101 Rahul Sales, E102 Priya HR, E103 Amit IT

Search for E102 and return HR.

📖 2. VLOOKUP()

VLOOKUP stands for Vertical Lookup.

It searches 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,FALSE)

Example:

Data: E101 Rahul 50000, E102 Priya 60000, E103 Amit 70000

Formula: =VLOOKUP("E102",A2:C4,3,FALSE)

Result: 60000

Arguments explained:

lookup_value: Value to search

table_array: Data table

col_index_num: Column number to return

FALSE: Exact match

⚠️ 3. Limitations of VLOOKUP

❌ Can only search left to right

❌ Column numbers must be counted manually

❌ Breaks if columns are inserted or deleted

❌ Slower on large datasets

📌 These limitations are why many professionals now prefer INDEX + MATCH or XLOOKUP.

📘 4. HLOOKUP()

HLOOKUP stands for Horizontal Lookup.

It searches across the first row and returns a value from a specified row.

Syntax:

=HLOOKUP(lookup_value,table_array,row_index_num,FALSE)

Example:

Data across columns: Jan 10000, Feb 12000, Mar 15000

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

Result: 12000

📌 Less commonly used because most business data is arranged vertically.

🎯 5. MATCH()

MATCH returns the position of a value in a range.

Syntax:

=MATCH(lookup_value,lookup_array,0)

Example:

List: Rahul, Priya, Amit

Formula: =MATCH("Priya",A2:A4,0)

Result: 2

📌 It returns the position, not the actual value.

📌 6. INDEX()

INDEX returns the value at a specified row and optionally column.

Syntax:

=INDEX(array,row_num)

Example:

Salary list: 50000, 60000, 70000

Formula: =INDEX(A2:A4,2)

Result: 60000

🚀 7. INDEX + MATCH (Powerful Combination)

This combination is more flexible than VLOOKUP.

Formula:

=INDEX(C2:C4,MATCH("E102",A2:A4,0))

Result: 60000

How It Works:

1. MATCH finds the row number of E102.

2. INDEX returns the salary from that row.

Advantages:

✅ Can look left or right

✅ Doesn't break when columns are inserted

✅ Faster on large datasets

✅ Easier to maintain

⭐ 8. XLOOKUP()

XLOOKUP is the modern replacement for VLOOKUP.

Syntax:

=XLOOKUP(lookup_value,lookup_array,return_array,"Not Found")

Example:

=XLOOKUP("E102",A2:A4,C2:C4,"Not Found")

Result: 60000

XLOOKUP vs VLOOKUP:

Left Lookup: VLOOKUP No, XLOOKUP Yes

Right Lookup: Both Yes

Default Error Message: VLOOKUP No, XLOOKUP Yes

Column Number Required: VLOOKUP Yes, XLOOKUP No

Easier to Read: VLOOKUP No, XLOOKUP Yes