Skip to main content
SheetTutor.
← All tasks↓ Sample workbook

Look up a price by an exact ID

Read-only

Step-by-step guide · read-only · Selected G2

Loading your interactive spreadsheet…

Preparing the worksheet and formulas…

Data & tools
Task requirements

Fill G2:G4 with the price for each ID in F2:F4. Use an exact match, return the first match, and show Not found when no ID matches.

Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G2, G3, G4. Browser calculations may differ from your Excel version.

Selection & formula search

Practice stays on this device. No worksheet data is uploaded.

Notes & examples
About this lesson
Find & reference7 min·Growing

Enter a product ID, return its price, and handle missing or repeated IDs without hiding the rule.

Choose the result you need

F2 supplies the ID, A2:A7 contains IDs and C2:C7 contains prices. Match mode 0 requests an exact match; the missing-result argument returns readable text. Fill G2:G4.

=XLOOKUP(F2,$A$2:$A$7,$C$2:$C$7,"Not found",0)
Download this example

Exact matching ignores letter case here. Keep the lookup and return ranges aligned. This example returns one price, not all matching rows or an approximate price band.

All result formulas
G2
=XLOOKUP(F2,$A$2:$A$7,$C$2:$C$7,"Not found",0)
G3
=XLOOKUP(F3,$A$2:$A$7,$C$2:$C$7,"Not found",0)
G4
=XLOOKUP(F4,$A$2:$A$7,$C$2:$C$7,"Not found",0)
Complete example · Find catalogue prices. Quoted strings show their exact spaces and spelling. Result cells are calculated outputs.Scroll sideways to read every column.
RowABCFG
1"Product ID""Product""Price""Find ID""Price found"
2"P-101""Notebook"8"P-103"5
3"P-102""Pen set"12"p-105"9
4"P-103""Folder"5"P-999""Not found"
5"P-104""Desk pad"18——
6"P-105""Binder"9——
7"P-106""Clips"4——

These are reference examples with matching downloads. The primary worksheet has its own stated task and checks; choosing an example here leaves that worksheet and your progress unchanged.

Open the primary practice

Microsoft function reference ↗ · Browser calculations checked. Native Excel and Google Sheets recalculation of these reference files has not been verified.

All worked examples

Each example uses the source data in its own downloadable workbook. Cell references below belong to that file. Opening an example leaves the primary practice and your progress unchanged.

Find a price or report no match

F2 supplies the ID, A2:A7 contains IDs and C2:C7 contains prices. Match mode 0 requests an exact match; the missing-result argument returns readable text. Fill G2:G4.

=XLOOKUP(F2,$A$2:$A$7,$C$2:$C$7,"Not found",0)

Enter in G2 in this example's workbook. The download contains the complete source and all result formulas.

G2
5
G3
9
G4
"Not found"

Exact matching ignores letter case here. Keep the lookup and return ranges aligned. This example returns one price, not all matching rows or an approximate price band.

Download this example's workbook
Return the first matching row

A-1 occurs twice with different prices. The default search starts at the top and returns 14 from the first complete source row.

=XLOOKUP(F2,$A$2:$A$7,$C$2:$C$7,"Not found",0)

Enter in G2 in this example's workbook. The download contains the complete source and all result formulas.

G2
14
G3
21
G4
0

The text ID "007" stays distinct from numeric 7. A price of 0 is a successful match. First means source order, not the oldest or newest date.

Download this example's workbook
Return the last matching row

Search mode -1 starts at the bottom. The same A-1 query now returns 99 from the later source row; all other matching rules stay the same.

=XLOOKUP(F2,$A$2:$A$7,$C$2:$C$7,"Not found",0,-1)

Enter in G2 in this example's workbook. The download contains the complete source and all result formulas.

G2
99
G3
21
G4
0

Last means the last row in this range. It does not choose the latest date unless the source has already been ordered that way. The separate primary practice still asks for the first match.

Download this example's workbook
UNDERSTAND THE IDEA

How it works

Duplicate IDs need a policy

This formula searches from top to bottom and returns the first match. It does not warn you when an ID appears twice. Check for duplicates before using a catalogue where IDs are expected to be unique.

Exact does not mean case-sensitive

Text IDs are matched without regard to letter case. Number 7 and text "007" are different IDs. Keep identifier types consistent and do not strip meaningful leading zeros.

Version and scope

XLOOKUP is not available in every older version of Excel. This lesson uses exact matches on a small table, not approximate tiers or wildcard searching. Compatibility with this browser engine, Microsoft Excel and Google Sheets is checked separately.

Read the full step-by-step walkthrough
  1. Choose the search value

    F2 contains the ID to find. Keep it separate from the catalogue so it can be changed easily.

    F2
  2. Search the ID column

    Only product IDs belong in the lookup array. Both arrays start and end on the same rows.

    $A$2:$A$7
  3. Return the corresponding price

    The return array is the Price column. A match on row five returns the value on row five of this range.

    $C$2:$C$7
  4. Make a missing ID readable

    The fourth argument provides a clear response when nothing matches. A real zero price remains the number zero.

    "Not found"
  5. Choose exact matching and fill down

    The fifth argument is 0. Lock the catalogue ranges and let F2 move to F3 and F4 when copied.

    0
WHEN SOMETHING LOOKS OFF

Things to watch

The wrong column is returned

The third argument should be the Price range, not the product name or the ID range.

A missing ID returns a nearby item

Approximate matching answers a different question. Use exact match mode 0 for identifiers.

A repeated ID returns the later value unexpectedly

This task expects the first match. If your data needs the last or all matches, define that rule separately.

About this example

Published examples, formula filling and practice checks were tested in Chrome on October 13, 2026. Tests cover the supplied datasets and stated input rules, not every possible formula. These workbooks have not yet been independently recalculated in Microsoft Excel or Google Sheets.

Browser: testedExcel: not verifiedGoogle Sheets: not verified

Content version 2. Practice checks compare calculated results across different datasets; they do not certify every possible formula.

Reference material