Challenge 2: Looking Left with INDEX/MATCH — Solution Walkthrough

Table (rearranged): A1:A5 = prices (9.99, 14.50, 22.00, 7.25),
B1:B5 = codes (SKU-100, SKU-200, SKU-300, SKU-400) — the price is now
to the LEFT of the code.

Formula:
  =INDEX(A1:A5, MATCH("SKU-200", B1:B5, 0))

Result: 14.50

Walkthrough:
  1. MATCH("SKU-200", B1:B5, 0) searches B1:B5 for an exact match to
     "SKU-200" and finds it in the 2nd position, so MATCH returns
     the plain number 2.
  2. INDEX(A1:A5, 2) then returns whatever is in the 2nd position of
     A1:A5 — which is 14.50.
  3. MATCH's lookup range (B1:B5) and INDEX's return range (A1:A5)
     are completely independent of one another — INDEX doesn't care
     that the return range sits to the LEFT of where MATCH searched.

Why plain VLOOKUP cannot solve this version at all:

VLOOKUP requires the value it searches for to be in the FIRST
(leftmost) column of whatever range you give it, and it can only
return a value from a column to the RIGHT of that first column —
this is a hard structural rule of how VLOOKUP works, not just a
default setting that can be changed with an argument. With the code
in column B and the price in column A, the value you want RETURNED
is to the left of the value you're searching BY — there is no way to
express that with VLOOKUP alone, no matter what arguments are passed.
The table would have to be physically rearranged (price moved to the
right of the code) before VLOOKUP could touch it.

Notes:
- This exact left-lookup case is the textbook reason experienced
  Excel users reached for INDEX/MATCH instead of VLOOKUP for years —
  it has nothing to do with performance or preference, it's a
  genuine capability VLOOKUP structurally lacks.