Ravindra BagaleCourses & study guides Track your progress
Medium challenge

Medium: HLOOKUP TRUE on unsorted thresholds

By · Free ·

Absolute refsNumbers as textLeading spacesFilter left onChart range% vs decimalDuplicate headerROUND vs displayFreeze panesPaste valuesVLOOKUP & textVLOOKUP col indexHLOOKUP TRUESUMIF spacesCOUNTIFS ORIF nest orderDate serialINDEX MATCHWildcard COUNTIFMixed currencyCircular & datesCircular interest3D sum renameNamed range staleCF vs IFPivot staleHard-coded NPVValidation movedPQ step orderTyped-over formulaSUM wrong anchorsFiscal year Apr–MarFind all bugsInterview oral

Score→grade card uses HLOOKUP(…,TRUE) but thresholds are unsorted. Score 78 returns #N/A (or nonsense). Fixed file uses nested IF bands → B (LibreOffice verified).

About this data: Made-up score card for Arjun Mehta (Jaipur) and Sofia Rossi (Rome). Practice only.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Approximate HLOOKUP/VLOOKUP needs ascending keys. Unsorted tables silently mis-grade.

What: Broken HLOOKUP TRUE → #N/A for 78. Fixed nested IF → B.

How:

  1. Open Card.
  2. Inspect threshold order.
  3. Replace with IF bands testing high cutoffs first.
  4. Confirm grade B for 78.

Ravindra Bagale’s Tip

If you must use TRUE, sort thresholds ascending and test edge scores (59,60,74,75).

Bugs explained (with mini examples)

Bug 1 — TRUE on unsorted keys

Approx match requires ascending order.

Bug 2 — Opaque grade logic

Nested IF (high→low) is clearer for four bands.

Practice tasks (12)

  1. Task 1. What does broken B8 show?
    Hint: #N/A or wrong.
  2. Task 2. Expected grade for 78?
    Hint: B.
  3. Task 3. List bands.
    Hint: F<60, C≥60, B≥75, A≥90.
  4. Task 4. Write nested IF.
    Hint: IF≥90 A …
  5. Task 5. Test score 60.
    Hint: C.
  6. Task 6. Test 90.
    Hint: A.
  7. Task 7. Interview line.
    Hint: “Approx lookup on unsorted grades.”
  8. Task 8. Why avoid HLOOKUP TRUE in class?
    Hint: Fragile.
  9. Task 9. Sort keys if keeping TRUE.
    Hint: 0,60,75,90.
  10. Task 10. Does FALSE need exact score key?
    Hint: Yes.
  11. Task 11. Bonus: lookup table vertical.
    Hint: VLOOKUP/MATCH.
  12. Task 12. Document bands in a note.
    Hint: A9.

In-browser formula check

Type the fixed formula you would put in the named cell. We check formula text — open the workbook for full recalculation.

Step-by-step solution (try first)
  1. Use nested IF high-to-low.
  2. 78 → B. LO: broken #N/A, fixed B.

Quick quiz

Q1. HLOOKUP TRUE requires keys:
Q2. Fixed grade for 78:
Q3. Broken LO result was:
Q4. Test high thresholds first when nesting IF:
Q5. Approx match on unsorted data is: