Advanced Excel Chapter 3: Lookup and Reference Functions with Practical Examples

Lookup functions help you search for information in a table and return related data automatically. They are widely used in sales reports, inventory systems, payroll sheets, student records, invoices and management dashboards.

In this chapter, you will learn VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH and XMATCH. You will also learn exact matching, approximate matching, two-way lookup, multiple-criteria lookup and professional error handling.

Learning Objectives

After completing this chapter, you should be able to:

• Understand how lookup functions work.
• Use VLOOKUP for vertical searches.
• Use HLOOKUP for horizontal searches.
• Use XLOOKUP as a modern alternative to VLOOKUP.
• Combine INDEX and MATCH for flexible lookups.
• Use XMATCH to find the position of a value.
• Perform exact and approximate matching.
• Create two-way and multiple-criteria lookups.
• Handle missing records professionally.
• Build a dynamic product-search report.

1. Understanding Lookup Functions

A lookup formula searches for a value in one area and returns related information from another row or column.

Basic Lookup Process

Lookup Value → Search Column → Matching Record → Return Value

For example, when a user enters a Product ID, Excel can search the Product Master table and return the product name, category, unit price and stock quantity.

Common Business Uses

• Finding product prices from product codes
• Retrieving employee departments from employee IDs
• Finding student names from roll numbers
• Adding customer details to invoices
• Matching orders with product information
• Assigning commission rates based on sales
• Creating searchable management reports

2. Create the Product Master Dataset

Create a worksheet named Product_Master and enter the following records:

ProductID	ProductName	Category	UnitPrice	Stock	Supplier
P-1001	Laptop	Computer	45000	25	Tech World
P-1002	Monitor	Computer	12000	40	Digital Hub
P-1003	Keyboard	Accessories	1500	100	Tech World
P-1004	Mouse	Accessories	800	150	Digital Hub
P-1005	Printer	Office Equipment	18500	20	Office Solutions
P-1006	Scanner	Office Equipment	9500	18	Office Solutions
P-1007	Webcam	Accessories	2500	75	Digital Hub
P-1008	Projector	Office Equipment	55000	12	Vision Technologies

Convert the dataset into an Excel Table using Ctrl + T and name it ProductData.

3. Using the VLOOKUP Function

VLOOKUP searches for a value in the first column of a table and returns information from another column in the same row.

VLOOKUP Syntax

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP Arguments

• lookup_value – The value you want to search for.
• table_array – The table containing the required data.
• col_index_num – The number of the return column.
• range_lookup – Use FALSE for an exact match or TRUE for an approximate match.

Example: Find Product Name

Suppose the Product ID is entered in cell H2. Use the following formula to return the product name:

=VLOOKUP(H2,$A$2:$F$9,2,FALSE)

Find Product Category

=VLOOKUP(H2,$A$2:$F$9,3,FALSE)

Find Unit Price

=VLOOKUP(H2,$A$2:$F$9,4,FALSE)

Find Available Stock

=VLOOKUP(H2,$A$2:$F$9,5,FALSE)

Find Supplier Name

=VLOOKUP(H2,$A$2:$F$9,6,FALSE)

4. VLOOKUP with an Excel Table

Using an Excel Table makes the formula more readable and ensures that newly added products are included automatically.

Find Product Name

=VLOOKUP(H2,ProductData,2,FALSE)

Find Unit Price

=VLOOKUP(H2,ProductData,4,FALSE)

Find Stock

=VLOOKUP(H2,ProductData,5,FALSE)

5. Understanding Exact Match in VLOOKUP

Use FALSE when Excel must find an exact value, such as a Product ID, Employee ID, Invoice Number or Student Roll Number.

=VLOOKUP(H2,ProductData,4,FALSE)

If the exact Product ID cannot be found, Excel returns #N/A.

Use IFNA to Handle a Missing Product

=IFNA(VLOOKUP(H2,ProductData,4,FALSE),"Product Not Found")

Use IFERROR for General Error Handling

=IFERROR(VLOOKUP(H2,ProductData,4,FALSE),"Unable to Retrieve Price")

IFNA is usually more suitable when the expected issue is simply that the lookup value does not exist.

