Day 8 — VLOOKUP and why it disappoints

What VLOOKUP does

You have a value. You want the matching value from another column of the same table. VLOOKUP finds the row and fetches across.

Every formula on this page runs against the Sales sheet below. The row numbers and column letters are real — F2 really is 165000.

Order 1009 was worth 220,000, came from the West, and was for Laptops. The column number counts from the left edge of the table you gave it, not from column A of the sheet — here column 6 is F because the table starts at A.

Always write FALSE

Where approximate match is the right tool

Grading, tax slabs, discount bands — anywhere a range of values maps to one result. The lookup column must be sorted ascending.

In practice, for banding, IFS is clearer than an approximate VLOOKUP unless you have many bands stored in their own table.

Handling "not found" properly

IFNA is the more careful choice: it catches "not found" but still lets genuine errors such as #REF! show through, so a broken formula does not disguise itself as a missing record.

Limitation 1 — it only looks right

VLOOKUP searches the first column of your table and returns something to the right of it. It cannot look leftwards.

The first works because the table starts at D, so Product is the first column. The second returns #N/A — it searched column A for "Mouse" and never looked at column D at all.

Limitation 2 — the column number is a hard-coded guess

Six means "the sixth column". Insert a new column anywhere inside that table and the sixth column becomes something else — the formula keeps working and quietly returns the wrong field. Asking for column 7 when only 6 exist gives #REF!.

Limitation 3 — exact means exact

This lesson uses the Raw sheet below — deliberately messy, the way exported data really arrives.

Looking up "amit sharma" fails, because the stored value has spaces around it. Looking up the cell itself works, because it matches itself exactly. This is the number one cause of #N/A in real workbooks, and TRIM is usually the cure.

Tomorrow

INDEX and MATCH solve all three limitations, and XLOOKUP solves them with less typing. VLOOKUP is still worth knowing — you will meet it in every inherited workbook you ever open.