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.
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 exerciseMore exercises in Lookups
- Find the price
- Stock on hand
- Look left with XLOOKUP
- A friendly "Not found"
- INDEX and MATCH
- Zero instead of #N/A
- Shipping rate by weight band
- Row and column lookup
- Latest status in a log
- Commission rate tier
- Targets laid out in a row
- Lookup by the start of a name
- Next sailing on or after a date
- Lookup on two keys
- Place in the queue