6. Limitations of VLOOKUP

• The lookup column must be the first column of the selected table.
• VLOOKUP normally searches only from left to right.
• The return column is identified by a fixed column number.
• Inserting or deleting columns can produce incorrect results.
• VLOOKUP returns only the first matching record.
• It is less flexible than XLOOKUP or INDEX and MATCH.

Example of a Fixed Column Problem

=VLOOKUP(H2,ProductData,4,FALSE)

The number 4 means that Excel should return a value from the fourth column. If a new column is inserted before UnitPrice, the formula may return data from the wrong column.

7. Using the HLOOKUP Function

HLOOKUP searches horizontally across the first row of a table and returns a value from another row in the same column.

HLOOKUP Syntax

=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

Horizontal Sales Dataset

Month	January	February	March	April
Sales	125000	142000	138000	165000
Expenses	85000	92000	88000	105000
Profit	40000	50000	50000	60000

Find March Sales

=HLOOKUP("March",A1:E4,2,FALSE)

Find March Profit

=HLOOKUP("March",A1:E4,4,FALSE)

HLOOKUP is useful for horizontally arranged tables, but most professional datasets should be stored vertically because vertical structures work better with Excel Tables, PivotTables and Power Query.

8. Using the XLOOKUP Function

XLOOKUP is a modern and flexible lookup function. It can search vertically or horizontally, return data from the left or right and provide a custom result when no match is found.

XLOOKUP Syntax

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

Find Product Name

=XLOOKUP(H2,ProductData[ProductID],ProductData[ProductName])

Find Unit Price

=XLOOKUP(H2,ProductData[ProductID],ProductData[UnitPrice])

Find Stock Quantity

=XLOOKUP(H2,ProductData[ProductID],ProductData[Stock])

Return a Custom Message When No Match Exists

=XLOOKUP(
H2,
ProductData[ProductID],
ProductData[ProductName],
"Product Not Found"
)

9. Why XLOOKUP Is Better Than VLOOKUP

VLOOKUP	XLOOKUP
Requires a table-array range	Uses separate lookup and return arrays
Normally searches left to right	Can search in any direction
Uses a fixed column number	Uses an actual return column
Needs FALSE for an exact match	Uses exact matching by default
Needs IFNA or IFERROR for missing values	Includes an optional not-found argument
Can break when columns are inserted	More resistant to structural changes

10. Performing a Left Lookup with XLOOKUP

Suppose you know the supplier name and want to return a related Product ID. XLOOKUP can return values from a column located to the left of the lookup column.

=XLOOKUP(
H2,
ProductData[Supplier],
ProductData[ProductID],
"Supplier Not Found"
)

A traditional VLOOKUP formula cannot perform this lookup directly because ProductID is positioned to the left of Supplier.

11. Returning Multiple Columns with XLOOKUP

Modern versions of Excel can return multiple adjacent columns from one XLOOKUP formula.

=XLOOKUP(
H2,
ProductData[ProductID],
ProductData[[ProductName]:[Supplier]],
"Product Not Found"
)

The result automatically spills across multiple columns and can return Product Name, Category, Unit Price, Stock and Supplier together.

Important Requirement

The cells into which the result needs to spill must be empty. Otherwise, Excel may display a #SPILL! error.

12. XLOOKUP Match Modes

The optional match mode controls how XLOOKUP handles exact, approximate and wildcard matches.

Match mode	Meaning
0	Exact match
-1	Exact match or next smaller item
1	Exact match or next larger item
2	Wildcard match

Exact Match Example

=XLOOKUP(H2,ProductData[ProductID],ProductData[UnitPrice],"Not Found",0)

Wildcard Search Example

=XLOOKUP(
"*Laptop*",
ProductData[ProductName],
ProductData[UnitPrice],
"Not Found",
2
)

The asterisks allow additional text to appear before or after the word Laptop.

13. XLOOKUP Search Modes

Search mode	Meaning
1	Search from first to last
-1	Search from last to first
2	Binary search in ascending order
-2	Binary search in descending order

Find the Last Matching Record

=XLOOKUP(
H2,
SalesData[ProductID],
SalesData[OrderDate],
"No Record",
0,
-1
)

