Ravindra BagaleCourses & study guides Track your progress
Medium challenge

Medium: VLOOKUP, spaces & text numbers

By · Free ·

EasyMediumHard

Two sheets: Lookup (grades + product codes) and Bonus (monthly scores). Bugs: trailing space in grade key → #N/A; VLOOKUP with TRUE on an unsorted price list; scores pasted as text so SUM skips them; IF uses >80 so score 80 gets No.

About this data: Made-up HR rates for Neha Kulkarni (Bengaluru) and Emma Wilson (London contractors), plus product codes. Practice only — not real payroll or prices.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Paste from HR portals often adds trailing spaces. Approximate VLOOKUP looks “almost right” until one code is wrong. Text numbers break SUM and comparisons.

What: Fix Amit’s A key (TRIM), stop using TRUE on unsorted codes, convert Emma/Omar scores to numbers, change IF to >=80.

How:

  1. Open sheet Lookup. Find Amit Shah’s #N/A in Rate_found.
  2. Clear the trailing space in Grade_key (or wrap lookup with TRIM).
  3. In the product block, notice TRUE approximate match — order code 300 returned the wrong price in LibreOffice on the unsorted list.
  4. Sort the code table ascending or switch to FALSE and only look up exact codes.
  5. Open sheet Bonus. Check which scores are left-aligned text.
  6. Convert with VALUE / multiply by 1 / Paste Special → Add. Fix IF to >=80.

Ravindra Bagale’s Tip

For IDs and grades always use VLOOKUP … FALSE (or 0). Save TRUE for sorted numeric slabs only.

Bugs explained (with mini examples)

Bug 1 — Trailing space → #N/A

Key in cell: "A " (A + space) =VLOOKUP(E8,$A$6:$B$9,2,FALSE) → #N/A Fix key with TRIM or retype "A"

Bug 2 — Approximate match on unsorted codes

Broken list order: 200,100,500,300 with TRUE Lookup 300 can return a wrong neighbour price Fix: sort ascending + TRUE only if you need bands, else FALSE

Bug 3 — Numbers stored as text

Scores: 85, "78", 80, "92", 79 Broken SUM → 244 (skips text) Fixed SUM → 414

Bug 4 — IF uses >80 instead of >=80

=IF(B6>80,"Bonus","No") with 80 → No =IF(B6>=80,"Bonus","No") → Bonus

Practice tasks (12)

  1. Task 1. Find which staff row returns #N/A and why.
    Hint: Look at Grade_key characters.
  2. Task 2. Fix Amit’s key so Rate_found is 900.
    Hint: TRIM or delete the space.
  3. Task 3. Rewrite Rate_found formulas with FALSE and locked table.
    Hint: $A$6:$B$9,2,FALSE
  4. Task 4. On the product block, change TRUE to FALSE for code 200.
    Hint: Exact match returns 20.
  5. Task 5. Explain what went wrong for order code 300 in the broken file.
    Hint: TRUE + unsorted list.
  6. Task 6. On Bonus, mark which Score cells are text.
    Hint: Often left-aligned or show green mark.
  7. Task 7. Convert text scores so SUM equals 414.
    Hint: VALUE or *1.
  8. Task 8. Change Bonus_IF so score 80 earns Bonus.
    Hint: Use >= not >.
  9. Task 9. What should Emma (78) show after the IF fix?
    Hint: No
  10. Task 10. Wrap a missing grade with IFNA to show Missing.
    Hint: =IFNA(VLOOKUP(...),"Missing")
  11. Task 11. Write one interview sentence: exact vs approximate VLOOKUP.
    Hint: FALSE for codes; TRUE only on sorted slabs.
  12. Task 12. Bonus: use TRIM inside VLOOKUP for Grade_key.
    Hint: =VLOOKUP(TRIM(E6),…)

In-browser formula check

Type the fixed formula you would put in the named cell (Excel or LibreOffice style). We only check the formula text — open the workbook for full recalculation.

Step-by-step solution (try first)
  1. Change Amit’s Grade_key from A to A (or TRIM in the formula).
  2. Keep VLOOKUP range_lookup = FALSE for grades.
  3. Sort product codes 100,200,300,500; use FALSE for exact order codes; do not approx-match 150.
  4. Bonus: convert "78" and "92" to numbers; IF >=80; SUM → 414; Amit → Bonus; Emma → No.

LibreOffice verified: Amit #N/A→900; order 300 wrong price 10→30; Bonus SUM 244→414.

Quick quiz

Q1. Trailing space in a lookup key with FALSE usually returns:
Q2. VLOOKUP range_lookup TRUE is safest when:
Q3. Numbers stored as text are often ignored by:
Q4. For a passing score of 80 inclusive use:
Q5. TRIM helps when: