Logical Functions
Intermediate

IFNA around MATCH

Where is it in the list — or is it not there at all?

Task:

Today's sign-ups need checking against the existing customer list in E2:E6. In B2:B6 give the position of each sign-up in that list, or New when the customer is not on it.

Interactive Spreadsheet

Read about IFNA

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of IFNA

0/3

The same formula in the other shapes it takes at work.

  1. IFNA Function

    Handle #N/A errors specifically using IFNA.

    Core
    Intermediate
  2. IFNA around MATCH

    Where is it in the list — or is it not there at all?

    Intermediate
  3. IFNA as a default inside a calculation

    A missing lookup becomes a sensible default, and the arithmetic carries on.

    Advanced

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

6 rows × 5 columns5 cells you fill in
ABCDE
1Sign-upOn listCustomers
2Orchard GymPine Dental
3Pine DentalQuarry Arts
4Reed StudioOrchard Gym
5Quarry ArtsSlate Cafe
6Tide YogaThorn Legal
What this exercise teachesMay contain the answer

#N/A from a lookup means "not found", which here is real information — a new customer. IFNA turns exactly that case into a label and leaves every other error alone, so a broken range still shows up as #REF! instead of being quietly called New.