This formula searches from the bottom upward and returns the latest matching record when data is arranged chronologically.

14. Approximate Lookup for Commission Rates

Approximate matching is useful when values belong to ranges, such as commission percentages, grades, tax rates or shipping charges.

Commission Table

Minimum Sales	Commission Rate
0	0%
50000	3%
100000	5%
150000	7%
200000	10%

Approximate VLOOKUP Formula

=VLOOKUP(B2,$H$2:$I$6,2,TRUE)

Important: The first column of an approximate VLOOKUP table must be sorted in ascending order.

Approximate XLOOKUP Formula

=XLOOKUP(
B2,
$H$2:$H$6,
$I$2:$I$6,
0,
-1
)

The match mode -1 returns an exact match or the next smaller value.

Calculate Commission Amount

=B2*XLOOKUP(B2,$H$2:$H$6,$I$2:$I$6,0,-1)

15. Using the MATCH Function

MATCH searches for a value and returns its relative position within a row or column.

MATCH Syntax

=MATCH(lookup_value,lookup_array,match_type)

Find the Position of a Product ID

=MATCH(H2,ProductData[ProductID],0)

If Product ID P-1004 is the fourth item in the ProductID column, MATCH returns 4.

MATCH Types

Match type	Meaning
0	Exact match
1	Largest value less than or equal to lookup value
-1	Smallest value greater than or equal to lookup value

16. Using the INDEX Function

INDEX returns a value from a selected range based on its row and column position.

INDEX Syntax

=INDEX(array,row_num,[column_num])

Return the Fourth Product Name

=INDEX(ProductData[ProductName],4)

This formula returns the fourth item from the ProductName column.

17. Combining INDEX and MATCH

INDEX and MATCH can be combined to create a flexible lookup formula. MATCH finds the position, and INDEX returns the value from that position.

Find Product Name

=INDEX(
ProductData[ProductName],
MATCH(H2,ProductData[ProductID],0)
)

Find Unit Price

=INDEX(
ProductData[UnitPrice],
MATCH(H2,ProductData[ProductID],0)
)

Find Supplier

=INDEX(
ProductData[Supplier],
MATCH(H2,ProductData[ProductID],0)
)

Handle Missing Records

=IFNA(
INDEX(
ProductData[UnitPrice],
MATCH(H2,ProductData[ProductID],0)
),
"Product Not Found"
)

18. Advantages of INDEX and MATCH

• It can search in any direction.
• The lookup column does not need to be first.
• It does not depend on a fixed column number.
• It is more flexible than VLOOKUP.
• It works well in older Excel versions where XLOOKUP is unavailable.
• It can be extended to two-way and multiple-criteria lookups.

19. Using the XMATCH Function

XMATCH is a modern replacement for MATCH. It returns the relative position of a value and supports additional matching and search options.

XMATCH Syntax

=XMATCH(lookup_value,lookup_array,[match_mode],[search_mode])

Find the Position of a Product

=XMATCH(H2,ProductData[ProductID],0)

Search from Last to First

=XMATCH(H2,SalesData[ProductID],0,-1)

Combine INDEX and XMATCH

=INDEX(
ProductData[UnitPrice],
XMATCH(H2,ProductData[ProductID],0)
)

20. Two-Way Lookup with INDEX and MATCH

A two-way lookup finds a value at the intersection of a selected row and column. For example, it can return the sales amount for a specific product and month.

Monthly Sales Matrix

Product	January	February	March	April
Laptop	250000	300000	275000	325000
Monitor	120000	145000	135000	155000
Printer	95000	110000	125000	118000

Suppose the selected Product is stored in H2 and the selected Month is stored in H3.

Two-Way Lookup Formula

=INDEX(
B2:E4,
MATCH(H2,A2:A4,0),
MATCH(H3,B1:E1,0)
)

Two-Way Lookup Using XMATCH

=INDEX(
B2:E4,
XMATCH(H2,A2:A4,0),
XMATCH(H3,B1:E1,0)
)

How the Formula Works

• The first MATCH finds the product’s row position.
• The second MATCH finds the month’s column position.
• INDEX returns the value where the selected row and column intersect.

21. Two-Way Lookup with XLOOKUP

A nested XLOOKUP can also perform a two-way lookup.

=XLOOKUP(
H2,
A2:A4,
XLOOKUP(H3,B1:E1,B2:E4)
)

The inner XLOOKUP selects the required month column, and the outer XLOOKUP returns the value for the selected product.

22. Multiple-Criteria Lookup

A multiple-criteria lookup searches for a record using more than one condition. For example, you may need to find sales for a particular salesperson and product.

Example Dataset

Salesperson	Product	Region	TotalSales
Rahul	Laptop	East	90000
Rahul	Monitor	East	48000
Priya	Laptop	North	135000
Priya	Printer	North	55500

Suppose the selected Salesperson is in H2 and the selected Product is in H3.

Multiple-Criteria XLOOKUP

=XLOOKUP(
1,
(A2:A5=H2)*(B2:B5=H3),
D2:D5,
"Record Not Found"
)

Multiple-Criteria INDEX and MATCH

=IFNA(
INDEX(
D2:D5,
MATCH(
1,
(A2:A5=H2)*(B2:B5=H3),
0
)
),
"Record Not Found"
)

How the Formula Works

• A2:A5=H2 checks the salesperson.
• B2:B5=H3 checks the product.
• Multiplying the two tests produces 1 only where both conditions are true.
• XLOOKUP or MATCH searches for that 1.

23. Case-Sensitive Lookup

Standard Excel lookup formulas are generally not case-sensitive. The EXACT function can be combined with INDEX and MATCH when uppercase and lowercase differences matter.

=INDEX(
B2:B10,
MATCH(
TRUE,
EXACT(H2,A2:A10),
0
)
)

This formula treats product codes such as abc101 and ABC101 as different values.

24. Create a Dynamic Product Search Report

Create a worksheet named Product_Search. The user will enter or select a Product ID, and Excel will display all related product details automatically.

Suggested Search Layout

Field	Input or result
Product ID	User input or dropdown
Product Name	XLOOKUP result
Category	XLOOKUP result
Unit Price	XLOOKUP result
Available Stock	XLOOKUP result
Supplier	XLOOKUP result
Stock Status	IF result

Product Name Formula

=XLOOKUP(
B2,
ProductData[ProductID],
ProductData[ProductName],
"Product Not Found"
)

Category Formula

=XLOOKUP(
B2,
ProductData[ProductID],
ProductData[Category],
""
)

Unit Price Formula

=XLOOKUP(
B2,
ProductData[ProductID],
ProductData[UnitPrice],
0
)

Stock Formula

=XLOOKUP(
B2,
ProductData[ProductID],
ProductData[Stock],
0
)

Supplier Formula

=XLOOKUP(
B2,
ProductData[ProductID],
ProductData[Supplier],
""
)

Stock Status Formula

=IF(
B6=0,
"Out of Stock",
IF(
B6<=20,
"Low Stock",
"Available"
)
)

25. Common Lookup Errors

Incorrect Range Selection

The selected table does not contain the lookup or return column.

Numbers Stored as Text

A product code stored as a number may not match the same code stored as text. Ensure that both values use consistent data types.

Extra Spaces

Imported data may contain hidden spaces. Use TRIM to remove unnecessary spaces:

=TRIM(A2)

Approximate Match Used Accidentally

Using TRUE or leaving the final VLOOKUP argument blank can return an incorrect approximate result.

=VLOOKUP(H2,ProductData,4,FALSE)

Fixed Column Number

A VLOOKUP formula may return incorrect data after a new column is inserted. Use XLOOKUP or INDEX and MATCH for a more flexible solution.

Duplicate Lookup Values

Most lookup functions return only the first matching record. Use FILTER when all matching records are required.

=FILTER(
SalesData,
SalesData[ProductID]=H2,
"No Records Found"
)

26. Lookup Formula Best Practices

• Use unique IDs as lookup values whenever possible.
• Convert source data into Excel Tables.
• Use exact matching for IDs and codes.
• Use XLOOKUP in modern versions of Excel.
• Use INDEX and MATCH when compatibility with older Excel versions is required.
• Use IFNA for expected missing lookup values.
• Keep lookup and return ranges equal in size.
• Check for duplicate IDs in the master table.
• Remove unnecessary spaces from imported data.
• Keep number and text formats consistent.
• Test formulas using both valid and invalid lookup values.
• Avoid hiding unexpected errors with IFERROR.

