← VCE Spreadsheets, SQL & Visualisation

Looking up values with XLOOKUP or VLOOKUP

A lookup function that finds a key in one column and returns the matching value from another column, such as a price for a product code. It can show a message if no match is found.

Part of: VCE Applied Computing - U1O1 - Data analysis — Module 17, VCE Data Analytics - U3O1 - Data Analytics — Module 70, VCE Data Analytics - U4O1 - Development and Evaluation — Module 11, 9-10 Digital Technologies: Acquiring, storing and validating data — Module 14, ACARA Digital Technologies Years 9–10 — Analysing and Visualising Data — Module 25

Understanding Looking up values with XLOOKUP or VLOOKUP in VCE Spreadsheets, SQL & Visualisation

What it does: The fourth argument of XLOOKUP is the text shown when there is no match, instead of an error. FALSE as the last argument makes VLOOKUP match exactly, so a similar code will not be returned by mistake. The lookup key (E2) comes first, then the column to search (A2:A20), then the return column (C2:C20) that holds the price. In VLOOKUP, the number is the column index. It says which column of the table to return a value from. The 'not found' message is the fourth argument, after the return column. Putting it in the third position would make it the return range.

Common mistakes: The lookup array and the return array are swapped. The key P101 is searched for in the Price column, so nothing matches. It should search A2:A6 (codes) and return B2:B6 (prices). The table A2:B6 only has two columns, so column 3 does not exist and gives a #REF! error. The return column number should be 2 to get the price.

Class: _____________________  ·  Name: _____________________  ·  Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Looking up values with XLOOKUP or VLOOKUP to a class →

This module's key terms are also in a printable crossword (Modules 4–20) and word search (Modules 4–34).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning
Duo
A shop sheet has product codes in A2:A20 and prices in C2:C20. Which formula finds the price for the code in E2?
=XLOOKUP(C2,A2:A20,E2:E20)=XLOOKUP(E2,A2:A20,C2:C20)
Matches
=VLOOKUP(A2,F2:G10,2)
↓
The match type is missing, so VLOOKUP defaults to an approximate match and may return the wrong price. Add FALSE to make it exact.
Applying
Meanings
Term
XLOOKUP
Meaning
A lookup function that finds a key in one column and returns the matching value from another column, such as a price for a product code. It can show a message if no match is found.
Practice
=XLOOKUP(D2,A2:A20,B2:B20,"Not found",0)
Testing
Gaps
=(A2,Products!A:A,Products!C:C)
Check Answer
Spots
3D2: P101
4E2: =XLOOKUP(D2,B2:B6,A2:A6)

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

The 'Hermit Crab' badge — Definition Collector, one of Cloodit's 106 collectible badges
Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy
Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games
Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print
Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. A shop sheet has product codes in A2:A20 and prices in C2:C20. Which formula finds the price for the code in E2?
A. =XLOOKUP(E2,A2:A20,C2:C20) ✓
B. =XLOOKUP(C2,A2:A20,E2:E20)
The lookup key (E2) comes first, then the column to search (A2:A20), then the return column (C2:C20) that holds the price.
2. In =XLOOKUP(E2,A2:A20,C2:C20), what is the lookup key?
A. C2:C20, the prices that come back
B. E2, the value you are searching for ✓
The lookup key is the value you want to find. C2:C20 is the return column, which holds the answer.
3. Which formula does an exact match with VLOOKUP?
A. =VLOOKUP(E2,A2:C20,3,FALSE) ✓
B. =VLOOKUP(E2,A2:C20,3,TRUE)
FALSE tells VLOOKUP to find only an exact match. TRUE allows an approximate match, which can return the wrong row in an unsorted table.
4. In =VLOOKUP(E2,A2:C20,3,FALSE), what does the number 3 mean?
A. Return the value from the 3rd column of the table ✓
B. Look for the key in row 3 of the table
In VLOOKUP, the number is the column index. It says which column of the table to return a value from.
5. A student types a code that is not in the product list. What does XLOOKUP show if you give it no 'not found' message?
A. 0
B. #N/A ✓
If there is no match, the lookup gives the #N/A error. It does not quietly return 0 or a blank.

Match the Pairs

=XLOOKUP(A2,F2:F10,G2:G10) → Finds the product code in A2 within F2:F10 and returns the matching price from G2:G10
=VLOOKUP(A2,F2:G10,2,FALSE) → Looks for A2 in the first column of F2:G10 and returns the value from column 2, using an exact match
Show a friendly message when the lookup key is not found in the table → =XLOOKUP(A2,F2:F10,G2:G10,"Not found")
Use an exact match so a product code only matches an identical code → =VLOOKUP(A2,F2:G10,2,FALSE)
=VLOOKUP(A2,F2:G10,2) → The match type is missing, so VLOOKUP defaults to an approximate match and may return the wrong price. Add FALSE to make it exact.
=XLOOKUP(A2,G2:G10,F2:F10) → The lookup array is the price column instead of the product code column, so the code in A2 will not be found. Search F2:F10 and return G2:G10.

Key Term

XLOOKUP
A lookup function that finds a key in one column and returns the matching value from another column, such as a price for a product code. It can show a message if no match is found.

Fill in the Blanks

1. =XLOOKUP(A2,Products!A:A,Products!C:C)
Hint: Type the function name that finds a key in one column and returns the matching value from another column.
2. =XLOOKUP(A2,F2:F10,G2:G10,"Not found")
Hint: Type the cell reference that holds the lookup key to search for, such as a product code.
3. =XLOOKUP(A2,F2:F10,G2:G10,"Not found")
Hint: Type the range of the return column that holds the values to give back, such as prices.
4. =VLOOKUP(A2,F2:G10,2,FALSE)
Hint: Type the logical value that makes VLOOKUP use an exact match only.
5. =IFERROR(VLOOKUP(A2,F:G,2,FALSE),"None")
Hint: Type the function name that wraps a lookup and shows a friendly result when no match is found.

Spot the Error

Question 1
A A1: Code, B1: Price
B A2: P101, B2: 4.50
C D2: P101
D E2: =XLOOKUP(D2,B2:B6,A2:A6)
Line D — The lookup array and the return array are swapped. The key P101 is searched for in the Price column, so nothing matches. It should search A2:A6 (codes) and return B2:B6 (prices).
Question 2
A Table A2:B6 holds code, price
B =VLOOKUP(D2,A2:B6,3,FALSE)
C Column 2 returns the price
Line B — The table A2:B6 only has two columns, so column 3 does not exist and gives a #REF! error. The return column number should be 2 to get the price.
Question 3
A Look up a student ID exactly
B =VLOOKUP(F2,A2:C30,2,TRUE)
C Column 2 holds the name
Line B — TRUE asks for an approximate match, which can return the wrong student. An ID needs an exact match, so the last argument should be FALSE.
Question 4
A Price lookup with a message
B D2: P999
C =XLOOKUP(D2,A2:A6,B2:B6,N/A)
D Shows N/A if P999 is missing
Line C — The not-found message is text, so it must be in quotes. Without quotes, N/A is not a valid value. It should be "N/A".
Question 5
A Codes A2:A5, prices B2:B5
B D2: P103
C E1: Price
D E2: =XLOOKUP(D2,A2:A5,B2:B6)
Line D — The lookup range has 4 rows (A2:A5) but the return range has 5 (B2:B6). XLOOKUP needs both ranges to be the same size. The return range should be B2:B5.

Student with a class code? Join your class →