3.3 Lookup Functions & Advanced Excel Functions

Lookup functions allow Excel users to search for information in a dataset and return related values. Advanced functions can also be used to perform logical tests, conditional calculations, and more efficient data analysis.

What You Will Learn

• Understand lookup functions.
• Use VLOOKUP to find related information.
• Understand XLOOKUP where available in the Excel version being used.
• Use IF for logical conditions.
• Use COUNTIF for conditional counting.
• Use SUMIF for conditional totals.
• Combine functions for practical data analysis.
• Understand common lookup errors.

What are Lookup Functions?

Lookup functions search for a value in a table or range and return related information. They are useful when information is stored in one table and needs to be retrieved based on a unique identifier such as an employee ID, product code, customer ID, or invoice number.

VLOOKUP Function

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

A typical VLOOKUP structure is:

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

For example, if a product code is entered into a worksheet, VLOOKUP can be used to retrieve the corresponding product name or price from a product table.

XLOOKUP Function

XLOOKUP is available in supported versions of Microsoft Excel and provides a flexible method for searching one range and returning a corresponding value from another range.

A basic structure is:

=XLOOKUP(lookup_value, lookup_array, return_array)

The exact availability of XLOOKUP depends on the Excel version or Microsoft 365 environment being used.

IF Function

The IF function evaluates a condition and returns one result when the condition is true and another result when it is false.

A basic structure is:

=IF(condition, value_if_true, value_if_false)

For example, a marks worksheet can use IF to display Pass when a student’s marks meet the selected passing condition and Fail otherwise.

COUNTIF Function

COUNTIF counts cells that meet a specified condition.

For example:

=COUNTIF(B2:B20,”Sales”)

This can be used to count records that match a selected department, category, status, or other criterion.

SUMIF Function

SUMIF adds values that meet a specified condition.

For example, a sales worksheet can use SUMIF to calculate the total sales generated by a particular salesperson or category.

Common Lookup Problems

• The lookup value does not exist in the source data.
• The lookup range is incorrect.
• The selected return column is incorrect.
• Extra spaces or inconsistent data can prevent matching.
• The wrong match option is selected.
• Cell references may change incorrectly when formulas are copied.

Practical Activity

Create two worksheets named Products and Sales.

In the Products worksheet, create the following columns:

• Product Code
• Product Name
• Category
• Price

In the Sales worksheet, create:

• Invoice Number
• Product Code
• Quantity
• Product Name
• Price
• Total

1. Enter at least ten products in the Products worksheet.
2. Enter sales records using product codes.
3. Use VLOOKUP or XLOOKUP to retrieve the product name.
4. Use a lookup function to retrieve the product price.
5. Calculate the total using Quantity multiplied by Price.
6. Use IF to identify whether a selected sales amount meets a target.
7. Use COUNTIF to count records matching a selected category.
8. Use SUMIF to calculate the total value for a selected category.

Learning Outcome

After completing this section, learners will be able to use lookup and advanced Excel functions to retrieve information, apply logical conditions, perform conditional calculations, and analyze related datasets efficiently.