Ravindra BagaleCourses & study guides Track your progress
Medium challenge

Medium: Left lookup needs INDEX+MATCH

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

Table is Name | Category | SKU | Price. Looking up SKU-300 with VLOOKUP on C:D returns 890 (Price), not USB Hub. INDEX+MATCH → USB Hub (LibreOffice verified).

About this data: Made-up SKUs for Vikram Singh (Amritsar) and Emily Ng (Singapore). Practice only.
Download broken workbook Download fixed (after you try)

Why → What → How

Why: Real tables are not always lookup-key-first. VLOOKUP cannot look left.

What: Broken B11=890, fixed B11=USB Hub.

How:

  1. Open Products.
  2. See VLOOKUP return Price.
  3. Write INDEX on Name, MATCH on SKU.
  4. Confirm USB Hub.

Ravindra Bagale’s Tip

Default to INDEX+MATCH (or XLOOKUP in Excel) when the return column might move or sit left.

Bugs explained (with mini examples)

Bug 1 — VLOOKUP right-only

Returns Price from col 2 of C:D.

Bug 2 — Rearranging columns to force VLOOKUP

Fragile — teach INDEX+MATCH.

Practice tasks (12)

  1. Task 1. Broken B11?
    Hint: 890.
  2. Task 2. Expected name?
    Hint: USB Hub.
  3. Task 3. Write INDEX+MATCH.
    Hint: INDEX Name, MATCH SKU
  4. Task 4. Lookup SKU-100.
    Hint: Basmati 5kg.
  5. Task 5. Interview sentence.
    Hint: “Needed left lookup.”
  6. Task 6. Why not move Name right of SKU?
    Hint: Breaks other sheets.
  7. Task 7. MATCH type 0 means?
    Hint: Exact.
  8. Task 8. Return Category for SKU-400.
    Hint: Stationery.
  9. Task 9. Avoid XLOOKUP here for LO verify.
    Hint: Yes.
  10. Task 10. Bonus: two-way MATCH.
    Hint: Optional.
  11. Task 11. Document key column.
    Hint: SKU in C.
  12. Task 12. Save fixed copy.
    Hint: Yes.

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. INDEX+MATCH exact.
  2. SKU-300 → USB Hub. LO: 890 → USB Hub.

Quick quiz

Q1. VLOOKUP cannot naturally:
Q2. Fixed name for SKU-300:
Q3. Broken LO result:
Q4. MATCH third arg for exact:
Q5. INDEX first arg here is the: