🔍 Day 7: Lookup Functions Mastery

VLOOKUP • HLOOKUP • MATCH

Ghaziabad, Mohan Nagar

Drafted by: HR Prateek Sharma

📊 Master Data Lookup & Retrieval

Learn to search and extract data efficiently from large datasets

📋 VLOOKUP - Vertical Lookup

V

What is VLOOKUP?

VLOOKUP searches for data vertically (down columns) in a table and returns corresponding values.

↓
VERTICAL

4 Required Parameters:

1 Lookup Value: What to search/find

Example: Emp_ID - The value you want to find

2 Table Array: Where to find the data

Example: Emp_Table - The range containing all data

3 Column Index Number: Which column to return

Example: 2 - Returns data from 2nd column (e.g., Name)

4 Range Lookup/Match Type: Exact or approximate match

0 or FALSE = Exact match (recommended)
1 or TRUE = Approximate match

Syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

📌 Example:

Find employee name using Employee ID:
=VLOOKUP(A2, EmployeeTable, 2, 0)

$

Cell Locking (Absolute Reference)

💡 Freeze/Lock Cells: Use the $ (dollar sign) to lock cell references

Why? Prevents cell references from changing when copying formulas

📌 Example with Locked Cells:

=VLOOKUP($A$2, $B$2:$F$100, 2, 0)

• $A$2 = Locked row and column
• $B$2:$F$100 = Locked table range

⚠️ Common Issue:

Need to change column index number again and again when copying formula across different columns

✅ Solution: Use MATCH Function!

MATCH automatically finds the column index number, eliminating manual changes

🎯 MATCH Function - Smart Column Finder

M

What is MATCH?

MATCH finds the position of a value within a range, automatically determining the column index number.

3 Parameters:

1 Lookup Value: What to find

Example: Column header name like "Name" or "Salary"

2 Table Array: Where to search

Example: The header row range

3 Match Type: Exact or approximate

0 = Exact match (most common)

VLOOKUP + MATCH Combination:

=VLOOKUP($I$65, $TableArray$, MATCH(SelectedName, $HeaderRange$, 0), 0)

📌 Power Formula Example:

=VLOOKUP($A2, $B$2:$F$100, MATCH(H1, $B$1:$F$1, 0), 0)

Breakdown:
• $A2 = Lookup value (Emp_ID)
• $B$2:$F$100 = Data table (locked)
• MATCH(H1, $B$1:$F$1, 0) = Finds column automatically
• 0 = Exact match

💡 Pro Tip: MATCH($I65) converts whole column according to their Emp_ID automatically!

📊 HLOOKUP - Horizontal Lookup

H

What is HLOOKUP?

HLOOKUP searches for data horizontally (across rows) in a table and returns corresponding values.

→ HORIZONTAL

4 Required Parameters:

1 Lookup Value: What to search/find

2 Table Array: Where to find the data

3 Row Index Number: Which row to return (instead of column)

4 Match Type: Exact or approximate match

0 = Exact | 1 = Approximate

Syntax:

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

📌 Example:

Find data from the 3rd row of a horizontal table:
=HLOOKUP(A1, $A$1:$Z$10, 3, 0)

⚖️ VLOOKUP vs HLOOKUP

VLOOKUP

↓

Vertical search
Searches down columns
Uses Column Index

Most commonly used!

HLOOKUP

→

Horizontal search
Searches across rows
Uses Row Index

Less common, but useful!

🎯 Key Takeaway: Choose based on your data structure!
Vertical data → VLOOKUP | Horizontal data → HLOOKUP

Ghaziabad, Mohan Nagar

Design & Content by HR Prateek Sharma

Day 7 - Lookup Functions Training

VLOOKUP • HLOOKUP • MATCH • Data Retrieval Mastery