VLOOKUP & the Power of Lookups¶

A lookup finds a value in one table and pulls back related info — like looking up a price by product code. VLOOKUP is the classic.

Two related tables with values being matched between them

The VLOOKUP Syntax¶

excel

1 line

1=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Argument	Meaning
`lookup_value`	What you're searching for
`table_array`	The table to search (1st col = keys)
`col_index_num`	Which column # to return
`range_lookup`	`FALSE` = exact, `TRUE` = approximate

A Worked Example¶

Price table (E:F)         Lookup
| Code | Price |          =VLOOKUP("P02", E2:F4, 2, FALSE)
| P01  |  10   |          → 25
| P02  |  25   |
| P03  |  40   |

"Find P02 in the first column, return the value from column 2, exact match."

Caution

Almost always use FALSE (exact match) as the last argument. TRUE assumes your data is sorted ascending and silently returns wrong results if it isn't.

VLOOKUP's Limitations¶

It can only look right — the key must be the leftmost column.
col_index_num is a fragile number; insert a column and it breaks.
It re-scans from the left each time on big data.

| Name | ID |        ❌ Can't VLOOKUP a Name from an ID here —
                        the ID is to the RIGHT of Name.

Wrapping with IFERROR¶

When a lookup finds nothing it returns #N/A. Make it friendly:

excel

1 line

1=IFERROR(VLOOKUP(A2, Prices, 2, FALSE), "Not found")

These limitations are exactly why Excel introduced XLOOKUP and INDEX/MATCH — coming up next.