Chapter 05 | Advanced Formulas and Functions

Advanced Lookup Functions

Connect transaction records with master data and retrieve the right answer instantly. This chapter develops professional lookup skills using VLOOKUP, XLOOKUP, XMATCH, INDEX and MATCH for product pricing, employee records, inventory, sales, finance, customer service and management reporting.

Start Learning Practical Assignment

Choose Language

Formula Chapter · Eight Practical Activities Included

Learning Objectives

After This Chapter, You Will Be Able To

Select and build the correct lookup method for real office and business datasets.

Prepare Lookup Data

Organize master tables with unique keys, consistent data types and dependable return columns.

Use Modern Lookups

Apply XLOOKUP and XMATCH for flexible, readable and safer retrieval formulas.

Perform Two-Way Retrieval

Find values at the intersection of a selected row and column.

Control Match Behaviour

Choose exact, approximate, wildcard, first-to-last or last-to-first matching appropriately.

1 Understanding Lookup Logic and Master Data

A lookup formula searches for a known value, locates the matching record and returns related information from another field. For example, entering a product code can automatically return its product name, selling price, tax rate and stock category. This prevents repeated typing and keeps reports connected to one controlled source of truth.

Most professional lookup systems contain two parts: a transaction table, where daily activity is recorded, and a master table, where stable reference information is maintained. A unique key such as Product ID, Employee Code, Customer ID or Invoice Number connects the two tables.

Definition: A lookup function searches for a key in a specified range or array and returns a related value from the matching position.

Lookup KeyThe value you already know, such as P104.

Lookup ArrayThe column or row Excel must search.

Return ArrayThe field from which the answer is returned.

Match RuleExact, approximate or wildcard behaviour.

Rules for Dependable Lookup Tables

Use a unique, non-blank key for each master record.
Keep codes consistent; numeric 101 and text "101" may not match.
Remove leading and trailing spaces from imported keys.
Convert the master range into an Excel Table for automatic expansion.
Do not merge cells inside the lookup dataset.
Decide how missing or duplicate keys should be handled before building the formula.

Practical Experiment 1: Audit a Product Master

Create columns for Product ID, Product Name, Category, Selling Price and Tax Rate, then check whether every Product ID is complete and unique.

Step 1: Build

Enter at least 12 sample product records with realistic codes.

Step 2: Inspect

Use conditional formatting or COUNTIF to identify duplicate codes.

Step 3: Correct

Remove duplicates, blank keys and inconsistent text or number formats.

Learning Output: A clean master table suitable for reliable lookup formulas.

2 VLOOKUP and HLOOKUP: Revision, Use and Limitations

VLOOKUP searches vertically in the first column of a table and returns a value from a specified column number. It remains common in older workbooks, so advanced learners must understand both its correct use and its risks.

=VLOOKUP(A2, $H$2:$K$20, 3, FALSE)

In this example, Excel searches for the value in A2 within the first column of H2:K20 and returns the value from the third column of that range. FALSE requests an exact match.

Function	Search Direction	Common Use	Important Limitation
VLOOKUP	Top to bottom	Find a code in the first column and return a field to its right.	Cannot naturally return a value located to the left of the lookup column.
HLOOKUP	Left to right	Find a heading in the top row and return a value from a lower row.	Less suitable for standard record-based datasets arranged vertically.

Why VLOOKUP Formulas Can Break

The lookup column must be the first column of the table array.
The return field is identified by a hard-coded column number.
Inserting or deleting columns may return the wrong field.
Omitting the fourth argument can activate approximate matching unexpectedly.
It returns the first match and does not warn you about duplicate keys.

Important: For exact business retrieval, always specify FALSE or 0 in VLOOKUP unless an approximate banded lookup is intentionally required.

Practical Experiment 2: Product Price with VLOOKUP

Use a product code entered in A2 to retrieve a product name and price from your Product Master.

Step 1: Retrieve Name

Build an exact-match VLOOKUP for the Product Name field.

Step 2: Retrieve Price

Change the column index to return Selling Price.

Step 3: Test Failure

Enter an unknown code and observe the #N/A result.

Learning Output: Understanding of traditional vertical lookup behaviour and limitations.

3 XLOOKUP for Flexible and Professional Retrieval

XLOOKUP separates the search range from the return range. This makes formulas clearer, allows left or right retrieval, provides a built-in message for missing matches and removes the need to count return-column numbers.

=XLOOKUP(A2, ProductMaster[Product ID], ProductMaster[Selling Price], "Code Not Found")

XLOOKUP Argument	Purpose	Example
lookup_value	The value you want to find.	A2
lookup_array	The single row or column Excel searches.	ProductMaster[Product ID]
return_array	The row or column from which Excel returns the answer.	ProductMaster[Selling Price]
if_not_found	A controlled message or value for a missing match.	"Code Not Found"
match_mode	Exact, next smaller, next larger or wildcard match.	0, -1, 1 or 2
search_mode	First-to-last, last-to-first or binary search.	1, -1, 2 or -2

Professional Advantages of XLOOKUP

Left Lookup Built-in Missing Message No Column Counting Can Return Multiple Columns

