Advanced Excel · Beginner level

Beginner level · Module 2: Lookup and Logical Functions · Lesson 1 of 3

XLOOKUP and INDEX/MATCH

Find values in other tables by a key, handle missing keys and look up by ranges.

In this lesson you will

  • Use XLOOKUP with a not-found value
  • Use INDEX/MATCH and approximate-match lookups

Key concepts

lookup keyXLOOKUPINDEX/MATCHapproximate match for bands

The lesson

XLOOKUP(lookup_value, lookup_array, return_array, if_not_found) finds a key, such as a product code, and returns a value from the matching row. The if_not_found argument replaces #N/A with a clear message.

INDEX/MATCH does the same in older Excel versions: =INDEX(Products[Price], MATCH(F2, Products[Code], 0)). The 0 means exact match.

Lookups can also find bands: with a sorted table of thresholds, an approximate match assigns commission rates or grade bands. Use XLOOKUP's match_mode or MATCH with 1 for this.

Step by step

  1. Look up product names and prices by code.
  2. Add a not-found message.
  3. Rewrite one lookup with INDEX/MATCH.
  4. Assign commission bands with an approximate match.
Code example

Filling an order line with XLOOKUP

Look up the item name and price from a product table by code, and handle a code that does not exist.

Excel formulaOrders sheet
' Products table: Code | Item | Price (SSP)'   P01 | Rice 5 kg       | 17500'   P02 | Cooking oil 1 L |  9000'   P03 | Sugar 1 kg      |  2800 ' Order line: code in F2, quantity in I2G2: =XLOOKUP(F2, Products[Code], Products[Item], "Not found")H2: =XLOOKUP(F2, Products[Code], Products[Price], 0)J2: =H2*I2

Output

F2 = P02, I2 = 3  →  G2: Cooking oil 1 L   H2: 9000   J2: 27000
F2 = P07, I2 = 3  →  G2: Not found        H2: 0      J2: 0

How it works

XLOOKUP finds F2 in the Code column and returns the value from the same row of the Item or Price column. For P02 that is Cooking oil 1 L at 9,000 SSP, so three units cost 27,000 SSP. P07 does not exist, so the fourth argument returns 'Not found' and a price of 0 instead of #N/A — and the zero line total makes the problem visible without breaking the order total. Structured references (Products[Code]) keep working when products are added. Prices are illustrative.

Key concepts

XLOOKUPif_not_found argumentstructured referencesvisible handling of missing codes

Practice activity

Complete the order sheet with lookups for item names and prices, including codes that do not exist.

Expected result: Correct names and prices, and a clear message for unknown codes.

Check your understanding

  1. What does XLOOKUP's fourth argument do?

    Show answer

    Sets the value returned when nothing is found.

  2. What does the 0 in MATCH(F2, range, 0) mean?

    Show answer

    Exact match.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.