Microsoft Excel Mastery

Part IV: Lookup & Reference Functions

VLOOKUP, HLOOKUP, XLOOKUP, INDEX-MATCH, Dynamic Arrays — with real Indian business examples from GST, CBSE, Flipkart, Zomato & TCS.

🔍 5 Chapters | 📊 100+ Solved Examples | 📝 25+ MCQs | 🎯 19 Interview Questions | 🚀 5 Mini Projects

Chapter 13

VLOOKUP — Vertical Lookup

🏢 How Flipkart Looks Up Product Prices in Milliseconds

Imagine Flipkart's catalogue with 150 million+ products. When a customer searches for "Samsung Galaxy S24", the system must instantly look up its price, stock status, seller details, and delivery time from a massive product database. This is essentially what VLOOKUP does — you give it a product ID, and it fetches the corresponding information from a table. In the corporate world, VLOOKUP is the single most-used Excel function after SUM.

FlipkartTCSInfosysReliance

Learning Objectives

What You'll Learn

Understand the syntax and all 4 arguments of VLOOKUP
Distinguish between Exact Match (FALSE/0) and Approximate Match (TRUE/1)
Use VLOOKUP to look up student marks, product prices, employee details, and GST rates
Troubleshoot common VLOOKUP errors: #N/A, #REF!, #VALUE!
Understand limitations of VLOOKUP and when to use alternatives
Use VLOOKUP with wildcards for partial matching
Perform two-way lookups using VLOOKUP + MATCH
Build a complete Student Result Lookup System

Theory & Concepts

What is VLOOKUP?

VLOOKUP stands for Vertical Lookup. It searches for a value in the first column of a table and returns a value from a specified column in the same row. Think of it like looking up a word in a dictionary — you search for the word (first column) and read its meaning (another column in the same row).

📐 VLOOKUP Syntax

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

lookup_value — The value to search for in the first column of the table.
table_array — The range of cells containing the data table (lookup column must be first).
col_index_num — The column number in the table from which to return the value (1 = first column).
range_lookup — FALSE (or 0) for exact match; TRUE (or 1) for approximate match. Always use FALSE unless you specifically need approximate matching.

Exact Match vs Approximate Match

This is the most critical concept in VLOOKUP. The 4th argument determines how Excel searches:

Feature	Exact Match (FALSE / 0)	Approximate Match (TRUE / 1)
What it does	Finds only an exact match of the lookup value	Finds the closest value less than or equal to lookup value
Data sorting required?	No — works with unsorted data	Yes — first column MUST be sorted ascending
If no match found	Returns `#N/A` error	Returns the next smaller value's row
Common use case	Product ID lookup, Student roll number	Tax slab lookup, Grade assignment, Commission tiers
Recommended?	Yes — use this by default	Only for range-based lookups

[Screenshot: VLOOKUP function dialog box showing all 4 arguments with descriptions]

Real-World Analogy

Think of VLOOKUP like a railway reservation chart. You have a PNR number (lookup_value). The chart (table_array) has PNR in the first column, followed by passenger name, berth, coach, and status. You look up your PNR and read across to find your berth (col_index_num = 4 for berth column). Exact match means you need the exact PNR — not "close enough".

Step-by-Step: Your First VLOOKUP

Let's create a Student Marks Database. Enter this data in cells A1:E6:

Roll No (A)	Name (B)	Maths (C)	Science (D)	English (E)
101	Aarav Sharma	85	92	78
102	Priya Patel	91	88	95
103	Rohan Gupta	76	69	82
104	Sneha Iyer	94	97	91
105	Vikram Singh	68	73	71

Step 1: In cell G2, type the Roll Number you want to look up: 103

Step 2: In cell H2, enter the formula:

=VLOOKUP(G2, A2:E6, 2, FALSE)

Step 3: Press Enter. Result: Rohan Gupta

[Screenshot: Excel showing VLOOKUP formula in H2 returning "Rohan Gupta" for Roll No 103]

How it works:

Excel searches for 103 in the first column (A2:A6) of the table
It finds 103 in row 4 (A4)
col_index_num = 2, so it returns the value from the 2nd column of that row → B4 = "Rohan Gupta"
FALSE means exact match — it won't accept 102 or 104 as "close enough"

Worked Examples — Exact Match

Example 1: Student Maths Marks Lookup

Formula

=VLOOKUP(104, A2:E6, 3, FALSE)

Result: 94 — Looks up Roll No 104 (Sneha Iyer) and returns the 3rd column (Maths marks).

Example 2: Product Price Lookup

Product ID (A)	Product Name (B)	Price ₹ (C)	GST % (D)	Stock (E)
P001	Wireless Mouse	599	18	150
P002	USB Keyboard	899	18	200
P003	Monitor 24"	12999	28	45
P004	Laptop Stand	1499	18	80
P005	Webcam HD	2499	18	60

Formula — Look up price of P003

=VLOOKUP("P003", A2:E6, 3, FALSE)

Result: 12999 — Finds P003 in the first column and returns the price (3rd column).

Formula — Calculate price with GST

=VLOOKUP("P003",A2:E6,3,FALSE) * (1 + VLOOKUP("P003",A2:E6,4,FALSE)/100)

Result: 16638.72 — 12999 × (1 + 28/100) = 12999 × 1.28 = ₹16,638.72 including 28% GST.

Example 3: Employee Details Lookup

Emp ID	Name	Department	Salary ₹	City
E1001	Amit Kumar	IT	85000	Bengaluru
E1002	Neha Reddy	HR	72000	Hyderabad
E1003	Raj Malhotra	Finance	95000	Mumbai
E1004	Kavita Nair	IT	88000	Chennai
E1005	Suresh Joshi	Marketing	68000	Pune

=VLOOKUP("E1003", A2:E6, 4, FALSE)   → Result: 95000
=VLOOKUP("E1004", A2:E6, 3, FALSE)   → Result: IT
=VLOOKUP("E1005", A2:E6, 5, FALSE)   → Result: Pune

Approximate Match — Tax Slab Lookup

Approximate match is perfect for range-based lookups where you need to find which bracket a value falls into. The classic Indian example is the Income Tax Slab.

Indian Income Tax Slabs (New Regime FY 2024-25):

Income From ₹ (A)	Tax Rate % (B)
0	0
300001	5
700001	10
1000001	15
1200001	20
1500001	30

Note: The "Income From" column MUST be sorted in ascending order for approximate match.

Example 1 — Income ₹8,50,000

=VLOOKUP(850000, A2:B7, 2, TRUE)

Result: 10 — Excel searches for 850000. It doesn't find an exact match, so it returns the rate for the largest value ≤ 850000, which is 700001 → 10%.

Example 2 — Income ₹4,50,000

=VLOOKUP(450000, A2:B7, 2, TRUE)

Result: 5 — Largest value ≤ 450000 is 300001 → 5% rate.

Example 3 — Income ₹18,00,000

=VLOOKUP(1800000, A2:B7, 2, TRUE)

Result: 30 — Largest value ≤ 1800000 is 1500001 → 30% rate.

Unsorted data with approximate match: If the first column is NOT sorted in ascending order, VLOOKUP with TRUE will return incorrect results without any error message. This is the most dangerous VLOOKUP mistake because Excel gives a wrong answer silently. Always sort your data before using approximate match.

GST Rate Lookup

HSN Code (A)	Product Category (B)	GST Rate % (C)
0401	Milk & Cream	0
1006	Rice	5
3004	Medicines	12
6109	T-shirts & Vests	5
8471	Computers & Laptops	18
8703	Motor Cars	28

=VLOOKUP("8471", A2:C7, 3, FALSE)   → Result: 18 (Computers attract 18% GST)
=VLOOKUP("0401", A2:C7, 2, FALSE)   → Result: Milk & Cream
=VLOOKUP("8703", A2:C7, 3, FALSE)   → Result: 28 (Cars attract 28% GST)

VLOOKUP with Wildcards

You can use * (any number of characters) and ? (single character) as wildcards in exact match VLOOKUP:

Wildcard Example — Find any employee with name starting with "Ka"

=VLOOKUP("Ka*", B2:E6, 1, FALSE)

Result: Kavita Nair — The * matches any characters after "Ka". Note: the lookup column here must contain the names.

=VLOOKUP("*Kumar", B2:E6, 1, FALSE)  → Amit Kumar (ends with Kumar)
=VLOOKUP("???a Reddy", B2:E6, 1, FALSE)  → Neha Reddy (? matches single character)

Two-Way Lookup: VLOOKUP + MATCH

Standard VLOOKUP requires you to hard-code the column number. But what if you want users to choose which subject's marks to look up? Use MATCH to make the column number dynamic:

Two-Way Lookup Formula

=VLOOKUP(G2, A1:E6, MATCH(H1, A1:E1, 0), FALSE)

If G2 = 103 (Roll No) and H1 = "Science", MATCH finds that "Science" is in position 4 of the header row. VLOOKUP then returns col 4 for Roll No 103 → Result: 69

Pincode-to-City Lookup

Pincode	City	State	Zone
110001	New Delhi	Delhi	North
400001	Mumbai	Maharashtra	West
560001	Bengaluru	Karnataka	South
600001	Chennai	Tamil Nadu	South
700001	Kolkata	West Bengal	East
500001	Hyderabad	Telangana	South

=VLOOKUP(560001, A2:D7, 2, FALSE)  → Bengaluru
=VLOOKUP(400001, A2:D7, 3, FALSE)  → Maharashtra
=VLOOKUP(700001, A2:D7, 4, FALSE)  → East

Common Errors & Troubleshooting

#N/A Error: The lookup value doesn't exist in the first column. Common causes: (1) Extra spaces — use =VLOOKUP(TRIM(G2), ...), (2) Number stored as text — use =VLOOKUP(VALUE(G2), ...), (3) Typo in the lookup value.

#REF! Error: The col_index_num is greater than the number of columns in table_array. E.g., if your table has 5 columns and you use col_index_num = 6, you get #REF!.

Wrong column index: Remember, col_index_num counts from the FIRST column of table_array, not from column A. If your table starts at column C, then col_index_num = 1 refers to column C, not column A.

VLOOKUP can only look RIGHT: The lookup column must be the leftmost column in your table_array. If you need to look left, use INDEX-MATCH or XLOOKUP instead.

Wrap VLOOKUP in IFERROR to handle #N/A gracefully:
=IFERROR(VLOOKUP(G2,A2:E6,2,FALSE), "Roll No Not Found")
This shows a friendly message instead of an ugly error.

F2 — Edit cell to see formula references highlighted in color

