Sheetsdrill

Level 3: Lookups Exercise 51 of 92

Is the code on the list?

A Google Sheets formula exercise from Sheetsdrill. Solve it with a formula bar on a live sheet in your browser, and it is checked the moment you submit.

The task

Check each scanned code against the approved list in E2:E7. Show "Listed" when the code is on the list and "Missing" when it is not. Fill B2 and B3 with formulas that could be filled down.

What this exercise teaches

When you only need to know whether a value exists, counting it is simpler than a lookup: there is no #N/A to handle. Lock the list so the formula can be filled down.

A hint if you get stuck

COUNTIF($E$2:$E$7, A2) is 0 when the code is not on the list. Use that as the test of an IF.

Functions used: COUNTIF, IF

Try it now, free, no account needed

Your first 15 exercises on Sheetsdrill are free. Open it on the practice page, solve it, and get instant feedback with a hint. A free account adds the worked solution; Pro unlocks every exercise, timed mode and the certificate.

Open this exercise

More exercises in Lookups