📊 Excel Formulas — Part 4

This part focuses on Lookup formulas — essential when you need to find information from another table.

1️⃣ XLOOKUP

Searches for a value and returns the corresponding result.

=XLOOKUP(A2,E2:E10,F2:F10,"Not Found")

Example: Find an employee's department using their Employee ID.

Why it's useful:

• Can look left or right

• Exact match by default

• Can return a custom result when nothing is found

2️⃣ VLOOKUP

Searches vertically in the first column of a table.

=VLOOKUP(A2,E2:G10,3,FALSE)

Example: Find a product's price using its Product ID.

Remember: FALSE → Exact match

TRUE → Approximate match

3️⃣ HLOOKUP

Searches horizontally across the first row of a table.

=HLOOKUP(B1,B2:F5,4,FALSE)

Useful when your lookup values are arranged horizontally.

4️⃣ INDEX

Returns a value from a specific position in a range.

=INDEX(B2:B10,4)

Example: Return the 4th value from the range B2:B10.

5️⃣ MATCH

Finds the position of a value within a range.

=MATCH("Laptop",A2:A10,0)

0 means you want an exact match.

6️⃣ INDEX + MATCH

A powerful combination for lookups.

=INDEX(C2:C10,MATCH(A2,A2:A10,0))

Here:

MATCH → Finds the position

INDEX → Returns the value from that position

💡 Sample Data

Product ID Product Price

P101 Laptop 50000

P102 Mouse 800

P103 Keyboard 1500

P104 Monitor 12000

P105 Headphones 2500

Try finding the Price for Product ID P103 using:

1. XLOOKUP

2. VLOOKUP

3. INDEX + MATCH

🧠 Double Tap ❤️ For More