Excel Formulas Practice: 8 Problems Where the Obvious Answer Is Wrong

Eight problems built around formulas that look correct and are not. Each group opens with the cause behind a whole family of bugs, then gives you a formula and a situation — work out what it returns before opening the answer.

Most Excel pages explain what a function does. That is useful once, and Google will usually show it to you in the search results without a click. What nobody can hand you in a snippet is the thing that actually costs people hours: a formula that runs, returns a number, and is wrong.

That is what this page drills. Four causes produce most spreadsheet bugs — a missing fourth argument in VLOOKUP that turns an exact match into an approximate one, references that slide when you drag, an error value that was misread, and criteria that silently match nothing. Each one is easy to fix and almost impossible to spot if you have not met it before.

Everything here behaves the same in Microsoft 365, Excel 2021 and recent versions; where a function needs a newer version — XLOOKUP does — the explanation says so. Work each problem before opening the answer, and note the follow-up habit each explanation recommends.

Four causes, eight problems

Why VLOOKUP returns #N/A when the value is clearly there

3 problems

Four causes account for almost every #N/A: the fourth argument was left off (so Excel does an approximate match on unsorted data), the lookup value is text while the table holds numbers (or vice versa), there are trailing spaces, or the lookup column is not the leftmost column of the range. Check them in that order — the missing fourth argument is by far the most common, and it is the one that produces a wrong answer rather than an error, which makes it the dangerous one.

How it shows up: "VLOOKUP not working" · "I can see the value but it returns #N/A"

Q1

Column A holds product IDs as text (they were imported from a CSV), and you type the ID directly into the formula as a number. What happens?

=VLOOKUP(1024, A2:C50, 3, FALSE)

(A2 contains the text value "1024")
  1. AIt returns the value from column C — Excel converts automatically
  2. BIt returns #N/A, because the number 1024 never matches the text "1024"
  3. CIt returns #VALUE!
  4. DIt returns 0
▶Show answer & explanation

Answer: B. It returns #N/A, because the number 1024 never matches the text "1024"

🐱 Excel does not coerce types inside a lookup: the number 1024 and the text "1024" are different keys, so the match fails and you get #N/A. The tell-tale sign is that the values look identical on screen but the text one is left-aligned while the number is right-aligned. Fix it either by converting the column (Text to Columns, or =VALUE(A2)) or by matching the type in the formula: VLOOKUP("1024", …) or VLOOKUP(TEXT(1024,"0"), …).

Q2

The lookup runs without an error, but several rows come back with the wrong price. The ID column is not sorted. What is the most likely cause?

=VLOOKUP(A2, Products!A:D, 4)
  1. AThe range should be absolute
  2. BThe fourth argument is missing, so Excel does an approximate match and returns the nearest value below the target
  3. CColumn D contains text
  4. DWhole-column references are not allowed
▶Show answer & explanation

Answer: B. The fourth argument is missing, so Excel does an approximate match and returns the nearest value below the target

🐱 Omitting the fourth argument means TRUE — approximate match — which assumes the lookup column is sorted ascending. On unsorted data it silently returns whatever it lands on, which is why this bug produces plausible-looking wrong numbers instead of an error. Always write FALSE (or 0) unless you deliberately want a banded lookup such as a tax bracket. This is the single most expensive Excel habit there is.

Q3

You want to look up an ID in column C and return the name from column A. What does this return?

=VLOOKUP(F2, A2:D100, 1, FALSE)

(F2 holds an ID; IDs live in column C; names live in column A)
  1. AThe name from column A
  2. B#N/A, because VLOOKUP searches the leftmost column of the range — here that is A, not C
  3. C#REF!
  4. DThe ID itself
▶Show answer & explanation

Answer: B. #N/A, because VLOOKUP searches the leftmost column of the range — here that is A, not C

🐱 VLOOKUP always searches the first column of the range you gave it and can only return values to the right. Since the range starts at A, it searches names for an ID and finds nothing. The two standard fixes: INDEX(A2:A100, MATCH(F2, C2:C100, 0)), which has no left/right restriction, or XLOOKUP(F2, C2:C100, A2:A100) if you have Microsoft 365 or Excel 2021 and later.

The $ signs: why the formula breaks when you drag it

1 problem

A reference without dollar signs moves when you copy the formula. That is what you want for the lookup value (each row should look up its own value) and exactly what you do not want for the lookup table (it must stay put). Nearly every 'works in row 2, breaks by row 20' report is this one bug: the table range slid down with the fill handle until the rows it needed had scrolled out of it.

How it shows up: "It works in the first row but not the rest" · "What does $A$1 mean?"

Q4

You enter this in B2 and drag it down to B100. Rows near the top work; rows near the bottom return #N/A. Why?

=VLOOKUP(A2, Rates!A2:B50, 2, FALSE)
  1. AThe lookup values at the bottom genuinely do not exist
  2. BThe table range is relative, so by row 100 it has slid to Rates!A100:B148 and no longer covers the data
  3. CVLOOKUP cannot be dragged
  4. DThe sheet name needs quotes
▶Show answer & explanation

Answer: B. The table range is relative, so by row 100 it has slid to Rates!A100:B148 and no longer covers the data

🐱 Dragging shifts every relative reference by the same number of rows: Rates!A2:B50 becomes A3:B51, then A4:B52, and so on. Once the window slides past the rows you needed, the match disappears. Lock the table with Rates!$A$2:$B$50 — or better, turn the range into an Excel Table and refer to it by name, which cannot slide at all. Press F4 while editing a reference to cycle through the lock modes.

