Looking up values with XLOOKUP or VLOOKUP

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

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)

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

3. Which formula does an exact match with VLOOKUP?

A =VLOOKUP(E2,A2:C20,3,FALSE)

B =VLOOKUP(E2,A2:C20,3,TRUE)

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

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

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1=XLOOKUP(A2,F2:F10,G2:G10)		ALooks for A2 in the first column of F2:G10 and returns the value from column 2, using an exact match
2=VLOOKUP(A2,F2:G10,2,FALSE)		B=VLOOKUP(A2,F2:G10,2,FALSE)
3Show a friendly message when the lookup key is not found in the table		CThe 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.
4Use an exact match so a product code only matches an identical code		D=XLOOKUP(A2,F2:F10,G2:G10,"Not found")
5=VLOOKUP(A2,F2:G10,2)		EFinds the product code in A2 within F2:F10 and returns the matching price from G2:G10
6=XLOOKUP(A2,G2:G10,F2:F10)		FThe match type is missing, so VLOOKUP defaults to an approximate match and may return the wrong price. Add FALSE to make it exact.

Definitions

XLOOKUP

Fill in the Blanks

Complete each line of code by filling in the missing parts.

=_______(A2,Products!A:A,Products!C:C)

=XLOOKUP(__,F2:F10,G2:G10,"Not found")

=XLOOKUP(A2,F2:F10,______,"Not found")

=VLOOKUP(A2,F2:G10,2,_____)

=_______(VLOOKUP(A2,F:G,2,FALSE),"None")

Spot the Error

Shade in the letter of the line that contains an 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)

Question 2

A Table A2:B6 holds code, price

B =VLOOKUP(D2,A2:B6,3,FALSE)

C Column 2 returns the price

Question 3

A Look up a student ID exactly

B =VLOOKUP(F2,A2:C30,2,TRUE)

C Column 2 holds the name

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

Question 5

A Codes A2:A5, prices B2:B5

B D2: P103

C E1: Price

D E2: =XLOOKUP(D2,A2:A5,B2:B6)

Answer Key

Looking up values with XLOOKUP or VLOOKUP

Section A: Quick Questions

A — =XLOOKUP(E2,A2:A20,C2:C20)

B — E2, the value you are searching for

A — =VLOOKUP(E2,A2:C20,3,FALSE)

A — Return the value from the 3rd column of the table

B — #N/A

Section B: 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.

Section C: Definitions

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.

Section D: Fill in the Blanks

=XLOOKUP(A2,Products!A:A,Products!C:C)

=XLOOKUP(A2,F2:F10,G2:G10,"Not found")

=VLOOKUP(A2,F2:G10,2,FALSE)

=IFERROR(VLOOKUP(A2,F:G,2,FALSE),"None")

Section E: Spot the Error

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).

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.

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.

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".

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.