Lookups and References

Connecting Data

Lookup functions find values in one place based on values in another. They're essential for:

  • Pulling data from reference tables
  • Combining information from multiple sources
  • Building dynamic reports
  • Creating connections between sheets

This is where spreadsheets get powerful.

VLOOKUP — The Classic

How It Works

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

Translation:

  • Look for this value
  • In this table (first column)
  • Return the value from this column number
  • Exact match (FALSE) or approximate (TRUE)

Example

You have product prices in one table, orders in another. You want to add prices to orders.

Price table (columns A-B on Sheet2):

ProductPrice
Widget$10
Gadget$25
Gizmo$15

Formula: =VLOOKUP(A2, Sheet2!$A$1:$B$100, 2, FALSE)

Looks up the value in A2, finds it in Sheet2's first column, returns the price from column 2.

VLOOKUP Rules

  1. Lookup value must be in the first column of your table range
  2. Use FALSE for exact match (almost always what you want)
  3. Use absolute references ($) for the table array
  4. Column index is counted from the left of your table range

Common VLOOKUP Errors

#N/A: Value not found. Check for extra spaces, different capitalization, or the value truly not existing.

Wrong value returned: Probably left range_lookup as TRUE (or empty). Use FALSE for exact match.

#REF!: Column index is larger than the table width.

XLOOKUP — The Modern Alternative

Why XLOOKUP Is Better

Available in Excel 365 and Excel 2021+, XLOOKUP solves VLOOKUP's limitations:

  • Lookup column doesn't need to be first
  • Can search right to left
  • Returns a range, not just one cell
  • Better error handling built-in
  • Clearer syntax

Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Example

=XLOOKUP(A2, Sheet2!$A:$A, Sheet2!$B:$B, "Not found")

  • Look for A2
  • In Sheet2 column A
  • Return the corresponding value from column B
  • If not found, return "Not found"

XLOOKUP Advantages

Return any column: =XLOOKUP(A2, Products!$C:$C, Products!$A:$A) Look up in column C, return from column A — works perfectly.

Built-in error handling: =XLOOKUP(A2, Data!$A:$A, Data!$B:$B, "Not found") No need for IFERROR wrapper.

Return multiple columns: =XLOOKUP(A2, Products!$A:$A, Products!$B:$D) Returns values from columns B, C, and D.

INDEX-MATCH — The Power Combo

Why Learn It

INDEX-MATCH works in all Excel versions, is more flexible than VLOOKUP, and is often faster on large datasets.

How It Works

MATCH: Finds the position of a value in a range =MATCH(lookup_value, lookup_array, match_type)

INDEX: Returns a value at a specific position in a range =INDEX(return_array, row_num)

Combined: MATCH finds the row, INDEX returns the value from that row.

Example

=INDEX(Sheet2!$B:$B, MATCH(A2, Sheet2!$A:$A, 0))

  • MATCH finds where A2 appears in Sheet2 column A (returns row number)
  • INDEX returns the value from that row in column B

INDEX-MATCH Benefits

  • Lookup column can be anywhere
  • More robust when columns are inserted/deleted
  • Can match on multiple criteria (with array formulas)
  • Often faster on large datasets

Handling Lookup Errors

IFERROR Wrapper

=IFERROR(VLOOKUP(...), "Not found")

Returns your custom value if the lookup fails.

Troubleshooting #N/A

  1. Extra spaces: Use TRIM on both sides =VLOOKUP(TRIM(A2), ...)

  2. Different capitalization: Use UPPER or LOWER =VLOOKUP(UPPER(A2), ...)

  3. Numbers stored as text: Check both values are same type

  4. Value doesn't exist: That's what #N/A means — value isn't in the lookup table

HLOOKUP — Horizontal Lookup

Same as VLOOKUP, but searches across rows instead of down columns.

=HLOOKUP(lookup_value, table_array, row_index_num, FALSE)

Use when: Your data is arranged with headers in the first row and data in rows below.

Lookup Best Practices

Use Exact Match

Always use FALSE (VLOOKUP/HLOOKUP) or 0 (MATCH) for exact matching unless you specifically need approximate matching.

Use Absolute References

Lock your lookup table reference with $: =VLOOKUP(A2, $D$1:$E$100, 2, FALSE)

Otherwise, the table range shifts when you copy the formula.

Name Your Lookup Tables

Instead of: =VLOOKUP(A2, Sheet2!$A$1:$B$1000, 2, FALSE)

Create a named range "PriceTable" and use: =VLOOKUP(A2, PriceTable, 2, FALSE)

Handle Errors Gracefully

Wrap lookups in IFERROR or use XLOOKUP's built-in error handling.

Multi-Criteria Lookups

SUMIFS for Sum

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)

INDEX-MATCH-MATCH for Two-Dimensional Lookup

Look up both row and column: =INDEX(data_range, MATCH(row_value, row_headers, 0), MATCH(col_value, col_headers, 0))

Concatenate Keys

Create a helper column that combines criteria: =A2&"|"&B2

Then lookup on the combined key.

AI Prompt: Lookup Help

I need to look up data in Excel.

I have:
- [Describe your data table with the values you want to find]
- [Describe what you're looking up and what you want returned]

Constraints:
- [Any special requirements — multiple criteria, different sheets, etc.]

Give me the best formula to use and explain why.

What's Next

You can find data anywhere. Now let's summarize it powerfully.

Next chapter: Pivot tables and summarization — turn raw data into insights in minutes.

Data ManagementPivot Tables and Summarization