Reading the error value instead of guessing

2 problems

Each error names its own cause, and learning the four common ones turns debugging from guesswork into a lookup. #N/A means 'not found' — the formula worked, the value was not there. #REF! means a reference no longer exists, usually because a row or column was deleted, or because a column index points outside the range. #VALUE! means a wrong type — text where a number belongs. #DIV/0! is exactly what it says. Wrap with IFERROR only after you know which one you are hiding.

How it shows up: "What does #REF! mean?" · "#VALUE! vs #N/A vs #DIV/0!"

Q5

The range is four columns wide (A to D). What does this return?

=VLOOKUP(F2, A2:D100, 5, FALSE)
  1. A#N/A
  2. B#REF!, because column index 5 points outside a four-column range
  3. C#VALUE!
  4. DThe value from column E
▶Show answer & explanation

Answer: B. #REF!, because column index 5 points outside a four-column range

🐱 The column index counts within the range you supplied, not within the sheet — so 5 is out of bounds for A:D and Excel returns #REF!. This distinction matters when you insert a column: the index does not update, so a formula that read column 3 silently starts reading different data. That is the argument for INDEX/MATCH or XLOOKUP, which reference the return column directly and survive insertions.

Q6

A colleague wraps every lookup in IFERROR so the sheet looks clean. What is the risk?

=IFERROR(VLOOKUP(A2, Rates!$A$2:$B$50, 2, FALSE), "")
  1. ANone — this is best practice
  2. BIt hides #REF! and #VALUE! too, so a genuinely broken formula looks like a legitimately missing value
  3. CIFERROR slows the workbook down noticeably
  4. DIFERROR only works with VLOOKUP
▶Show answer & explanation

Answer: B. It hides #REF! and #VALUE! too, so a genuinely broken formula looks like a legitimately missing value

🐱 IFERROR swallows every error type, not just the one you had in mind. A blank cell then means either 'this ID has no rate yet' or 'someone deleted a column and this formula is broken' — and you cannot tell which. Use IFNA instead when you only want to handle 'not found', and keep the structural errors visible so they get fixed rather than papered over.

SUMIF, COUNTIF and the criteria that silently miss

1 problem

Conditional aggregation fails quietly: a criterion that matches nothing returns 0, which looks like a real answer. Two habits prevent most of it — put comparison operators inside quotes (">100", not >100), and remember that SUMIF takes the range first and the sum range last while SUMIFS takes the sum range first. Getting that argument order backwards is the most common reason a formula that looks right returns zero.

How it shows up: "Why does my SUMIF return 0?" · "SUMIF vs SUMIFS"

Q7

Both of these are meant to total sales above 100. Which one is correct?

=SUMIF(B2:B50, ">100", C2:C50)

=SUMIFS(B2:B50, ">100", C2:C50)
  1. ABoth are correct
  2. BThe first — SUMIF takes (range, criteria, sum_range); SUMIFS takes (sum_range, range, criteria) and so has its arguments in the wrong order here
  3. CThe second — SUMIFS is always preferred
  4. DNeither; the operator must be outside the quotes
▶Show answer & explanation

Answer: B. The first — SUMIF takes (range, criteria, sum_range); SUMIFS takes (sum_range, range, criteria) and so has its arguments in the wrong order here

🐱 The two functions genuinely disagree about argument order, which is a design wart worth memorising rather than reasoning about: SUMIF is (range, criteria, [sum_range]) and SUMIFS is (sum_range, criteria_range1, criteria1, …). The SUMIFS line above passes a criteria string where a criteria range belongs, so it errors or returns nothing useful. If you use SUMIFS for everything — which is a reasonable policy since it handles one condition fine — you only have to remember one order.

Keep going

SQL interview questionsSame hands-on format for the database side: joins, NULL, HAVING and window functions.Numerical reasoning test (20 Q, 20 min)Percentages, ratios and reading data tables under a clock — the employer-test cousin of spreadsheet work.All tests and worked question setsReasoning papers, interview prep and more practice sets.

Excel formulas — FAQ

Why does VLOOKUP return #N/A when I can see the value?

In order of likelihood: the fourth argument is missing (so Excel does an approximate match), the lookup value is a number while the table stores text or vice versa, there are trailing spaces, or the lookup column is not the leftmost column of the range. The type mismatch is the sneakiest — identical-looking values, but text aligns left and numbers align right.

Should I use VLOOKUP, INDEX/MATCH or XLOOKUP?

XLOOKUP if you have Microsoft 365 or Excel 2021 and later: it defaults to exact match, looks in any direction, and survives column insertion. INDEX/MATCH for older versions with the same benefits. VLOOKUP is fine for simple, stable sheets — just always write FALSE as the fourth argument.

What is the difference between #N/A, #REF! and #VALUE!?

#N/A means the formula worked but the value was not found. #REF! means a reference no longer exists — a deleted column, or a column index pointing outside the range. #VALUE! means a type mismatch, usually text where a number belongs. Each names its own cause, which is why reading the error beats guessing.

Why does my formula work in the first row but break further down?

Almost always relative references: the lookup table slid down with the fill handle. Lock it with dollar signs ($A$2:$B$50) or, better, convert the range to an Excel Table and refer to it by name so it cannot slide at all.

Is it bad practice to wrap everything in IFERROR?

It hides structural errors alongside missing values, so a broken formula looks like an empty result. Use IFNA when you only want to handle 'not found', and leave #REF! and #VALUE! visible so they get fixed.