Lookup Functions

One of the most common tasks in Excel is pulling data from one table into another. You have a list of order IDs and you need the customer names. You have product codes and you need the prices. You have employee numbers and you need the department. Lookup functions are what connect tables together — they search for a value in one place and return related data from the same row or column. VLOOKUP is the classic entry point, INDEX/MATCH is the professional upgrade, and together they cover almost every data retrieval scenario you will encounter.

VLOOKUP — The Classic Lookup

VLOOKUP stands for Vertical Lookup. It searches down the first column of a table for a value you specify, then moves across that row by a number of columns you choose and returns what it finds there. Think of it as pointing at a row and saying: "find this value, then tell me what is in column 3 of that row."

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

  lookup_value  →  What you are searching for
  table_array   →  The range containing your lookup table
  col_index_num →  Which column number to return (1 = first column)
  range_lookup  →  FALSE for exact match, TRUE for approximate match
                   Always use FALSE unless you specifically need approximate

Step by step: VLOOKUP looks down column 1 (Product ID) for "P002". It finds it in row 3. It then moves 3 columns across and returns the value it finds there — £25.00. The FALSE at the end tells Excel to find an exact match only.

Practical example — order sheet pulling prices from product table:

Order sheet has product code in A2.
Product table is on Sheet2, range A2:C50.

=VLOOKUP(A2, Sheet2!$A$2:$C$50, 3, FALSE)

The $ signs lock the table range so it does not shift when you copy
the formula down through your order rows.

Always wrap VLOOKUP in IFERROR for production use: =IFERROR(VLOOKUP(A2, Sheet2!$A$2:$C$50, 3, FALSE), "Not Found") — so a missing product code shows a clean message instead of #N/A.

VLOOKUP Limitations — What It Cannot Do

VLOOKUP is powerful but has three significant limitations that you will hit quickly in real work:

HLOOKUP — Horizontal Lookup

HLOOKUP is VLOOKUP's horizontal counterpart. Instead of searching down a column, it searches across a row and returns a value from the same column a number of rows below. The syntax is identical — only the direction changes.

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Example — quarterly targets stored in a header row:

       Jan    Feb    Mar    Apr
Target 8000   9500   11000  10000

=HLOOKUP("Mar", A1:E2, 2, FALSE)  →  11000

Looks across row 1 for "Mar", returns the value 2 rows down (row 2).

HLOOKUP is less common than VLOOKUP because most data is stored in rows (vertical tables) rather than columns (horizontal tables). It shares the same limitations. In practice, when you need to look up across a horizontal table, INDEX/MATCH is more flexible.

INDEX and MATCH — The Professional Upgrade

INDEX/MATCH is the combination that overcomes every VLOOKUP limitation. It takes two functions working together — MATCH finds the position of a value in a range, and INDEX uses that position to return a value from any other range. Because they are separate functions, the lookup column does not need to be on the left, and inserting columns cannot break the formula.

Start by understanding each function on its own:

=MATCH(lookup_value, lookup_array, [match_type])
  Returns the POSITION (row or column number) of a value in a range
  match_type: 0 = exact match (use this almost always)

=INDEX(array, row_num, [col_num])
  Returns the VALUE at a specific position in a range

MATCH finds WHERE.   INDEX returns WHAT IS THERE.

The magic of nesting them: MATCH("P002", A2:A5, 0) returns 2. INDEX(C2:C5, 2) returns whatever is in position 2 of column C — which is £25.00. You can swap column C for any other column without changing the MATCH part. And you can look up by any column, not just the leftmost one.

Full INDEX/MATCH pattern — lookup value in A2, table B2:D50:

=INDEX(D2:D50, MATCH(A2, B2:B50, 0))

Look up by Product Name (column B) and return Price (column D)
Works even though the return column D is to the RIGHT of lookup column B
— impossible with VLOOKUP if the lookup column were not leftmost

Left-direction lookup — return something to the LEFT of lookup column:
=INDEX(A2:A50, MATCH(A2, C2:C50, 0))
Look up by Price (column C), return Product ID (column A) ← to the LEFT

Left-direction lookups are simply not possible with VLOOKUP. INDEX/MATCH handles them effortlessly because you define the lookup column and the return column independently.

Two-Way Lookup — INDEX/MATCH/MATCH

You can extend the pattern to look up both a row and a column simultaneously — a two-way lookup. This is useful for finding a value at the intersection of a row header and a column header, like reading from a rate table or a pricing matrix.

=INDEX(table, MATCH(row_value, row_headers, 0), MATCH(col_value, col_headers, 0))

Example — freight rate table by region and weight band:

         0-5kg   5-20kg   20kg+
North    £4.50   £8.00    £14.00
South    £5.00   £9.00    £16.00
East     £4.00   £7.50    £13.00

=INDEX(B2:D4, MATCH("South", A2:A4, 0), MATCH("5-20kg", B1:D1, 0))
→  £9.00   (South row, 5-20kg column)

VLOOKUP vs INDEX/MATCH — Side by Side

Approximate Match — Range Lookups

So far we have always used FALSE (exact match). But VLOOKUP and MATCH also support approximate match — TRUE in VLOOKUP, 1 in MATCH — which is used for banded ranges like tax brackets, commission tiers, and grading scales. The table must be sorted in ascending order for this to work correctly.

Commission tier table (must be sorted ascending):
  Sales Amount   Commission Rate
  0              5%
  50,000         8%
  100,000        12%
  150,000        15%

=VLOOKUP(93000, A2:B5, 2, TRUE)  →  8%
Finds the largest value that is ≤ 93,000 — which is 50,000 — returns 8%

=VLOOKUP(152000, A2:B5, 2, TRUE)  →  15%
152,000 is ≥ 150,000 threshold, returns 15%

Next up — XLOOKUP, the modern replacement for VLOOKUP that is cleaner, more powerful, and fixes every limitation you just learned about.