=XLOOKUP(A2, ProductMaster[Product ID], ProductMaster[[Product Name]:[Selling Price]], "Not Found")

In supported Excel versions, the second formula can spill several related fields into adjacent cells from one lookup.

Practical Experiment 3: Upgrade VLOOKUP to XLOOKUP

Replace the VLOOKUP formulas from Experiment 2 with structured XLOOKUP formulas.

Step 1: Convert

Use Product ID as the lookup array and Product Name as the return array.

Step 2: Add Control

Display “Invalid Product Code” when no match exists.

Step 3: Compare

Insert a new master-table column and confirm that XLOOKUP still returns the correct field.

Learning Output: A more readable and resilient lookup formula.

4 XMATCH for Finding a Position

XMATCH returns the relative position of a value within a one-dimensional range. It does not return the related business value directly; instead, it tells Excel where the match appears. This position can be used independently or supplied to INDEX.

=XMATCH(A2, ProductMaster[Product ID], 0)

If the entered product code is the fifth item in the Product ID column, XMATCH returns 5.

Where XMATCH Is Useful

Checking whether a code exists in a list.
Locating a selected month or heading for a two-way lookup.
Finding the first or last occurrence of a value.
Replacing older MATCH formulas with clearer match and search modes.

=XMATCH(A2, Sales[Customer ID], 0, -1) // search from last record to first

Practical Experiment 4: Find First and Last Transactions

Use a Customer ID that appears several times in a sales list.

Step 1: First Match

Use search mode 1 to return the first matching position.

Step 2: Last Match

Use search mode -1 to return the final matching position.

Step 3: Explain

Write when the first or latest transaction is more useful.

Learning Output: Control over match position and search direction.

5 INDEX and MATCH: A Flexible Classic Combination

INDEX returns a value from a given row or column position. MATCH identifies the position of the lookup key. When combined, MATCH finds the row and INDEX returns the corresponding value from any selected return column.

=INDEX(ProductMaster[Selling Price], MATCH(A2, ProductMaster[Product ID], 0))

Identify the Key

Choose the Product ID or other known value.

MATCH the Position

Find the row number within the lookup column.

INDEX the Answer

Return the value from the same position in another column.

Handle Missing Keys

Add IFNA or a controlled validation process.

Professional Tip: INDEX with MATCH remains valuable for compatibility with Excel versions that do not support XLOOKUP and for advanced two-dimensional models.

Practical Experiment 5: Employee Department Lookup

Use Employee Code to return Department from an Employee Master table.

Step 1: MATCH

Test MATCH separately and confirm the returned row position.

Step 2: INDEX

Embed MATCH inside INDEX to return Department.

Step 3: Return Left

Place Employee Name left of Employee Code and confirm the formula can still return it.

Learning Output: A flexible lookup that is independent of column direction.

6 Two-Way Lookup Using Row and Column Selection

A two-way lookup retrieves a value at the intersection of one selected row and one selected column. For example, you may choose a salesperson in one cell and a month in another cell, then return the sales amount where that person and month meet.

=INDEX(B2:M10, XMATCH(P2, A2:A10), XMATCH(P3, B1:M1))

Selected Input	Search Range	Resulting Position
Salesperson in P2	A2:A10	Row number within the sales matrix
Month in P3	B1:M1	Column number within the sales matrix
INDEX matrix B2:M10	Uses both positions	Sales value at the intersection

Practical Experiment 6: Monthly Performance Matrix

Create a matrix containing six employees and six months of performance values.

Step 1: Select Row

Create a dropdown for Employee Name.

Step 2: Select Column

Create a dropdown for Month.

Step 3: Retrieve

Use INDEX with two XMATCH functions to return the selected value.

Learning Output: An interactive two-dimensional performance lookup.

7 Multiple-Condition Lookups

A single field may not uniquely identify a record. A price list could contain the same Product ID for several branches, or an employee could have one attendance record for every month. In such cases, two or more conditions must be matched together.

=XLOOKUP(1, (Sales[Product ID]=H2)*(Sales[Branch]=H3), Sales[Net Amount], "Not Found")

Each comparison creates TRUE or FALSE values. Multiplication converts TRUE to 1 and requires both conditions to be TRUE, producing a combined lookup value of 1 only for the qualifying record.

Alternative Helper-Key Method

=[@[Product ID]]&"|"&[@Branch]

=XLOOKUP(H2&"|"&H3, Sales[Helper Key], Sales[Net Amount], "Not Found")

A helper key is easier for beginners to audit, while array-based criteria keep the table cleaner. Choose based on maintainability and workbook compatibility.

Practical Experiment 7: Branch-Wise Product Price

Build a price table where one Product ID has different selling prices in different branches.

Step 1: Create Data

Enter Product ID, Branch and Price for at least 15 rows.

Step 2: Match Two Inputs

Retrieve price using the selected Product ID and Branch.

Step 3: Test Duplicates

Check whether the combination is truly unique before trusting the result.

Learning Output: Accurate retrieval from datasets that require more than one key.

8 Exact, Approximate, Wildcard and Search Modes