Ctrl + ` — Toggle formula view to see all formulas at once

F4 — Toggle absolute/relative references (critical for locking table_array: $A$2:$E$6)

Tab — Accept formula autocomplete suggestion

VLOOKUP Limitations

Limitation	Explanation	Alternative
Can only look right	Lookup column must be leftmost	INDEX-MATCH, XLOOKUP
Single column return	Returns value from one column only	Multiple VLOOKUPs or XLOOKUP
Column index breaks on insert	If you insert a column, the hard-coded col_index changes	VLOOKUP+MATCH or INDEX-MATCH
Returns first match only	Can't find 2nd, 3rd occurrence	FILTER function, helper columns
Slower on large datasets	Scans entire column for each lookup	INDEX-MATCH is faster

Solved Examples

25 Solved VLOOKUP Examples

Examples 4-8: Employee Database Lookups

=VLOOKUP("E1001",A2:E6,2,FALSE)              → Amit Kumar
=VLOOKUP("E1002",A2:E6,4,FALSE)              → 72000
=VLOOKUP("E1004",A2:E6,5,FALSE)              → Chennai
=IFERROR(VLOOKUP("E9999",A2:E6,2,FALSE),"Not Found")  → Not Found
=VLOOKUP("E10"&"03",A2:E6,3,FALSE)           → Finance (concatenation works)

Examples 9-13: Product Calculations

=VLOOKUP("P001",A2:E6,3,FALSE)+100                    → 699 (price + shipping)
=VLOOKUP("P004",A2:E6,3,FALSE)*1.18                   → 1768.82 (price with 18% GST)
=VLOOKUP("P005",A2:E6,5,FALSE)>50                     → TRUE (stock > 50?)
=IF(VLOOKUP("P002",A2:E6,5,FALSE)>100,"In Stock","Low Stock") → In Stock
=VLOOKUP("P003",A2:E6,3,FALSE)*VLOOKUP("P003",A2:E6,5,FALSE) → 584955 (value of stock)

Examples 14-18: Grade Assignment with Approximate Match

Min Marks (A)	Grade (B)
0	F
33	D
45	C
60	B
75	A
90	A+

=VLOOKUP(85, A2:B7, 2, TRUE)    → A  (85 falls between 75 and 89)
=VLOOKUP(92, A2:B7, 2, TRUE)    → A+ (92 ≥ 90)
=VLOOKUP(44, A2:B7, 2, TRUE)    → D  (44 falls between 33 and 44)
=VLOOKUP(60, A2:B7, 2, TRUE)    → B  (exact boundary = 60)
=VLOOKUP(25, A2:B7, 2, TRUE)    → F  (25 falls between 0 and 32)

Examples 19-22: VLOOKUP with Other Functions

=UPPER(VLOOKUP(101,A2:E6,2,FALSE))            → AARAV SHARMA
=LEFT(VLOOKUP(102,A2:E6,2,FALSE),5)            → Priya
=VLOOKUP(103,A2:E6,3,FALSE)+VLOOKUP(103,A2:E6,4,FALSE)+VLOOKUP(103,A2:E6,5,FALSE) → 227 (total marks)
=AVERAGE(VLOOKUP(104,A2:E6,3,FALSE),VLOOKUP(104,A2:E6,4,FALSE),VLOOKUP(104,A2:E6,5,FALSE)) → 94

Examples 23-25: Advanced VLOOKUP

/* Two-way lookup with MATCH */
=VLOOKUP(102, A1:E6, MATCH("English",A1:E1,0), FALSE)  → 95

/* VLOOKUP returning column header */
=INDEX(A1:E1, MATCH(MAX(C2:E2), C2:E2, 0)+2)           → Returns subject with highest marks

/* Nested IF with VLOOKUP */
=IF(VLOOKUP(105,A2:E6,3,FALSE)>=75, "Distinction",
 IF(VLOOKUP(105,A2:E6,3,FALSE)>=60, "First Class",
 IF(VLOOKUP(105,A2:E6,3,FALSE)>=45, "Second Class", "Fail")))  → Second Class

Practice Exercises

Use this Zomato Restaurant Dataset for exercises:

Rest ID	Restaurant Name	Cuisine	Rating	Avg Cost ₹	City
Z001	Barbeque Nation	North Indian	4.2	1200	Bengaluru
Z002	Domino's Pizza	Italian	3.8	400	Mumbai
Z003	Saravana Bhavan	South Indian	4.5	350	Chennai
Z004	Haldiram's	Snacks	4.0	250	Delhi
Z005	Paradise Biryani	Hyderabadi	4.6	500	Hyderabad
Z006	MTR Restaurant	South Indian	4.3	450	Bengaluru

Exercise 1: Look up the cuisine type of restaurant Z004. Answer: =VLOOKUP("Z004",A2:F7,3,FALSE) → Snacks

Exercise 2: Find the average cost for Z005. Answer: =VLOOKUP("Z005",A2:F7,5,FALSE) → 500

Exercise 3: Look up the rating of Saravana Bhavan and check if it's above 4. Answer: =VLOOKUP("Z003",A2:F7,4,FALSE)>4 → TRUE

Exercise 4: Find which city Z006 is in and display "Delivery Available" if it's Bengaluru. Answer: =IF(VLOOKUP("Z006",A2:F7,6,FALSE)="Bengaluru","Delivery Available","Check Coverage")

Exercise 5: Calculate the GST amount (5%) on the average cost for Z001. Answer: =VLOOKUP("Z001",A2:F7,5,FALSE)*0.05 → 60

Exercise 6: Use IFERROR to show "Restaurant Not Listed" when looking up Z999. Answer: =IFERROR(VLOOKUP("Z999",A2:F7,2,FALSE),"Restaurant Not Listed")

Exercise 7: Use a wildcard VLOOKUP to find any restaurant starting with "Para". Answer: =VLOOKUP("Para*",B2:F7,1,FALSE) → Paradise Biryani

Exercise 8: Create a two-way lookup: Given Rest ID in H1 and column header "Rating" in I1, write the formula. Answer: =VLOOKUP(H1,A1:F7,MATCH(I1,A1:F1,0),FALSE)

Exercise 9: Look up the cost for Z002 and Z003, and find their total. Answer: =VLOOKUP("Z002",A2:F7,5,FALSE)+VLOOKUP("Z003",A2:F7,5,FALSE) → 750

Exercise 10: Write a formula that returns "Premium" if avg cost > 500, else "Budget" for any given restaurant ID in cell H1. Answer: =IF(VLOOKUP(H1,A2:F7,5,FALSE)>500,"Premium","Budget")

Assignment

📋 Assignment: Indian Railway Fare Calculator

Create a workbook with these components:

A Train Master Data sheet with columns: Train No, Train Name, From Station, To Station, Sleeper Fare ₹, AC-3 Fare ₹, AC-2 Fare ₹, AC-1 Fare ₹, Distance (km)
Enter at least 10 trains (Rajdhani, Shatabdi, Duronto, etc.)
A Fare Lookup sheet where user enters Train No and Class, and the fare is automatically looked up using VLOOKUP + MATCH
Add IFERROR to handle invalid train numbers
Calculate the GST (5% on AC fares, 0% on Sleeper) using an IF formula combined with VLOOKUP

MCQ Assessment

VLOOKUP Quiz (5 MCQs)

What does the 4th argument FALSE mean in VLOOKUP?

Search from bottom to top
Return approximate match
Return exact match only
Ignore errors

✅ Answer: (c) — FALSE (or 0) tells VLOOKUP to find an exact match. TRUE would allow approximate matching.

Which error will =VLOOKUP("XYZ", A2:C10, 5, FALSE) produce if the table has only 3 columns?

#N/A
#VALUE!
#REF!
#NAME?

✅ Answer: (c) — #REF! occurs when col_index_num exceeds the number of columns in table_array. You asked for column 5 from a 3-column table.

For approximate match (TRUE) to work correctly, the first column of the table must be:

Sorted in descending order
Sorted in ascending order
Contain unique values only
Contain text values only

✅ Answer: (b) — Approximate match requires the first column to be sorted in ascending order. Otherwise, results are unpredictable.

VLOOKUP can search for a value in which direction?

Left to right in a row
Top to bottom in the first column, then returns a value to the right
Right to left in a row
Bottom to top in a column

✅ Answer: (b) — VLOOKUP searches vertically (top to bottom) in the first column and can only return values from columns to the right.

What will =VLOOKUP(102, A2:E6, 2, FALSE) return if Roll No 102 appears twice in the data?

Both values
The value from the first occurrence
The value from the last occurrence
#VALUE! error

✅ Answer: (b) — VLOOKUP always returns the first match found (top to bottom). It cannot return multiple matches.

Mini Project

🚀 Student Result Lookup System

📊 Project: Complete Student Result Lookup System

Problem Statement

Build an interactive Excel-based Student Result Lookup System for a CBSE school. A teacher enters a Roll Number, and the system automatically displays the student's complete result card.

Requirements

Database Sheet: Contains 20 students with columns: Roll No, Name, Class, Section, Maths, Science, English, Hindi, Social Science, Computer, Total, Percentage, Grade
Lookup Sheet: Has an input cell for Roll Number and displays:
- Student Name (VLOOKUP col 2)
- Class & Section (VLOOKUP cols 3, 4)
- Individual subject marks (VLOOKUP cols 5-10)
- Total, Percentage, Grade (VLOOKUP cols 11-13)
- Pass/Fail status using IF with VLOOKUP
- Rank using RANK function
All lookups must use IFERROR for invalid roll numbers
Use conditional formatting to highlight marks below 33 (fail) in red
Add a Data Validation dropdown for Roll Number selection

Deliverables

Excel file with Database and Lookup sheets
Formatted result card layout with school header
At least 20 student records with realistic CBSE data
Auto-calculated Total, Percentage, and Grade

[Screenshot: Final Student Result Lookup System showing Roll No input and all auto-populated fields]

Interview Corner

VLOOKUP Interview Questions

Q1: What is the difference between VLOOKUP with TRUE and FALSE?

Answer: FALSE (exact match) searches for the exact lookup value and returns #N/A if not found. TRUE (approximate match) finds the largest value that is less than or equal to the lookup value — but requires the first column to be sorted in ascending order. Use FALSE for ID lookups (Employee ID, Product Code) and TRUE for range-based lookups (tax slabs, grade assignment, commission tiers).

Q2: A VLOOKUP is returning #N/A even though the value exists in the table. What could be wrong?

Answer: Common causes: (1) Extra spaces — use TRIM() on both lookup and table values. (2) Number stored as text or vice versa — one cell has 101 as a number, the other as text "101". Use VALUE() or TEXT() to convert. (3) Hidden characters — use CLEAN() + TRIM(). (4) Lookup value not in first column of the specified table_array. (5) Table_array doesn't cover the correct range.

Q3: Why is INDEX-MATCH considered better than VLOOKUP?

Answer: (1) INDEX-MATCH can look in any direction — left, right, up, down — while VLOOKUP only looks right. (2) INDEX-MATCH is not affected by column insertions/deletions since it references actual ranges, not column numbers. (3) INDEX-MATCH is faster on large datasets because MATCH only searches the lookup column, while VLOOKUP loads the entire table_array. (4) INDEX-MATCH supports multiple criteria more naturally.

Q4: How would you use VLOOKUP to look up data from a different sheet or workbook?

Answer: For a different sheet: =VLOOKUP(A2, Sheet2!A:E, 3, FALSE). For a different workbook (must be open): =VLOOKUP(A2, [Workbook.xlsx]Sheet1!A:E, 3, FALSE). Best practice: use named ranges to make formulas more readable, e.g., =VLOOKUP(A2, EmployeeData, 3, FALSE) where EmployeeData is a named range.

Q5: How do you handle VLOOKUP returning the first match when you need all matches?

Answer: VLOOKUP always returns the first match. For all matches: (1) Use FILTER() function (Excel 365/2021): =FILTER(B2:B100, A2:A100="IT"). (2) Use helper column with COUNTIF to create unique keys. (3) Use Power Query for complex multi-match scenarios. (4) In older Excel, use a combination of SMALL, IF, ROW, and INDEX as an array formula.

Pedagogy tip: Start VLOOKUP teaching with a physical analogy — bring a printed phone directory to class and ask students to look up a name. They naturally search the first column (name) and read across (phone number). This IS VLOOKUP. Then transition to Excel. Students struggle most with: (1) understanding that lookup column must be leftmost, (2) the difference between TRUE/FALSE, and (3) locking table_array with $. Spend extra time on these three concepts.

📋 Chapter 13 Summary

VLOOKUP searches the first column of a table and returns a value from a specified column
Use FALSE (exact match) for ID-based lookups; TRUE (approximate) for range-based lookups
Approximate match requires sorted ascending data in the first column
Always wrap with IFERROR() for production-ready formulas
Use F4 to lock table_array with $ signs when copying formulas
VLOOKUP can only look right — for leftward lookups, use INDEX-MATCH or XLOOKUP
Use wildcards * and ? for partial matching in exact match mode
Combine with MATCH() for dynamic column selection (two-way lookup)

Chapter 14

HLOOKUP — Horizontal Lookup

📊 When Your Data Flows Sideways

Not all data sits in neat vertical columns. Think of a CBSE report card where subjects are written across the top (Jan, Feb, Mar, Apr...) and each student has one row. Or a quarterly sales dashboard where Q1, Q2, Q3, Q4 are column headers. When your data is arranged horizontally, VLOOKUP can't help — you need its sibling, HLOOKUP.

CBSESchoolsFinancial Reports

Learning Objectives

What You'll Learn

Understand HLOOKUP syntax and all its arguments
Know when to choose HLOOKUP over VLOOKUP
Apply HLOOKUP to horizontal datasets (semester marks, monthly sales)
Handle errors and understand limitations
Build a Semester Performance Tracker mini project

Theory & Concepts

What is HLOOKUP?

HLOOKUP stands for Horizontal Lookup. It searches for a value in the first row of a table and returns a value from a specified row in the same column. It's the horizontal counterpart of VLOOKUP.

📐 HLOOKUP Syntax

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

lookup_value — The value to search for in the first row of the table.
table_array — The range containing the data (lookup row must be the first row).
row_index_num — The row number in the table from which to return the value (1 = first row).
range_lookup — FALSE for exact match; TRUE for approximate match.

VLOOKUP vs HLOOKUP — When to Use Which?

Feature	VLOOKUP	HLOOKUP
Data orientation	Vertical (data in columns)	Horizontal (data in rows)
Searches in	First column	First row
Returns from	Specified column number	Specified row number
Index argument	col_index_num	row_index_num
Common use case	Most database tables	Time series, schedule tables
Frequency of use	Very common (90%+ use cases)	Rare (niche use cases)

In real-world Excel usage, VLOOKUP is used about 10x more than HLOOKUP because most databases store records as rows (vertical). HLOOKUP is mostly seen in academic schedules, financial summary tables, and legacy reports where data was arranged horizontally.

Step-by-Step: Your First HLOOKUP

A CBSE school stores monthly test scores horizontally. Data in A1:G4:

	Jan (B)	Feb (C)	Mar (D)	Apr (E)	May (F)	Jun (G)
Maths (A2)	78	82	85	79	91	88
Science (A3)	72	75	80	83	87	90
English (A4)	85	88	82	90	92	95

Question: What was the Maths score in March?

Formula

=HLOOKUP("Mar", B1:G4, 2, FALSE)

Result: 85 — Searches for "Mar" in the first row (B1:G1), finds it in column D. Returns row 2 value from that column → D2 = 85.

Example 2 — Science score in May

=HLOOKUP("May", B1:G4, 3, FALSE)

Result: 87 — Finds "May" in row 1, returns row 3 (Science) → 87.

Example 3 — English score in Jun

=HLOOKUP("Jun", B1:G4, 4, FALSE)

Result: 95 — Finds "Jun" in row 1, returns row 4 (English) → 95.

Worked Examples — Quarterly Sales

TCS Quarterly Revenue (₹ Crores):

	Q1	Q2	Q3	Q4
Revenue	59692	61237	60583	62613
Profit	11342	11726	11058	12434
Employees	601400	603456	600987	607000

=HLOOKUP("Q2", B1:E4, 2, FALSE)       → 61237  (Q2 Revenue)
=HLOOKUP("Q4", B1:E4, 3, FALSE)       → 12434  (Q4 Profit)
=HLOOKUP("Q1", B1:E4, 4, FALSE)       → 601400 (Q1 Employees)
=HLOOKUP("Q4",B1:E4,2,FALSE) - HLOOKUP("Q1",B1:E4,2,FALSE)  → 2921 (Revenue growth Q1→Q4)
=HLOOKUP("Q3",B1:E4,3,FALSE)/HLOOKUP("Q3",B1:E4,2,FALSE)*100 → 18.25% (Q3 Profit Margin)

Semester-wise Student Performance

	Sem 1	Sem 2	Sem 3	Sem 4	Sem 5	Sem 6
Aarav	7.8	8.2	8.5	7.9	9.1	8.8
Priya	8.5	8.8	9.0	8.7	9.2	9.4
Rohan	6.5	7.0	7.3	7.8	8.0	8.5

=HLOOKUP("Sem 4", B1:G4, 2, FALSE)     → 7.9  (Aarav's Sem 4 SGPA)
=HLOOKUP("Sem 6", B1:G4, 3, FALSE)     → 9.4  (Priya's Sem 6 SGPA)
=HLOOKUP("Sem 1", B1:G4, 4, FALSE)     → 6.5  (Rohan's Sem 1 SGPA)

/* Average SGPA across semesters for Priya */
=AVERAGE(HLOOKUP("Sem 1",B1:G4,3,FALSE), HLOOKUP("Sem 2",B1:G4,3,FALSE),
         HLOOKUP("Sem 3",B1:G4,3,FALSE), HLOOKUP("Sem 4",B1:G4,3,FALSE),
         HLOOKUP("Sem 5",B1:G4,3,FALSE), HLOOKUP("Sem 6",B1:G4,3,FALSE))  → 8.93

/* Improvement from Sem 1 to Sem 6 for Rohan */
=HLOOKUP("Sem 6",B1:G4,4,FALSE) - HLOOKUP("Sem 1",B1:G4,4,FALSE)  → 2.0

HLOOKUP with Approximate Match

Delivery time based on distance (km):

Distance	0	50	200	500	1000	2000
Days	1	2	3	5	7	10

=HLOOKUP(150, A1:F2, 2, TRUE)    → 2 (150 falls between 50 and 199)
=HLOOKUP(750, A1:F2, 2, TRUE)    → 5 (750 falls between 500 and 999)
=HLOOKUP(2500, A1:F2, 2, TRUE)   → 10 (2500 ≥ 2000)

More Solved Examples

Indian Festival Sales Data

	Diwali	Holi	Eid	Christmas	Navratri
Flipkart ₹Cr	45000	8500	6200	12000	9800
Amazon ₹Cr	42000	7800	5900	13500	8700
Meesho ₹Cr	8500	3200	2100	4500	3800

=HLOOKUP("Diwali", B1:F4, 2, FALSE)    → 45000 (Flipkart Diwali sales)
=HLOOKUP("Eid", B1:F4, 3, FALSE)       → 5900 (Amazon Eid sales)
=HLOOKUP("Diwali",B1:F4,2,FALSE) + HLOOKUP("Diwali",B1:F4,3,FALSE)  → 87000 (Total Diwali: Flipkart + Amazon)
=HLOOKUP("Christmas",B1:F4,3,FALSE) > HLOOKUP("Christmas",B1:F4,2,FALSE)  → TRUE (Amazon beat Flipkart at Christmas)

Using HLOOKUP when VLOOKUP would work: If your data can easily be transposed to a vertical format, use VLOOKUP instead. HLOOKUP should only be used when the data is naturally horizontal (time series, schedules). Most Excel experts recommend converting horizontal data to vertical using Paste Special → Transpose.

Teaching tip: HLOOKUP gets maybe 10% of class time. Teach it as "VLOOKUP rotated 90°" — same logic, different direction. Focus more time on XLOOKUP which replaces both VLOOKUP and HLOOKUP.

Practice Exercises

Exercise 1: Using the monthly test scores table, find the Science score in February. Answer: =HLOOKUP("Feb",B1:G4,3,FALSE) → 75

Exercise 2: Find the average of Maths scores across all 6 months using HLOOKUP. Answer: Use 6 HLOOKUP calls inside AVERAGE

Exercise 3: Look up TCS Q3 Revenue and calculate 22% tax on it. Answer: =HLOOKUP("Q3",B1:E4,2,FALSE)*0.22 → 13328.26

Exercise 4: Find the profit margin (Profit/Revenue) for TCS in Q1. Answer: =HLOOKUP("Q1",B1:E4,3,FALSE)/HLOOKUP("Q1",B1:E4,2,FALSE) → 19.0%

Exercise 5: Use IFERROR with HLOOKUP to return "Quarter Not Found" for an invalid quarter. Answer: =IFERROR(HLOOKUP("Q5",B1:E4,2,FALSE),"Quarter Not Found")

Exercise 6: Find the festival where Amazon had the highest sales using HLOOKUP+MAX. Hint: Combine INDEX, MATCH, and MAX

Exercise 7: Look up delivery days for a 350 km distance using approximate match. Answer: =HLOOKUP(350,A1:F2,2,TRUE) → 3

Exercise 8: Create a two-way lookup combining HLOOKUP with an IF to check semester performance. Answer: =IF(HLOOKUP("Sem 6",B1:G4,3,FALSE)>9,"Excellent","Good")

Ctrl + Shift + T — Transpose selected range (via Paste Special)

Alt + = — Auto-SUM (works great with HLOOKUP results)

Ctrl + ~ — Toggle between value view and formula view

More Examples & Assignments

HLOOKUP Limitations & Alternatives

Limitation	Description	Alternative
Can only look down	Lookup value must be in the first row	INDEX-MATCH or XLOOKUP
Row index is fragile	Inserting rows changes the index	INDEX-MATCH
Not common in databases	Most data is stored vertically	Transpose + VLOOKUP
Deprecated in modern Excel	XLOOKUP handles both H and V	XLOOKUP

MCQ Assessment

HLOOKUP Quiz (5 MCQs)

HLOOKUP searches for a value in the:

First column of a table
First row of a table
Last row of a table
Any row of a table

✅ Answer: (b) — HLOOKUP stands for Horizontal Lookup and searches in the first ROW.

The "H" in HLOOKUP stands for:

High
Header
Horizontal
Hybrid

✅ Answer: (c) — H stands for Horizontal, as it searches horizontally across the first row.

In =HLOOKUP("Q2", A1:E4, 3, FALSE), what does "3" represent?

Third column
Third row from the top of the table
Third value found
Third character of the lookup value

✅ Answer: (b) — The 3rd argument in HLOOKUP is row_index_num, which specifies the row number from which to return the value.

Which function can replace both VLOOKUP and HLOOKUP in modern Excel?

INDEX
MATCH
XLOOKUP
LOOKUP

✅ Answer: (c) — XLOOKUP (Excel 365/2021) can search both vertically and horizontally, replacing both VLOOKUP and HLOOKUP.

When would you choose HLOOKUP over VLOOKUP?

When data is in a standard database table
When lookup values are arranged in a row across the top
When you need to look left
When you have more than 1 million rows

✅ Answer: (b) — Use HLOOKUP when your data is arranged horizontally (categories as column headers, values in rows below).

Mini Project

🚀 Semester Performance Tracker

📊 Project: B.Tech Semester Performance Tracker

Problem Statement

Build a tracker for 10 B.Tech students of VIT/SRM/Manipal where semester-wise SGPA is stored horizontally (Sem 1 through Sem 8 as column headers). A user selects a student name and semester to view the SGPA.

Requirements

Data Layout: Row 1 has semester headers (Sem 1 to Sem 8). Column A has student names. SGPA values (1.0 to 10.0) fill the grid.
Lookup Panel: User enters Semester in one cell → HLOOKUP retrieves the row. User enters Student Name → used to pick the correct row_index_num via a helper MATCH formula.
Display: SGPA for selected student + semester, plus their CGPA (average of all semesters), trend (improving/declining), and highest semester.
Use conditional formatting: SGPA ≥ 9 = Green, 7-8.99 = Yellow, Below 7 = Red.

Deliverables

Excel file with Data and Dashboard sheets
Dynamic semester selection using Data Validation dropdown
Automatic CGPA calculation and performance classification

Interview Q1: When would you use HLOOKUP in a real project?

Answer: HLOOKUP is useful when data is naturally horizontal — budget templates with months as columns, comparison tables with products across the top, or legacy financial reports. However, in modern Excel (365/2021), XLOOKUP replaces HLOOKUP entirely. I'd typically transpose horizontal data to vertical and use VLOOKUP or INDEX-MATCH for better maintainability.

Interview Q2: Can HLOOKUP and VLOOKUP be combined?

Answer: Yes! For a true two-dimensional lookup. Example: =INDEX(B2:E4, MATCH(G1,A2:A4,0), MATCH(H1,B1:E1,0)). But you can also nest: =VLOOKUP(G1, A1:E4, MATCH(H1,A1:E1,0), FALSE) where MATCH dynamically determines the column. This is a common pattern for cross-referencing row and column headers.

Interview Q3: Is HLOOKUP being deprecated?

Answer: Not officially deprecated, but Microsoft recommends XLOOKUP for new work. HLOOKUP will remain for backward compatibility, but XLOOKUP is more versatile — it handles horizontal lookups naturally without a separate function. Companies migrating to Excel 365 should train employees on XLOOKUP as the standard lookup function.

📋 Chapter 14 Summary

HLOOKUP searches the first row and returns a value from a specified row below
Use when data is arranged horizontally (months, quarters, semesters as columns)
Same TRUE/FALSE logic as VLOOKUP for exact vs approximate match
HLOOKUP is rarely used — most data is vertical; prefer XLOOKUP in modern Excel
Can be combined with MATCH for dynamic row selection

Chapter 15

XLOOKUP — The Modern Lookup

🚀 The Function That Replaced VLOOKUP

In August 2019, Microsoft introduced XLOOKUP — the function that makes VLOOKUP, HLOOKUP, and even many INDEX-MATCH combinations obsolete. It can look left, look right, return multiple columns, handle missing values gracefully, and search in reverse. Think of it as VLOOKUP 2.0. At companies like Infosys and Wipro, analysts who know XLOOKUP are already more productive than those still using VLOOKUP.

Excel 365Excel 2021InfosysWipro

Learning Objectives

What You'll Learn

Master the XLOOKUP syntax with all 6 arguments
Use XLOOKUP to look in any direction (left, right, horizontal, vertical)
Set custom "not found" messages without IFERROR
Understand all 4 match modes and 4 search modes
Return multiple columns with a single XLOOKUP
Perform nested XLOOKUP for two-dimensional lookups
Compare XLOOKUP vs VLOOKUP comprehensively

Theory & Concepts

XLOOKUP Syntax

📐 XLOOKUP Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

lookup_value — The value to search for.
lookup_array — The range to search IN (single row or column).
return_array — The range to return FROM (can be multiple columns/rows!).
if_not_found — Optional. Custom text/value to return if no match found (replaces IFERROR).
match_mode — Optional. 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard.
search_mode — Optional. 1 = first to last (default), -1 = last to first, 2 = binary ascending, -2 = binary descending.

Match Modes Explained

Match Mode	Value	Description	Use Case
Exact Match	0	Finds only exact match (default)	ID lookups, code lookups
Exact or Next Smaller	-1	If no exact match, returns next smaller	Tax slabs, grade boundaries
Exact or Next Larger	1	If no exact match, returns next larger	Shipping weight brackets
Wildcard	2	Allows * and ? wildcards	Partial name matching

Search Modes Explained

Search Mode	Value	Description	Use Case
First to Last	1	Search from top/left (default)	Standard lookups
Last to First	-1	Search from bottom/right	Find most recent entry
Binary Ascending	2	Binary search on sorted ascending data	Fast lookup on sorted data
Binary Descending	-2	Binary search on sorted descending data	Reverse-sorted tables

XLOOKUP vs VLOOKUP — Comprehensive Comparison

Feature	VLOOKUP	XLOOKUP
Look direction	Right only	Any direction
Not-found handling	Returns #N/A (needs IFERROR)	Built-in if_not_found argument
Column index	Hard-coded number	Separate return_array (no index needed)
Column insert impact	Breaks formula	Unaffected
Return multiple columns	No (one at a time)	Yes — returns entire row/range
Default match	Approximate (dangerous!)	Exact (safe default)
Reverse search	No	Yes (search_mode = -1)
Horizontal lookup	No (need HLOOKUP)	Yes — same function
Availability	All Excel versions	Excel 365, Excel 2021+

[Screenshot: Side-by-side comparison of VLOOKUP and XLOOKUP formulas for the same task, showing XLOOKUP's cleaner syntax]

Basic XLOOKUP Examples

Employee Database (same as Chapter 13):

Emp ID (A)	Name (B)	Department (C)	Salary ₹ (D)	City (E)
E1001	Amit Kumar	IT	85000	Bengaluru
E1002	Neha Reddy	HR	72000	Hyderabad
E1003	Raj Malhotra	Finance	95000	Mumbai
E1004	Kavita Nair	IT	88000	Chennai
E1005	Suresh Joshi	Marketing	68000	Pune

Example 1: Basic exact match

Look up employee name by ID

=XLOOKUP("E1003", A2:A6, B2:B6)

Result: Raj Malhotra — Searches for "E1003" in A2:A6, returns corresponding value from B2:B6. No column index number needed!

Example 2: Built-in error handling

Custom not-found message

=XLOOKUP("E9999", A2:A6, B2:B6, "Employee Not Found")

Result: Employee Not Found — No IFERROR wrapper needed! The 4th argument handles missing values directly.

Example 3: Looking LEFT (impossible with VLOOKUP!)

Find Emp ID by Name (leftward lookup)

=XLOOKUP("Kavita Nair", B2:B6, A2:A6)

Result: E1004 — Searches in column B (Name) and returns from column A (ID). VLOOKUP can't do this because it can only look right!

Example 4: Return multiple columns

Return Name, Department, and City in one formula

=XLOOKUP("E1002", A2:A6, B2:E6)

Result: Neha Reddy | HR | 72000 | Hyderabad — Returns the entire row! The result spills across 4 cells (B, C, D, E columns). This replaces 4 separate VLOOKUP formulas.

Example 5: Reverse search (find last occurrence)

Transaction log with duplicate customer IDs:

Cust ID	Date	Amount ₹
C001	01-Jan-2025	5000
C002	15-Jan-2025	3200
C001	20-Feb-2025	7500
C003	10-Mar-2025	4100
C001	05-Apr-2025	9200

Find the MOST RECENT transaction for C001

=XLOOKUP("C001", A2:A6, C2:C6, , 0, -1)

Result: 9200 — The search_mode = -1 searches from last to first, so it finds the latest entry for C001 (April). VLOOKUP would always return the first (5000).

XLOOKUP for Tax Slab Lookup

Income Tax with match_mode = -1 (exact or next smaller)

=XLOOKUP(850000, A2:A7, B2:B7, , -1)

Result: 10 — Using the tax slab table from Chapter 13. Match mode -1 finds the largest value ≤ 850000, which is 700001 → 10%. No need to worry about TRUE/FALSE or sorted data warnings.

XLOOKUP for Horizontal Lookups

XLOOKUP replacing HLOOKUP — Maths score in March

=XLOOKUP("Mar", B1:G1, B2:G2)

Result: 85 — Searches "Mar" in the header row B1:G1, returns from the Maths row B2:G2. Same function handles both V and H lookups!

Nested XLOOKUP — Two-Dimensional Lookup

Look up a specific student's specific subject marks

=XLOOKUP(H1, A2:A6, XLOOKUP(I1, B1:E1, B2:E6))

If H1 = "Rohan Gupta" and I1 = "Science": The inner XLOOKUP finds the "Science" column from the headers, returning the entire column. The outer XLOOKUP then finds "Rohan Gupta" in that column. Result: 69

Wildcard Search with XLOOKUP

=XLOOKUP("*Kumar", B2:B6, D2:D6, "Not Found", 2)     → 85000 (wildcard match_mode = 2)
=XLOOKUP("Ne??", B2:B6, C2:C6, "Not Found", 2)        → HR (? matches single char)
=XLOOKUP("*al*", B2:B6, A2:A6, "Not Found", 2)        → E1003 (Raj Malhotra contains "al")

More Solved Examples

GST Rate Lookup with XLOOKUP

=XLOOKUP("8471", A2:A7, C2:C7, "HSN Not Found")         → 18
=XLOOKUP("0401", A2:A7, B2:B7, "HSN Not Found")         → Milk & Cream
=XLOOKUP("9999", A2:A7, C2:C7, "Invalid HSN Code")      → Invalid HSN Code

Reliance Industries — Segment Revenue Lookup

Segment	Revenue ₹Cr	Growth %	Employees
Jio Platforms	112446	12.5	45000
Retail	265750	18.3	350000
O2C (Oil)	537840	5.2	35000
Media	8762	-3.1	12000
Financial Services	18950	28.6	8500

=XLOOKUP("Retail", A2:A6, B2:B6)                        → 265750
=XLOOKUP("Jio Platforms", A2:A6, C2:C6)                  → 12.5
=XLOOKUP("Media", A2:A6, B2:D6)                          → 8762 | -3.1 | 12000 (multi-column return)
=XLOOKUP(MAX(C2:C6), C2:C6, A2:A6)                      → Financial Services (highest growth segment)
=XLOOKUP(MIN(C2:C6), C2:C6, A2:A6)                      → Media (segment with negative growth)

XLOOKUP returns multiple columns: When return_array spans multiple columns, the result spills across adjacent cells. This is incredibly powerful — one formula replaces many. Just make sure the spill area is empty, or you'll get a #SPILL! error.

#SPILL! Error: When XLOOKUP tries to return multiple values but the adjacent cells are not empty, you get #SPILL!. Solution: Clear the cells where the results need to spill into, or use @ operator to force single-cell return: =@XLOOKUP(...)

Practice Exercises

Exercise 1: Use XLOOKUP to find the city of employee E1005. Answer: =XLOOKUP("E1005",A2:A6,E2:E6) → Pune

Exercise 2: Find the employee ID of someone named "Neha Reddy" (leftward lookup). Answer: =XLOOKUP("Neha Reddy",B2:B6,A2:A6)

Exercise 3: Look up salary for E1001 with a custom "ID Invalid" message. Answer: =XLOOKUP("E1001",A2:A6,D2:D6,"ID Invalid")

Exercise 4: Return all details (Name, Dept, Salary, City) for E1004 in one formula. Answer: =XLOOKUP("E1004",A2:A6,B2:E6)

Exercise 5: Use wildcard to find any employee with "Joshi" in their name. Answer: =XLOOKUP("*Joshi*",B2:B6,A2:A6,"Not Found",2)

Exercise 6: Find the income tax rate for ₹12,50,000 using match_mode -1. Answer: =XLOOKUP(1250000,A2:A7,B2:B7,,-1) → 20

Exercise 7: Look up the last transaction amount for C001 using reverse search. Answer: =XLOOKUP("C001",A2:A6,C2:C6,,0,-1) → 9200

Exercise 8: Find the Reliance segment with the most employees. Answer: =XLOOKUP(MAX(D2:D6),D2:D6,A2:A6) → Retail

Exercise 9: Perform a nested XLOOKUP to find Rohan Gupta's English marks from the student database. Answer: =XLOOKUP("Rohan Gupta",B2:B6,XLOOKUP("English",A1:E1,A2:E6))

Exercise 10: Use XLOOKUP to find delivery days for 750 km distance (next larger match). Answer: =XLOOKUP(750,A1:F1,A2:F2,,1)

Ctrl + Shift + Enter — Enter array formula (not needed for XLOOKUP in Excel 365, but needed in some contexts)

Ctrl + / — Select the current array range (useful for spill ranges)

F9 — Evaluate part of a formula in the formula bar (select a portion and press F9)

MCQ Assessment

XLOOKUP Quiz (5 MCQs)

What is the default match_mode of XLOOKUP?

Approximate match
Exact match
Wildcard match
Binary search

✅ Answer: (b) — XLOOKUP defaults to exact match (0), unlike VLOOKUP which defaults to approximate match (TRUE). This is a much safer default.

Which XLOOKUP feature eliminates the need for IFERROR?

match_mode argument
search_mode argument
if_not_found argument
return_array argument

✅ Answer: (c) — The if_not_found (4th argument) lets you specify a custom value when no match is found, eliminating the need to wrap in IFERROR.

To find the LAST occurrence of a value, which search_mode should you use?

1
-1
2
0

✅ Answer: (b) — search_mode = -1 searches from last to first, finding the last occurrence of the lookup value.

What advantage does XLOOKUP have over VLOOKUP for column references?

It uses a separate return_array instead of a column index number
It automatically finds the column
It uses column letters instead of numbers
It doesn't need a table reference

✅ Answer: (a) — XLOOKUP uses a return_array (e.g., C2:C6) instead of a column index (e.g., 3). This means inserting/deleting columns won't break the formula.

XLOOKUP is available in which Excel versions?

All versions from Excel 2010
Excel 2016 and later
Excel 365 and Excel 2021 only
Only Excel 365 (not Excel 2021)

✅ Answer: (c) — XLOOKUP was introduced in Excel 365 (2019) and is also available in Excel 2021. It is NOT available in Excel 2019, 2016, or earlier.

Mini Project

🚀 Product Catalog Search Engine

📊 Project: Flipkart Product Catalog Search Engine with XLOOKUP

Problem Statement

Build a product search system for an e-commerce catalog with 50+ products. Users can search by Product ID, Product Name (partial match), or Category, and the system displays all product details instantly.

Requirements

Catalog Sheet: 50 products with columns: Product ID, Name, Category, Brand, MRP ₹, Selling Price ₹, Discount %, Rating, Stock, Seller City
Search by ID: =XLOOKUP(SearchID, ProductIDs, AllData, "Product Not Found") — returns all columns
Search by Name: =XLOOKUP("*"&SearchTerm&"*", Names, AllData, "No Match", 2) — wildcard search
Reverse Lookup: Given a price, find which product matches — =XLOOKUP(Price, Prices, Names, "No Match", -1)
Category Filter: Last product in a category using search_mode = -1
Calculate GST (18%) on selling price for electronics, 5% for clothing
Show savings (MRP - Selling Price) and savings percentage

Deliverables

Excel file with Catalog and Search Dashboard sheets
Three search modes: by ID, by name (partial), by category
Auto-calculated GST, savings, and total payable
Conditional formatting for out-of-stock items

Interview Q1: Why should new Excel users learn XLOOKUP instead of VLOOKUP?

Answer: XLOOKUP is superior in every way: (1) Looks in any direction, (2) Built-in error handling, (3) Returns multiple columns, (4) Safer default (exact match), (5) Not affected by column insertions, (6) Replaces both VLOOKUP and HLOOKUP. The only reason to learn VLOOKUP is backward compatibility — many companies still use Excel 2016/2019 which don't support XLOOKUP.

Interview Q2: Explain the difference between match_mode -1 and 1 in XLOOKUP.

Answer: match_mode -1 finds the "exact match or next smaller value" — used for tax slabs, grade boundaries, where you want the bracket floor. match_mode 1 finds the "exact match or next larger value" — used for shipping weight tiers or pricing tiers where you want to round up. Example: For weight 3.5 kg, if tiers are 1, 2, 5, 10 kg — mode -1 returns the 2 kg tier (₹50), mode 1 returns the 5 kg tier (₹80).

Interview Q3: How does XLOOKUP handle the #SPILL! error?

Answer: #SPILL! occurs when XLOOKUP returns multiple values but the target cells aren't empty. Solutions: (1) Clear the cells in the spill range, (2) Use @ operator to return only the first value: =@XLOOKUP(...), (3) Wrap in INDEX to extract a specific element. Understanding spill behavior is essential for working with dynamic arrays in Excel 365.

Teaching sequence: Teach VLOOKUP first (students will encounter it in existing workbooks), then XLOOKUP as "VLOOKUP done right." Run a side-by-side comparison exercise where students solve the same 10 problems with both functions and see the difference in formula complexity. The "look left" example is the biggest aha moment.

📋 Chapter 15 Summary

XLOOKUP replaces VLOOKUP, HLOOKUP, and many INDEX-MATCH combinations
Can look in any direction — left, right, horizontal, vertical
Built-in if_not_found eliminates the need for IFERROR
Returns multiple columns with a single formula (spill range)
Match modes: 0 (exact), -1 (next smaller), 1 (next larger), 2 (wildcard)
Search modes: 1 (first→last), -1 (last→first), 2 (binary asc), -2 (binary desc)
Available only in Excel 365 and Excel 2021
Nested XLOOKUP enables powerful two-dimensional lookups

Chapter 16

INDEX & MATCH — The Power Duo

🏆 The Formula Every Excel Expert Swears By

Ask any senior data analyst at TCS, Deloitte, or EY which lookup formula they prefer, and 8 out of 10 will say INDEX-MATCH. It's the Swiss Army knife of Excel lookups — it can look in any direction, handle multiple criteria, run faster than VLOOKUP on large datasets, and never breaks when you insert or delete columns. If VLOOKUP is a bicycle, INDEX-MATCH is a sports car.

TCSDeloitteEYKPMG

Learning Objectives

What You'll Learn

Master the INDEX function independently
Master the MATCH function independently
Combine INDEX + MATCH for flexible, powerful lookups
Understand why INDEX-MATCH is preferred over VLOOKUP in professional settings
Perform two-way lookups with INDEX-MATCH-MATCH
Use INDEX-MATCH with multiple criteria
Build a Dynamic Employee Directory

Theory & Concepts

The INDEX Function

INDEX returns a value from a specific position in a range. Think of it as "give me the value at row X, column Y of this table."

📐 INDEX Syntax

=INDEX(array, row_num, [col_num])

array — The range of cells to return from.
row_num — The row position in the array.
col_num — Optional. The column position (default is 1 for single-column arrays).

INDEX Examples (Standalone)

Using the Employee Database (A1:E6):

Emp ID (A)	Name (B)	Department (C)	Salary ₹ (D)	City (E)
E1001	Amit Kumar	IT	85000	Bengaluru
E1002	Neha Reddy	HR	72000	Hyderabad
E1003	Raj Malhotra	Finance	95000	Mumbai
E1004	Kavita Nair	IT	88000	Chennai
E1005	Suresh Joshi	Marketing	68000	Pune

=INDEX(B2:B6, 3)          → Raj Malhotra (3rd value in the Name column)
=INDEX(A2:E6, 2, 4)       → 72000 (row 2, column 4 of the table = Neha's salary)
=INDEX(A2:E6, 5, 5)       → Pune (row 5, column 5 = Suresh's city)
=INDEX(D2:D6, 1)          → 85000 (1st salary)

The MATCH Function

MATCH searches for a value in a range and returns its position (not the value itself). It answers "WHERE is this value?"

📐 MATCH Syntax

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value — The value to search for.
lookup_array — A single row or column to search in.
match_type — 0 = exact match, 1 = largest value ≤ lookup (sorted asc), -1 = smallest value ≥ lookup (sorted desc).

MATCH Examples (Standalone)

=MATCH("E1003", A2:A6, 0)        → 3 (E1003 is in the 3rd position of A2:A6)
=MATCH("Kavita Nair", B2:B6, 0)  → 4 (Kavita is in position 4)
=MATCH("IT", C2:C6, 0)           → 1 (first IT is in position 1)
=MATCH("Finance", C2:C6, 0)      → 3 (Finance is in position 3)

MATCH returns a POSITION, not a VALUE. MATCH("E1003", A2:A6, 0) returns 3, not "E1003" or "Raj Malhotra". It says "the value you're looking for is at position 3 in this range." This position number is then fed into INDEX.

INDEX + MATCH Combined — The Magic

Now combine them: MATCH finds the position, INDEX returns the value at that position.

📐 INDEX-MATCH Combined Pattern

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Step 1: MATCH finds the position of the lookup value in the lookup_range.
Step 2: INDEX returns the value at that position from the return_range.

Example 1: Look up employee name by ID (same as VLOOKUP)

=INDEX(B2:B6, MATCH("E1003", A2:A6, 0))
  → MATCH finds E1003 at position 3
  → INDEX returns the 3rd value from B2:B6
  → Result: Raj Malhotra

Example 2: LEFTWARD lookup (VLOOKUP can't do this!)

=INDEX(A2:A6, MATCH("Kavita Nair", B2:B6, 0))
  → MATCH finds "Kavita Nair" at position 4 in B2:B6
  → INDEX returns the 4th value from A2:A6
  → Result: E1004

Example 3: Look up salary by city (leftward)

=INDEX(D2:D6, MATCH("Mumbai", E2:E6, 0))
  → MATCH finds "Mumbai" at position 3 in E2:E6
  → INDEX returns 3rd value from D2:D6
  → Result: 95000

Why INDEX-MATCH is Better Than VLOOKUP

Advantage	Explanation
🔄 Look in any direction	Can look left, right, up, or down — VLOOKUP only looks right
🛡️ Insert-proof	Uses actual column references (B2:B6), not column index numbers. Inserting columns doesn't break it
⚡ Faster on large data	MATCH only searches the lookup column; VLOOKUP loads the entire table_array into memory
🔧 More flexible	Easy to add multiple criteria, look up max/min, or chain conditions
📊 Two-way lookup	INDEX-MATCH-MATCH naturally handles row + column lookups

Two-Way Lookup: INDEX-MATCH-MATCH

This is the ultimate lookup technique — find a value at the intersection of a specific row AND column.

Student Subject Marks (A1:E6):

Student	Maths	Science	English	Hindi
Aarav	85	92	78	88
Priya	91	88	95	82
Rohan	76	69	82	74
Sneha	94	97	91	90
Vikram	68	73	71	65

INDEX-MATCH-MATCH — Find Rohan's English marks

=INDEX(B2:E6, MATCH("Rohan", A2:A6, 0), MATCH("English", B1:E1, 0))

Step 1: MATCH("Rohan", A2:A6, 0) → 3 (Rohan is in row 3)
Step 2: MATCH("English", B1:E1, 0) → 3 (English is column 3)
Step 3: INDEX(B2:E6, 3, 3) → 82
Result: 82

/* More examples */
=INDEX(B2:E6, MATCH("Sneha",A2:A6,0), MATCH("Hindi",B1:E1,0))     → 90
=INDEX(B2:E6, MATCH("Aarav",A2:A6,0), MATCH("Maths",B1:E1,0))     → 85
=INDEX(B2:E6, MATCH("Priya",A2:A6,0), MATCH("Science",B1:E1,0))   → 88

INDEX-MATCH with Multiple Criteria

Sales Data with Multiple Criteria:

Salesperson	Region	Product	Sales ₹
Amit	North	Laptop	450000
Priya	South	Phone	320000
Amit	North	Phone	280000
Rohan	West	Laptop	510000
Priya	South	Laptop	390000
Amit	East	Tablet	180000

Find Amit's Phone sales in North region (multiple criteria)

=INDEX(D2:D7, MATCH(1, (A2:A7="Amit")*(B2:B7="North")*(C2:C7="Phone"), 0))

Result: 280000 — This is an array formula (Ctrl+Shift+Enter in older Excel). It multiplies three TRUE/FALSE arrays: each condition creates 1s and 0s. The only row where all three are 1 (TRUE) is row 3. MATCH finds position 3, INDEX returns 280000.

In Excel 365, this formula works without Ctrl+Shift+Enter. In older versions (2016, 2019), you MUST press Ctrl+Shift+Enter to make it an array formula. You'll see curly braces {} around the formula in the formula bar.

More Solved Examples

Indian Company Financials

Company	Revenue ₹Cr	Profit ₹Cr	Employees	Sector
TCS	240000	42500	607000	IT
Reliance	950000	73500	390000	Conglomerate
Infosys	180000	28900	335000	IT
HDFC Bank	285000	51000	195000	Banking
Wipro	110000	15200	250000	IT

/* Basic INDEX-MATCH lookups */
=INDEX(B2:B6, MATCH("Infosys",A2:A6,0))                → 180000 (Revenue)
=INDEX(E2:E6, MATCH("HDFC Bank",A2:A6,0))              → Banking (Sector)

/* Leftward lookup: Find company name by employee count */
=INDEX(A2:A6, MATCH(607000,D2:D6,0))                   → TCS

/* Find the most profitable company */
=INDEX(A2:A6, MATCH(MAX(C2:C6),C2:C6,0))               → Reliance

/* Find the IT company with highest revenue */
=INDEX(A2:A6, MATCH(1,(E2:E6="IT")*(B2:B6=MAX(IF(E2:E6="IT",B2:B6))),0))  → TCS

/* Profit margin calculation */
=INDEX(C2:C6,MATCH("TCS",A2:A6,0))/INDEX(B2:B6,MATCH("TCS",A2:A6,0))*100  → 17.7%

/* Revenue per employee */
=INDEX(B2:B6,MATCH("Wipro",A2:A6,0))*10000000/INDEX(D2:D6,MATCH("Wipro",A2:A6,0))  → ₹44 lakh per employee

Practice Exercises

Exercise 1: Use INDEX-MATCH to find the salary of employee E1004. Answer: =INDEX(D2:D6,MATCH("E1004",A2:A6,0)) → 88000

Exercise 2: Leftward lookup: Find the Emp ID of the person in Mumbai. Answer: =INDEX(A2:A6,MATCH("Mumbai",E2:E6,0)) → E1003

Exercise 3: Two-way lookup: Find Sneha's Hindi marks. Answer: =INDEX(B2:E6,MATCH("Sneha",A2:A6,0),MATCH("Hindi",B1:E1,0)) → 90

Exercise 4: Find the employee with the highest salary. Answer: =INDEX(B2:B6,MATCH(MAX(D2:D6),D2:D6,0))

Exercise 5: Multi-criteria: Find Priya's Laptop sales in South region. Answer: =INDEX(D2:D7,MATCH(1,(A2:A7="Priya")*(B2:B7="South")*(C2:C7="Laptop"),0))

Exercise 6: Find the company with the most employees. Answer: =INDEX(A2:A6,MATCH(MAX(D2:D6),D2:D6,0)) → TCS

Exercise 7: Find the position of "Finance" in the Department column. Answer: =MATCH("Finance",C2:C6,0) → 3

Exercise 8: Use INDEX to get the 4th employee's name and city. Answer: =INDEX(B2:B6,4) and =INDEX(E2:E6,4)

Exercise 9: Find which sector has a company with revenue > ₹5,00,000 Cr. Answer: =INDEX(E2:E6,MATCH(1,(B2:B6>500000)*1,0))

Exercise 10: Calculate the average salary of IT department employees using INDEX-MATCH. Answer: =AVERAGEIF(C2:C6,"IT",D2:D6) or use INDEX-MATCH to first identify IT employees

Assignment

📋 Assignment: Indian Cricket Player Stats Lookup

Create a workbook with 20 Indian cricket players (Virat, Rohit, Bumrah, etc.) and columns: Player Name, Role (Batsman/Bowler/All-rounder), Matches, Runs, Wickets, Average, Strike Rate, IPL Team. Use INDEX-MATCH for:

Look up any stat by player name
Find the player with the highest runs (INDEX + MATCH + MAX)
Two-way lookup: select player + stat to display
Multi-criteria: Find the all-rounder with the most wickets

Ctrl + Shift + Enter — Enter array formula (needed for multi-criteria INDEX-MATCH in Excel 2016/2019)

F2 — Edit cell and see color-coded range references

Ctrl + [ — Navigate to precedent cells (trace which cells feed into formula)

Ctrl + ] — Navigate to dependent cells (trace which cells use this cell)

MCQ Assessment

INDEX-MATCH Quiz (5 MCQs)

What does the MATCH function return?

The value that matches
The position (row/column number) of the match
TRUE if the value is found
The cell address of the match

✅ Answer: (b) — MATCH returns the relative position (1, 2, 3...) of the lookup value in the array, not the value itself.

In =INDEX(C2:C10, 5), what is returned?

The 5th cell of column C (C5)
The 5th value within the range C2:C10 (which is C6)
The value 5
The last 5 values

✅ Answer: (b) — INDEX counts from the start of the specified range. The 5th value in C2:C10 is C6 (C2=1st, C3=2nd, C4=3rd, C5=4th, C6=5th).

What is the main advantage of INDEX-MATCH over VLOOKUP?

It's easier to learn
It can look in any direction, not just right
It works in all versions of Excel
It automatically sorts data

✅ Answer: (b) — INDEX-MATCH can look left, right, up, or down. VLOOKUP can only return values from columns to the right of the lookup column.

In INDEX-MATCH-MATCH for a two-way lookup, how many MATCH functions are used?

One
Two
Three
Depends on the data

✅ Answer: (b) — INDEX-MATCH-MATCH uses two MATCH functions: one to find the row position and one to find the column position. INDEX then returns the value at the intersection.

What is the match_type 0 in MATCH(lookup_value, lookup_array, 0)?

Approximate match (ascending)
Approximate match (descending)
Exact match
Wildcard match

✅ Answer: (c) — match_type 0 means exact match. 1 means largest value ≤ lookup (ascending data), -1 means smallest value ≥ lookup (descending data).

Mini Project

🚀 Dynamic Employee Directory

📊 Project: Dynamic Employee Directory — Multi-Search System

Problem Statement

Build a comprehensive employee directory for a company with 50+ employees. Users can search by Employee ID, Name, or Department, and the system displays the complete employee profile.

Requirements

Employee Database: 50 employees with: Emp ID, Name, Department, Designation, Salary ₹, City, Join Date, Phone, Email, Manager Name
Search by ID: =INDEX(NameCol, MATCH(SearchID, IDCol, 0))
Search by Name: =INDEX(IDCol, MATCH(SearchName, NameCol, 0)) (leftward lookup)
Search by Department: Show all employees in a department using multiple INDEX-MATCH calls or FILTER
Two-way lookup: Select Employee Name (dropdown) and Field Name (dropdown) → display the value
Multi-criteria search: Find employees in IT Department AND Bengaluru city
Analytics panel: Highest salary employee, lowest salary, average salary by department

Deliverables

Excel file with Database, Search Dashboard, and Analytics sheets
Three search modes working with dropdowns
Department-wise summary with COUNTIF and AVERAGEIF
Formatted employee profile card layout

[Screenshot: Dynamic Employee Directory showing search panel with dropdown and auto-populated employee details]

Q1: In an interview, how would you explain INDEX-MATCH to a non-technical person?

Answer: "Imagine a library. MATCH is like looking at the catalog and finding that the book you want is on Shelf 7. INDEX is like going to Shelf 7 and picking up the book. MATCH finds the location, INDEX retrieves the item. Together, they form a complete lookup system — you can search any row or column and retrieve data from anywhere in the table."

Q2: When would you use INDEX-MATCH over XLOOKUP?

Answer: (1) When working with Excel 2016/2019 which don't support XLOOKUP. (2) For multi-criteria lookups using array formulas — INDEX-MATCH handles this more naturally. (3) When you need to use MATCH independently for dynamic column references. (4) When building formulas that need backward compatibility across different Excel versions in the organization. In Excel 365, XLOOKUP is simpler for basic lookups, but INDEX-MATCH remains relevant for complex scenarios.

Q3: How does INDEX-MATCH handle multiple criteria lookups?

Answer: Use array multiplication inside MATCH: =INDEX(ReturnRange, MATCH(1, (Criteria1Range=Value1)*(Criteria2Range=Value2), 0)). Each condition creates a TRUE/FALSE (1/0) array. Multiplying them creates 1 only where ALL conditions are TRUE. MATCH(1, ..., 0) finds the position of that 1. In older Excel versions, this requires Ctrl+Shift+Enter. In Excel 365, it works as a regular formula.

Q4: Why is INDEX-MATCH faster than VLOOKUP on large datasets?

Answer: VLOOKUP loads the entire table_array into memory for each lookup. If the table is A:Z (26 columns × 1M rows), all 26M cells are loaded. INDEX-MATCH only loads two ranges — the lookup range (1 column × 1M rows) and the return range (1 column × 1M rows). This means INDEX-MATCH processes roughly 1/13th the data, making it significantly faster for wide tables.

Q5: Can you explain INDEX-MATCH-MATCH?

Answer: INDEX-MATCH-MATCH is used for two-dimensional lookups where you need to find a value at the intersection of a specific row and column. The formula is: =INDEX(DataRange, MATCH(RowValue, RowHeaders, 0), MATCH(ColValue, ColHeaders, 0)). The first MATCH finds the row number, the second MATCH finds the column number, and INDEX returns the value at that intersection. This is ideal for matrices like student marks across subjects, or sales data across regions and months.

Teaching approach: Teach INDEX and MATCH separately first. Spend 15 minutes on INDEX alone — have students extract values by position. Then 15 minutes on MATCH — have them find positions. Then combine them and watch the "aha" moment. The two-step mental model (find position → get value) is key. Use the library analogy: MATCH = catalog, INDEX = retrieval.

📋 Chapter 16 Summary

INDEX returns a value from a specific position in a range
MATCH returns the position of a value in a range
Combined: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Can look in any direction — left, right, up, down
Insert-proof — not affected by column/row insertions
Faster than VLOOKUP on large datasets
INDEX-MATCH-MATCH enables two-way lookups
Multiple criteria supported via array multiplication inside MATCH
The preferred lookup method of professional Excel analysts worldwide

Chapter 17

Dynamic Arrays & Spill Functions

⚡ The Biggest Change to Excel in 30 Years

In 2018, Microsoft completely changed how Excel formulas work. Before dynamic arrays, one cell = one value. Now, a single formula can return dozens or hundreds of values that automatically "spill" into adjacent cells. Functions like FILTER, SORT, UNIQUE, SEQUENCE, and SORTBY have made tasks that previously required Pivot Tables, VBA, or complex array formulas trivially simple. At companies like Zomato and Razorpay, data analysts use these functions daily to build live dashboards without pivot tables.

Excel 365ZomatoRazorpaySwiggy

Learning Objectives

What You'll Learn

Understand the dynamic array engine and spill range concept
Use the spill operator # to reference dynamic results
Master 8 dynamic array functions: UNIQUE, SORT, SORTBY, FILTER, SEQUENCE, RANDARRAY, HSTACK, VSTACK
Combine dynamic array functions for powerful one-formula solutions
Build a dynamic dashboard without Pivot Tables

Theory & Concepts

What Are Dynamic Arrays?

Before Excel 365, if a formula returned multiple values, you had to manually enter it in every cell and press Ctrl+Shift+Enter (CSE). With dynamic arrays, you enter a formula in ONE cell, and the results automatically spill into adjacent cells. The blue border around the spill range shows which cells are controlled by the formula.

[Screenshot: A UNIQUE formula in cell G2 that spills results into G3, G4, G5 with the blue spill border visible]

The Spill Operator (#)

The # (spill reference operator) refers to the entire spill range of a dynamic array formula. If cell G2 contains a UNIQUE formula that spills into G2:G5, then G2# refers to the entire range G2:G5.

=UNIQUE(A2:A10)        → Spills unique values starting from the formula cell
=COUNTA(G2#)           → Counts how many values are in the spill range
=SUM(G2#)              → Sums all values in the spill range

#SPILL! Error: This occurs when cells in the spill range are not empty. Dynamic array results need blank cells to spill into. Solution: Clear all cells below/to the right of your formula that might block the spill.

UNIQUE — Extract Unique Values

📐 UNIQUE Syntax

=UNIQUE(array, [by_col], [exactly_once])

array — The range to extract unique values from.
by_col — Optional. FALSE (default) = compare rows. TRUE = compare columns.
exactly_once — Optional. FALSE (default) = all distinct values. TRUE = only values appearing exactly once.

Zomato Orders Data:

Order ID	Customer	Restaurant	City	Amount ₹
Z001	Aarav	Domino's	Mumbai	450
Z002	Priya	McDonald's	Delhi	380
Z003	Aarav	KFC	Mumbai	520
Z004	Rohan	Domino's	Bengaluru	350
Z005	Priya	Subway	Delhi	290
Z006	Sneha	Pizza Hut	Chennai	610
Z007	Aarav	Burger King	Mumbai	420
Z008	Vikram	Domino's	Hyderabad	380

=UNIQUE(B2:B9)                      → Aarav, Priya, Rohan, Sneha, Vikram (unique customers)
=UNIQUE(D2:D9)                      → Mumbai, Delhi, Bengaluru, Chennai, Hyderabad (unique cities)
=UNIQUE(C2:C9)                      → Domino's, McDonald's, KFC, Subway, Pizza Hut, Burger King
=UNIQUE(B2:B9, , TRUE)              → Rohan, Sneha, Vikram (customers who ordered only ONCE)
=COUNTA(UNIQUE(B2:B9))              → 5 (count of unique customers)

SORT — Sort Results

📐 SORT Syntax

=SORT(array, [sort_index], [sort_order], [by_col])

array — The range to sort.
sort_index — Optional. Column number to sort by (default = 1).
sort_order — Optional. 1 = ascending (default), -1 = descending.
by_col — Optional. FALSE (default) = sort rows. TRUE = sort columns.

=SORT(A2:E9)                        → Sorts entire table by first column (Order ID ascending)
=SORT(A2:E9, 5, -1)                 → Sorts by Amount (col 5) descending — highest order first
=SORT(A2:E9, 4, 1)                  → Sorts by City (col 4) alphabetically
=SORT(UNIQUE(D2:D9))                → Sorted unique cities: Bengaluru, Chennai, Delhi, Hyderabad, Mumbai
=SORT(E2:E9, , -1)                  → Just the amounts sorted: 610, 520, 450, 420, 380, 380, 350, 290

SORTBY — Sort by Another Column

📐 SORTBY Syntax

=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)

array — The range to sort (what you see).
by_array — The range to sort BY (the sorting key).
sort_order — 1 = ascending, -1 = descending. Can specify multiple sort levels.

=SORTBY(B2:B9, E2:E9, -1)           → Customer names sorted by amount (highest spender first): Sneha, Aarav, Aarav...
=SORTBY(A2:E9, D2:D9, 1, E2:E9, -1) → Sort by City ascending, then by Amount descending within each city
=SORTBY(C2:C9, E2:E9, -1)           → Restaurants sorted by order amount: Pizza Hut, KFC, Domino's...

FILTER — The Most Powerful Dynamic Array Function

📐 FILTER Syntax

=FILTER(array, include, [if_empty])

array — The range to filter.
include — A Boolean array (TRUE/FALSE) that defines which rows to include.
if_empty — Optional. Value to return if no rows match the filter.

Example 1: Filter orders from Mumbai

=FILTER(A2:E9, D2:D9="Mumbai")
→ Returns:
  Z001 | Aarav | Domino's   | Mumbai | 450
  Z003 | Aarav | KFC        | Mumbai | 520
  Z007 | Aarav | Burger King | Mumbai | 420

Example 2: Filter orders above ₹400

=FILTER(A2:E9, E2:E9>400)
→ Returns all rows where Amount > 400 (Z001, Z003, Z006, Z007)

Example 3: Multiple criteria (AND — multiply conditions)

=FILTER(A2:E9, (D2:D9="Mumbai")*(E2:E9>400))
→ Mumbai orders above ₹400: Z001 (450), Z003 (520), Z007 (420)

Example 4: Multiple criteria (OR — add conditions)

=FILTER(A2:E9, (D2:D9="Mumbai")+(D2:D9="Delhi"))
→ All orders from Mumbai OR Delhi

Example 5: With custom empty message

=FILTER(A2:E9, D2:D9="Jaipur", "No orders from this city")
→ "No orders from this city" (no Jaipur orders exist)

FILTER is a game-changer: Before FILTER, extracting rows that meet a condition required Advanced Filter, helper columns, or complex array formulas. Now it's one simple formula. Combine it with SORT and UNIQUE for incredibly powerful one-formula dashboards.

SEQUENCE — Generate Number Sequences

📐 SEQUENCE Syntax

=SEQUENCE(rows, [cols], [start], [step])

rows — Number of rows to fill.
cols — Optional. Number of columns (default = 1).
start — Optional. Starting number (default = 1).
step — Optional. Increment (default = 1).

=SEQUENCE(5)              → 1, 2, 3, 4, 5 (vertical)
=SEQUENCE(3, 4)           → 3×4 grid: 1-12
=SEQUENCE(5, 1, 100, 10)  → 100, 110, 120, 130, 140
=SEQUENCE(5, 1, 10, -2)   → 10, 8, 6, 4, 2 (countdown)
=SEQUENCE(12, 1, DATE(2025,1,1), 30)  → Dates: Jan 1, Jan 31, Mar 2... (every 30 days)

/* Generate roll numbers */
=SEQUENCE(50, 1, 2001, 1)  → 2001, 2002, 2003... 2050

/* Multiplication table for 7 */
=SEQUENCE(10, 1, 7, 7)    → 7, 14, 21, 28, 35, 42, 49, 56, 63, 70

RANDARRAY — Generate Random Data

📐 RANDARRAY Syntax

=RANDARRAY([rows], [cols], [min], [max], [integer])

rows — Number of rows (default = 1).
cols — Number of columns (default = 1).
min — Minimum value (default = 0).
max — Maximum value (default = 1).
integer — TRUE for whole numbers, FALSE for decimals (default).

=RANDARRAY(5, 1, 1, 100, TRUE)         → 5 random integers between 1-100
=RANDARRAY(10, 3, 30, 100, TRUE)        → 10 students × 3 subjects, marks 30-100
=RANDARRAY(5, 1, 50000, 150000, TRUE)   → 5 random salaries ₹50K-₹1.5L
=RANDARRAY(1, 6, 1, 49, TRUE)           → 6 random lottery numbers (1-49)
=RANDARRAY(20, 1, 1, 5, TRUE)           → 20 random ratings (1-5 stars)

RANDARRAY is perfect for creating sample datasets for practice. Need 100 student records with random marks? Use =RANDARRAY(100,5,0,100,TRUE) to instantly generate a 100×5 grid of marks between 0 and 100!

HSTACK and VSTACK — Combine Arrays

📐 HSTACK (Horizontal Stack) — combine side by side

=HSTACK(array1, array2, ...)

Combines multiple arrays horizontally (adds columns). All arrays must have the same number of rows.

📐 VSTACK (Vertical Stack) — combine top to bottom

=VSTACK(array1, array2, ...)

Combines multiple arrays vertically (adds rows). All arrays must have the same number of columns.

/* Combine names and salaries side by side */
=HSTACK(B2:B6, D2:D6)              → Two-column result: Name | Salary

/* Stack two city lists on top of each other */
=VSTACK(Sheet1!A2:A10, Sheet2!A2:A15)  → Combined list from two sheets

/* Create a numbered list */
=HSTACK(SEQUENCE(5), B2:B6)         → Row numbers with names: 1|Amit, 2|Neha...

/* Combine unique cities with their counts */
=HSTACK(UNIQUE(D2:D9), COUNTIF(D2:D9, UNIQUE(D2:D9)))
→ Mumbai|3, Delhi|2, Bengaluru|1, Chennai|1, Hyderabad|1

Combining Dynamic Array Functions

The true power comes from nesting these functions together:

Combo 1: SORT + UNIQUE — Sorted unique list

=SORT(UNIQUE(D2:D9))               → Bengaluru, Chennai, Delhi, Hyderabad, Mumbai

Combo 2: FILTER + SORT — Filtered and sorted results

=SORT(FILTER(A2:E9, E2:E9>400), 5, -1)
→ Orders above ₹400, sorted by amount descending

Combo 3: UNIQUE + COUNTIF — Frequency table

=HSTACK(SORT(UNIQUE(D2:D9)), COUNTIF(D2:D9, SORT(UNIQUE(D2:D9))))
→ City | Count — a complete frequency table in ONE formula!

Combo 4: FILTER + SORT + UNIQUE — Dynamic dropdown source

=SORT(UNIQUE(FILTER(C2:C9, D2:D9="Mumbai")))
→ Sorted unique restaurants in Mumbai: Burger King, Domino's, KFC

Combo 5: Complete Summary in One Formula

=HSTACK(
    SORT(UNIQUE(D2:D9)),
    COUNTIF(D2:D9, SORT(UNIQUE(D2:D9))),
    SUMIF(D2:D9, SORT(UNIQUE(D2:D9)), E2:E9)
)
→ City | Order Count | Total Revenue — complete city-wise summary!

Dynamic arrays only work in Excel 365 and Excel 2021. If you share a workbook with dynamic array formulas to someone using Excel 2019 or earlier, the formulas will show #NAME? errors. Always check compatibility before sharing.

Indian Context Examples

GST Summary by Rate

Product	Category	Price ₹	GST %
Milk	Essential	56	0
Rice	Essential	120	5
Laptop	Electronics	65000	18
Phone	Electronics	25000	18
Car	Automobile	800000	28
AC	Appliance	45000	28
Bread	Essential	40	0
Shirt	Clothing	1200	5

/* All products with 18% GST */
=FILTER(A2:D9, D2:D9=18)
→ Laptop | Electronics | 65000 | 18
  Phone  | Electronics | 25000 | 18

/* Products under ₹500, sorted by price */
=SORT(FILTER(A2:D9, C2:C9<500), 3, 1)
→ Bread|40|0, Milk|56|0, Rice|120|5

/* Unique GST rates, sorted */
=SORT(UNIQUE(D2:D9))    → 0, 5, 18, 28

/* Category-wise product count */
=HSTACK(SORT(UNIQUE(B2:B9)), COUNTIF(B2:B9, SORT(UNIQUE(B2:B9))))
→ Appliance|1, Automobile|1, Clothing|1, Electronics|2, Essential|3

/* Total GST amount for electronics */
=SUMPRODUCT(FILTER(C2:C9, B2:B9="Electronics") * FILTER(D2:D9, B2:B9="Electronics")/100)
→ 16200

Practice Exercises

Exercise 1: Extract unique restaurant names from the Zomato dataset. Answer: =UNIQUE(C2:C9)

Exercise 2: Sort the orders by amount in descending order. Answer: =SORT(A2:E9,5,-1)

Exercise 3: Filter orders from Delhi only. Answer: =FILTER(A2:E9,D2:D9="Delhi")

Exercise 4: Generate a sequence of 10 even numbers starting from 2. Answer: =SEQUENCE(10,1,2,2)

Exercise 5: Create random test scores (0-100) for 15 students across 4 subjects. Answer: =RANDARRAY(15,4,0,100,TRUE)

Exercise 6: Find orders where Amount > 400 AND City = "Mumbai". Answer: =FILTER(A2:E9,(D2:D9="Mumbai")*(E2:E9>400))

Exercise 7: Create a sorted list of unique cities with order counts using HSTACK. Answer: =HSTACK(SORT(UNIQUE(D2:D9)),COUNTIF(D2:D9,SORT(UNIQUE(D2:D9))))

Exercise 8: Sort customers by their name alphabetically using SORTBY. Answer: =SORTBY(A2:E9,B2:B9,1)

Exercise 9: Filter products with GST rate 0% from the GST dataset. Answer: =FILTER(A2:D9,D2:D9=0)

Exercise 10: Use VSTACK to combine two separate lists of products into one. Answer: =VSTACK(Sheet1!A2:A5,Sheet2!A2:A5)

Assignment

📋 Assignment: Automated Sales Report Generator

Create a sales dataset with 100 rows: Salesperson, Region (North/South/East/West), Product, Quantity, Unit Price, Total
Use RANDARRAY to generate realistic random data
Build these dynamic reports using ONLY formulas (no pivot tables):
- Region-wise total sales: =HSTACK(SORT(UNIQUE(RegionCol)), SUMIF(...))
- Top 5 salespeople by revenue: =SORT(FILTER(...),col,-1) + take first 5
- Product-wise unique count and average price
- Filtered view by region (dropdown selects region)

Ctrl + Shift + Enter — Enter legacy array formula (not needed in Excel 365)

Ctrl + T — Convert range to Table (great with dynamic arrays)

Ctrl + Space — Select entire column in a Table

Ctrl + Shift + L — Toggle AutoFilter

MCQ Assessment

Dynamic Arrays Quiz (5 MCQs)

What does the # (spill reference) operator do?

Converts text to numbers
References the entire spill range of a dynamic array formula
Adds a comment to the formula
Makes the formula absolute

✅ Answer: (b) — The # operator (e.g., G2#) references all cells in the spill range that starts from G2. If UNIQUE spills into G2:G7, then G2# = G2:G7.

Which function extracts rows that meet a condition, similar to AutoFilter but as a formula?

SORT
UNIQUE
FILTER
SEQUENCE

✅ Answer: (c) — FILTER extracts rows from an array that meet specified conditions, returning results dynamically. It's the formula equivalent of AutoFilter.

What will =SEQUENCE(5, 1, 10, 3) return?

10, 13, 16, 19, 22
1, 2, 3, 4, 5
10, 11, 12, 13, 14
3, 6, 9, 12, 15

✅ Answer: (a) — SEQUENCE(5, 1, 10, 3) generates 5 numbers, starting at 10, incrementing by 3: 10, 13, 16, 19, 22.

What error occurs when a dynamic array result can't spill because the cells are not empty?

#N/A
#VALUE!
#SPILL!
#REF!

✅ Answer: (c) — #SPILL! is a new error type introduced with dynamic arrays. It occurs when the formula needs to output multiple values but the destination cells are occupied.

Which combination creates a frequency table (unique values + their counts) in a single formula?

FILTER + SORT
UNIQUE + SEQUENCE
HSTACK + UNIQUE + COUNTIF
VSTACK + RANDARRAY

✅ Answer: (c) — =HSTACK(UNIQUE(range), COUNTIF(range, UNIQUE(range))) creates a two-column table with unique values and their counts — a complete frequency table in one formula.

Mini Project

🚀 Dynamic Dashboard (No Pivot Tables!)

📊 Project: Swiggy/Zomato Order Analytics Dashboard

Problem Statement

Build a complete analytics dashboard for a food delivery platform using ONLY dynamic array formulas — no pivot tables, no VBA, no Power Query. The dashboard should update automatically when new data is added.

Data Sheet (100+ rows)

Columns: Order ID, Customer Name, Restaurant, Cuisine Type, City, Order Amount ₹, Delivery Fee ₹, Rating (1-5), Order Date, Payment Method

Dashboard Components (All using dynamic array formulas)

City Dropdown Filter: Select a city → all data filters instantly
```
=FILTER(AllData, CityCol=SelectedCity, "No orders")
```

Top 10 Restaurants by Revenue:

=SORT(HSTACK(UNIQUE(RestCol), SUMIF(RestCol, UNIQUE(RestCol), AmountCol)), 2, -1)

Take first 10 rows

Unique Cuisines Available:
```
=SORT(UNIQUE(CuisineCol))
```

City-wise Summary Table:

=HSTACK(SORT(UNIQUE(CityCol)), COUNTIF(CityCol, SORT(UNIQUE(CityCol))), SUMIF(CityCol, SORT(UNIQUE(CityCol)), AmountCol))

Payment Method Distribution:

=HSTACK(UNIQUE(PaymentCol), COUNTIF(PaymentCol, UNIQUE(PaymentCol)))

High-Value Orders (>₹500):

=SORT(FILTER(AllData, AmountCol>500), 6, -1)

Random Sample of 10 Orders: Use SORTBY with RANDARRAY for random sampling
```
=SORTBY(AllData, RANDARRAY(ROWS(AllData)), 1)
```
and take first 10

Deliverables

Excel file with Data and Dashboard sheets
All 7 dashboard components working with dynamic formulas
City filter dropdown using Data Validation + FILTER
Professional formatting with conditional formatting for ratings
Everything updates automatically when new rows are added to data

[Screenshot: Complete dashboard showing city filter, top restaurants, cuisine list, and summary table — all powered by dynamic array formulas]

Q1: What are dynamic arrays in Excel and why are they important?

Answer: Dynamic arrays, introduced in Excel 365, allow a single formula to return multiple values that "spill" into adjacent cells automatically. Before this, each cell needed its own formula. This is revolutionary because: (1) One formula can replace dozens of formulas, (2) Results update automatically when source data changes, (3) Functions like FILTER, SORT, and UNIQUE enable pivot-table-like analysis without pivot tables, (4) The # spill operator lets other formulas reference the dynamic results. They represent the biggest change to Excel's calculation engine in 30 years.

Q2: How would you create a frequency table using dynamic arrays?

Answer: Use =HSTACK(SORT(UNIQUE(range)), COUNTIF(range, SORT(UNIQUE(range)))). UNIQUE extracts distinct values, SORT orders them, COUNTIF counts occurrences of each unique value, and HSTACK combines them side by side. For a more complete table with sums: =HSTACK(SORT(UNIQUE(CatCol)), COUNTIF(CatCol, SORT(UNIQUE(CatCol))), SUMIF(CatCol, SORT(UNIQUE(CatCol)), AmountCol)). This creates a Category | Count | Total table in one formula.

Q3: What is the #SPILL! error and how do you fix it?

Answer: #SPILL! occurs when a dynamic array formula needs to output multiple values but one or more cells in the output range are already occupied. To fix: (1) Click the #SPILL! cell — Excel highlights the blocking cells with a purple border, (2) Clear those blocking cells, (3) Move the formula to an area with enough empty space. You can also use the @ (implicit intersection) operator to force single-cell output: =@UNIQUE(range) returns only the first unique value.

Teaching dynamic arrays: Start with SEQUENCE — it's the easiest to understand (generates numbers). Then UNIQUE (students see instant results). Then SORT (logical next step). Save FILTER for last as it's the most powerful. The key "aha" moment is when you show HSTACK(UNIQUE(...), COUNTIF(..., UNIQUE(...))) — students realize they just built a pivot table with a single formula. Let them experiment with nesting combinations.

📋 Chapter 17 Summary

Dynamic arrays let one formula return multiple values that spill automatically
# (spill operator) references the entire spill range: G2#
UNIQUE extracts distinct values from a range
SORT sorts results; SORTBY sorts by a different column
FILTER extracts rows matching conditions — the most powerful function
SEQUENCE generates number sequences; RANDARRAY generates random data
HSTACK combines arrays horizontally; VSTACK combines vertically
Combining these functions creates pivot-table-like dashboards with formulas
Available only in Excel 365 and Excel 2021
#SPILL! error = destination cells aren't empty

Part IV — Complete

Part IV: What You've Mastered

🏆 Congratulations! You've completed Part IV

You now command the five most powerful lookup and reference techniques in Excel. From the workhorse VLOOKUP to the modern XLOOKUP, from the professional favorite INDEX-MATCH to the revolutionary Dynamic Arrays, you have the tools to find, retrieve, filter, and organize any data in any direction. These are the exact skills that companies like TCS, Infosys, Deloitte, and Flipkart test in interviews.

Chapter	Function(s)	Key Skill
13	VLOOKUP	Basic vertical lookups, exact & approximate match, error handling
14	HLOOKUP	Horizontal lookups for time-series and cross-tab data
15	XLOOKUP	Modern lookups: any direction, multiple returns, built-in error handling
16	INDEX + MATCH	Professional-grade lookups: flexible, fast, multi-criteria
17	Dynamic Arrays	UNIQUE, SORT, FILTER, SEQUENCE — formula-based dashboards

What's Next: Part V — Data Analysis & Visualization

In Part V, you'll learn to analyze data with Pivot Tables, create stunning Charts, use Conditional Formatting for visual insights, and master Data Validation for professional forms. You'll also explore What-If Analysis tools like Goal Seek and Scenario Manager. The journey from data entry to data analysis continues!