27. Practical Assignment: Product Search and Billing System

Create a Product Search and Billing System using the Product Master table.

Required Worksheets

• Product_Master
• Product_Search
• Sales_Entry
• Invoice

Assignment Requirements

1. Create at least 30 product records.
2. Convert the Product Master range into an Excel Table.
3. Name the table ProductData.
4. Create a Product ID dropdown using Data Validation.
5. Use XLOOKUP to display product details.
6. Use IFNA or the XLOOKUP not-found argument.
7. Automatically retrieve the product price in the Sales Entry sheet.
8. Calculate Quantity × Unit Price.
9. Display Low Stock when stock is 20 or below.
10. Create a two-way monthly product-sales lookup.
11. Create a multiple-criteria lookup using Salesperson and Product.
12. Apply professional formatting to the search interface.

Sales Amount Formula

=[@Quantity]*[@UnitPrice]

Stock Validation Formula

=IF(
[@Quantity]>[@AvailableStock],
"Insufficient Stock",
"Order Accepted"
)

28. Practice Exercises

Exercise 1: Employee Lookup

Create an Employee Master table and return Employee Name, Department and Salary using an Employee ID.

Exercise 2: Student Search

Create a student-search form that displays Student Name, Class, Marks and Result from a Roll Number.

Exercise 3: Approximate Commission Lookup

Create a commission table and use an approximate lookup to return a rate based on monthly sales.

Exercise 4: Two-Way Lookup

Create a Product × Month sales matrix and return the sales value using selected Product and Month values.

Exercise 5: Multiple-Criteria Lookup

Return the sales amount using Salesperson, Product and Region as three separate conditions.

=XLOOKUP(
1,
(A2:A100=H2)*
(B2:B100=H3)*
(C2:C100=H4),
D2:D100,
"Record Not Found"
)

29. Chapter Quiz

1. What does a lookup function do?
2. Why should FALSE normally be used for Product ID lookups?
3. What is the biggest left-lookup limitation of VLOOKUP?
4. Why is XLOOKUP safer when columns are inserted?
5. What does MATCH return?
6. What does INDEX return?
7. How do INDEX and MATCH work together?
8. What is a two-way lookup?
9. When should an approximate lookup be used?
10. Why must an approximate VLOOKUP table be sorted?
11. How can multiple lookup conditions be combined?
12. Which function can return all matching records?

30. Frequently Asked Questions

Should I use VLOOKUP or XLOOKUP?

Use XLOOKUP when working in a modern version of Excel. It is more flexible, searches in any direction and supports a built-in not-found result. Use VLOOKUP when compatibility with older versions is required.

Can XLOOKUP return data from the left?

Yes. XLOOKUP uses separate lookup and return arrays, so the return column can be positioned anywhere.

Why does my lookup formula return #N/A?

The lookup value may not exist, its data type may not match, or it may contain extra spaces. Check the source data before hiding the error.

Can a lookup return more than one record?

Traditional lookup functions normally return one record. Use the FILTER function when you need every matching record.

What is the difference between MATCH and XMATCH?

Both return a value’s position. XMATCH offers more flexible matching and search-direction options and uses exact matching by default.

Can lookup formulas use more than one condition?

Yes. Multiple conditions can be combined into arrays of TRUE and FALSE results. Multiplying those arrays produces 1 where all conditions are satisfied.

Conclusion

Lookup and reference functions connect different areas of a workbook and eliminate repetitive data entry. VLOOKUP and HLOOKUP provide traditional lookup methods, while XLOOKUP offers a more flexible and reliable modern solution.

INDEX, MATCH and XMATCH provide additional control for dynamic, two-way and multiple-criteria lookups. By combining these functions with Excel Tables and professional error handling, you can build reliable product-search tools, invoices, employee reports and management systems.

In the next chapter, we will learn conditional aggregation functions, including SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF, AVERAGEIFS, MAXIFS and MINIFS.