Lookup accuracy depends on the selected match rule. Exact matching is safest for IDs and codes. Approximate matching is useful for rate slabs, grading bands, commission percentages and tax tables. Wildcards help when only part of the text is known.

Mode	XLOOKUP / XMATCH Setting	Suitable Example	Data Requirement
Exact	0	Employee Code, Product ID, Invoice Number	Key must match accurately.
Exact or Next Smaller	-1	Commission or discount threshold	Threshold logic must be planned correctly.
Exact or Next Larger	1	Delivery bracket or minimum qualifying limit	Understand which higher band should apply.
Wildcard	2	Partial customer or product text	Use * for any characters and ? for one character.
Last-to-First Search	search_mode -1	Latest matching status or transaction	Records must be arranged in meaningful order.

=XLOOKUP(F2, Commission[Minimum Sales], Commission[Rate], "Below Minimum", -1)

Risk Control: Approximate lookups can produce believable but incorrect answers when threshold data is incomplete, unsorted where required, or logically designed in the wrong direction.

Practical Experiment 8: Commission Slab Lookup

Create a commission table with minimum sales thresholds and corresponding rates.

Step 1: Define Slabs

Create ascending thresholds such as 0, 25,000, 50,000 and 100,000.

Step 2: Retrieve Rate

Use an approximate XLOOKUP to return the applicable commission percentage.

Step 3: Boundary Test

Test values below, exactly at and just above every threshold.

Learning Output: A validated slab-based retrieval system.

Real-Time Practical Assignment

Build a Smart Sales Invoice Lookup System

Connect a transaction entry sheet to Product, Customer and Salesperson master tables.

Assignment: Automated Master-Data Retrieval

Create a workbook that automatically fills descriptive fields after the user enters controlled codes.

Step 1: Prepare Masters

Create Product Master, Customer Master and Employee Master as separate Excel Tables with unique IDs.

Step 2: Build Transaction Entry

Create columns for Date, Invoice No., Product ID, Customer ID, Salesperson Code, Quantity and Selling Price.

Step 3: Automate Retrieval

Return Product Name, Category, Customer Name, City, Salesperson Name and Department using suitable lookup formulas.

Required Formula Skills

Use at least three XLOOKUP formulas.
Use one INDEX with MATCH or XMATCH formula.
Include a meaningful message for invalid codes.
Use structured references wherever practical.
Create one two-way lookup summary.
Use one multiple-condition lookup.

Expected Business Output

Reduced manual data-entry effort.
Consistent product and customer information.
Clear invalid-code alerts.
Easy expansion when new master records are added.
Reliable inputs for PivotTables and dashboards.
Documented formula and data-quality checks.

Lookup Decision Assistant

Select the Requirement

Use this mini activity to identify a suitable lookup approach.

Return Direction Number of Criteria Excel Compatibility

Recommended: XLOOKUP for a clear, flexible modern lookup.

Lookup Formula Review Worksheet

Is the lookup key unique and complete?Record duplicate, blank or inconsistent keys found.

Which field is searched and which field is returned?Identify the lookup and return arrays clearly.

Is exact or approximate matching required?Explain the business rule rather than guessing.

What should happen when no match is found?Use a meaningful message, blank or controlled default only when appropriate.

Could duplicate matches exist?Decide whether the first, last or all matching records are required.

Will the formula remain correct after data expansion?Prefer Excel Tables and structured references.

Common Mistakes

Lookup Errors Students Should Avoid

A formula can be syntactically correct and still return the wrong business answer.

Wrong Habits

Using approximate VLOOKUP without intending to.
Mixing numeric keys with text-formatted keys.
Ignoring duplicate records in the master table.
Hard-coding VLOOKUP column numbers without documenting them.
Using entire-column array calculations unnecessarily.
Displaying blank results that conceal invalid source codes.
Testing only records that are known to match.

Professional Habits

Use unique and standardized master-data keys.
Choose exact matching by default for IDs and codes.
Use structured table references for expanding data.
Provide a clear missing-match message.
Test first, last, duplicate, blank and invalid cases.
Document approximate-threshold logic.
Select functions according to version compatibility.

Remember: Never trust a lookup result until the uniqueness, data type, match mode and missing-value behaviour have been tested.

AICPE Quality Learning Commitment

AICPE Gurukul develops practical, career-oriented learning that helps students build dependable office, analytical, freelancing and self-employment skills. Learn more at aicpeindia.org and aicpe.online.

Quick Revision

Remember These Lookup Principles

Review these ideas before moving to mathematical and statistical functions.

Start with a Clean Key

Unique, complete and consistently formatted keys are the foundation of reliable retrieval.

Know VLOOKUP Limits

It searches the first table column and relies on a return-column number.

Prefer Flexible Arrays

XLOOKUP separates search and return ranges and supports controlled missing results.

Position Powers INDEX

MATCH or XMATCH finds the position that INDEX uses to return the answer.

Use Two-Way Logic

Combine row and column matches to retrieve a value from a matrix.

Test Match Behaviour

Validate exact, approximate, wildcard, duplicate and missing-match scenarios.