XLOOKUP function in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to look up the value in cell A2 within the range B2:B10 and return the corresponding value from C2:C10.

Excel

=XLOOKUP([1], B2:B10, C2:C10)

Drag options to blanks, or click blank then click option'

AC2

BB2

CA2

DD2

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong cell reference as the lookup value.

Confusing the lookup array with the return array.

The first argument in XLOOKUP is the value to find. Here, it should be the value in cell A2.

2fill in blank

medium

Complete the formula to return "Not found" if the lookup value in A5 is not found in the range B2:B10.

Excel

=XLOOKUP(A5, B2:B10, C2:C10, [1])

Drag options to blanks, or click blank then click option'

A"Not found"

B"Missing"

C0

D"Error"

Attempts:

3 left

💡 Hint

Common Mistakes

Leaving the fourth argument empty, which returns an error if not found.

Using a number instead of a text message.

The fourth argument in XLOOKUP is the value to return if the lookup value is not found. Here, it should be "Not found".

3fill in blank

hard

Fix the error in the formula to correctly look up the value in A3 in the range B2:B10 and return the matching value from C2:C10.

Excel

=XLOOKUP(A3, [1], C2:C10)

Drag options to blanks, or click blank then click option'

AD2:D10

BC2:C10

CA2:A10

DB2:B10

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the lookup array and return array.

Using a range that does not contain the lookup values.

The second argument is the lookup array where Excel searches for the lookup value. It should be B2:B10.

4fill in blank

hard

Fill both blanks to create a formula that looks up the value in A4 in B2:B10 and returns the matching value from C2:C10, or "Not found" if missing.

Excel

=XLOOKUP([1], [2], C2:C10, "Not found")

Drag options to blanks, or click blank then click option'

AA4

BB2:B10

CC2:C10

DD2:D10

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up the lookup array and return array.

Using the wrong cell reference for the lookup value.

The first blank is the lookup value (A4), and the second blank is the lookup array (B2:B10).

5fill in blank

hard

Fill all three blanks to create a formula that looks up the uppercase version of the value in A6 in B2:B10 and returns the matching value from C2:C10, or "Not found" if missing.

Excel

=XLOOKUP([1], [2], [3], "Not found")

Drag options to blanks, or click blank then click option'

AUPPER(A6)

BB2:B10

CC2:C10

DLOWER(A6)

Attempts:

3 left

💡 Hint

Common Mistakes

Using LOWER() instead of UPPER() when uppercase is needed.

Swapping lookup and return arrays.

The first blank converts A6 to uppercase for lookup, the second is the lookup array B2:B10, and the third is the return array C2:C10.