Lookups and Data CleanupModule 1: Lookups Mastery: VLOOKUP → XLOOKUP → INDEX-MATCH

VLOOKUP Exact Match (FALSE / 0): Linking Two Datasets Seamlessly

Master =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The 4 rules to prevent #N/A errors.

मुफ़्त प्रीव्यू

आप एक पूरा पाठ प्रीव्यू कर रहे हैं।

यह पाठ मुफ़्त प्रीव्यू है। इसे पढ़ने के लिए अकाउंट की ज़रूरत नहीं।

Interactive Study Modes:Switch between stacked, split-screen workbench, or cinematic theater mode.

VLOOKUP Exact Match (FALSE / 0): Linking Two Datasets Seamlessly

The video for this lesson has not been published yet. The written lesson below is the full material for this session.

Instant AutoSumAutomates SUM formula insertion over highlighted rows and columns.

⌥Alt=

Toggle FiltersTurns on header dropdown filters for data sorting and filtering.

⌃Ctrl⇧ShiftL

Convert to Excel TableFormats range as a self-expanding, structured Excel table.

⌃CtrlT

Format Cells DialogOpens complete number, border, fill, and font formatting controls.

⌃Ctrl1

Lock Reference ($)Cycles through absolute and mixed reference locks in formulas.

F4

Interactive Spreadsheet Practice Sandbox

Practice live Excel formulas directly in your browser. Real calculation verified by HyperFormula.

Formula practice lab

Exercise

Exercise 1: Basic SUM Addition

Calculate total revenue in cell D6 by summing all items in column D (cells D2 to D5).

Every formula you enter is evaluated by a real spreadsheet engine in your browser — results are not pre-computed.

Hint:Use formula =SUM(D2:D5)

Exercise 1: Basic SUM Addition practice sheet
#	A	B	C	D
1	Product	Quantity	Unit Price	Total
2	Laptop	5	45000	225000
3	Mouse	20	500	10000
4	Keyboard	15	1200	18000
5	Monitor	8	12500	100000
6	Grand Total

Lesson notes & syllabus guide

VLOOKUP Exact Match (FALSE / 0)

VLOOKUP (Vertical Lookup) is the most famous function in spreadsheet history. It searches for a key in the first column of a table and returns a value from another column in the same row.

1. The 4 Arguments

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

lookup_value: What you are searching for (e.g. Part Number in cell A2).
table_array: The master catalog where the data lives. Rule 1: The lookup key must be in the very first column of this range! Use $ to lock: $F$2:$H$500.
col_index_num: The column number you want to retrieve (1 for first col, 2 for second, etc.).
range_lookup: Always type FALSE (or 0) for exact matches!

2. Practical Corporate Example

=VLOOKUP(A2, $F$2:$H$100, 3, FALSE)

Finds the Part Number in column F, and returns the Price from column H (3rd column of the selected array).

[!WARNING] If you omit the 4th argument, Excel defaults to TRUE (approximate match), which will return wrong data if your master table is not sorted alphabetically! Always type , 0.

Lesson resources

Download the workbooks and cheat sheets used in this lesson.

Tata Motors Spare Parts Price Directory & Orders — Practice Workbooktata_parts_price_directory_before.xlsx

VLOOKUP Exact Match Assessment

Pass mark 70%

5 questions on VLOOKUP arguments, column indexing, and exact matching.

This quiz has 5 questions. Sign in to answer them — results are saved to your account.

Parts Catalog VLOOKUP Integration

Community discussion

Lesson Q&A & Peer Discussions

Stuck on a formula or edge case? Ask questions, upvote solutions, and earn community XP.

No questions yet on this lesson

Be the first to ask a doubt or start a discussion with peers and mentors!

Product

Quantity

Unit Price

Total

Laptop

45000

225000

Mouse

500

10000

Keyboard

1200

18000

Monitor

12500

100000

Grand Total

VLOOKUP Exact Match (FALSE / 0)

VLOOKUP (Vertical Lookup) is the most famous function in spreadsheet history. It searches for a key in the first column of a table and returns a value from another column in the same row.

1. The 4 Arguments

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

lookup_value: What you are searching for (e.g. Part Number in cell A2).

table_array: The master catalog where the data lives. Rule 1: The lookup key must be in the very first column of this range! Use $ to lock: $F$2:$H$500.

col_index_num: The column number you want to retrieve (1 for first col, 2 for second, etc.).

range_lookup: Always type FALSE (or 0) for exact matches!

2. Practical Corporate Example

=VLOOKUP(A2, $F$2:$H$100, 3, FALSE)

Finds the Part Number in column F, and returns the Price from column H (3rd column of the selected array).

[!WARNING] If you omit the 4th argument, Excel defaults to TRUE (approximate match), which will return wrong data if your master table is not sorted alphabetically! Always type , 0.