26. Excel Tricks Every Analyst Should Know

26.5 Lookup and Logic Tricks

By Ravindra Bagale · Last updated: 6 October 2026

In short: A lookup shows #N/A for a new store code, an Order ID list has repeats, a manager wants one total that switches city, and an AOV column shows #DIV/0!.

Example first. A lookup shows #N/A for a new store code, an Order ID list has repeats, a manager wants one total that switches city, and an AOV column shows #DIV/0!. Four small formulas fix them.

Trick 17. A lookup that says "Not found" instead of #N/A is shown in the box below; tricks 18–20 follow it.

First look at the data (before → after):

Expected result — After adding the if_not_found argument

F2: =XLOOKUP(E2,$A$2:$A$4,$B$2:$B$4,"Not found")

Trick 18. Flag duplicates with COUNTIF

Before:

After:

B2: =COUNTIF($A$2:$A$7,A2)>1

Steps:

1. In B2 type =COUNTIF($A$2:$A$7,A2)>1 and fill down.
2. TRUE means the ID appears more than once.
3. Filter column B to TRUE to review the duplicates before deleting anything.

Trick 19. One SUMIFS that changes when you change a cell

Before:

After:

F2: =SUMIFS(G2:G11,C2:C11,F1)

Steps:

1. Type a city in F1 (better: a drop-down, see 2.9).
2. In F2 type =SUMIFS(G2:G11,C2:C11,F1).
3. Change F1; the total follows. No new formula needed.

Trick 20. Hide #DIV/0! with IFERROR

Before:

After:

D2: =IFERROR(B2/C2,0)

Steps:

1. In D2 type =IFERROR(B2/C2,0) and fill down.
2. Nagpur, whose only order was cancelled, now shows 0 instead of an error.
3. Use IFERROR only where an error is expected; it also hides real mistakes.

Practice task (try it before you open the answer):

1. Flag the duplicate Order IDs.
2. Make F2 show the Nashik total by changing only F1.
3. Show 0 instead of #DIV/0! for a city with no delivered orders.