Excel lookup formulas

1.1 Understand the VLOOKUP pattern

VLOOKUP searches down the first column of a table and returns a value from another column on the same row. It is a good fit when the lookup key is the leftmost column of your data.

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

1. Put product IDs in A2:A4, product names in B2:B4, and prices in C2:C4.
2. Enter the ID to find in D2.
3. Search the table, return its second column, and request an exact match with FALSE.

To return the product name for the ID in D2:

=VLOOKUP(D2, A2:C4, 2, FALSE)

D2 is the key; A2:C4 is the table; 2 means the second column in that selected table (Product); and FALSE requests an exact match. With P-102 in D2, the result is Mouse.

Common pitfall: If you omit the last argument, VLOOKUP may use approximate matching. For ordinary IDs, names, or codes, use FALSE.

1.2 Keep the source range fixed when copying

A relative reference changes as a formula is copied; an absolute reference marked with $ stays put. Usually the input cell should move down each row, while the lookup table should remain fixed.

=VLOOKUP(D2, $A$2:$C$4, 2, FALSE)

1. Enter the formula in E2.
2. Copy it down to E3. The lookup cell becomes D3.
3. The table remains $A$2:$C$4, so every row searches the same list.

Use the reference shortcut in Excel while editing a reference to cycle through relative and absolute forms. The dollar signs lock both the columns and rows in this example.

1.3 Handle missing matches with IFERROR

If the key is absent, an exact-match VLOOKUP returns an error. Wrap the lookup in IFERROR when you want a readable fallback.

=IFERROR(VLOOKUP(D2, $A$2:$C$4, 2, FALSE), "Product not found")

IFERROR(value, value_if_error) returns the lookup result when it works and the message when the formula returns an error. Because it catches any error, use it when a friendly fallback is appropriate—not to hide mistakes you still need to diagnose. See the IFERROR tutorial.

2.1 Set up XLOOKUP

XLOOKUP takes a lookup range and a return range separately. The ranges line up item by item, so you don't count columns in a table.

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

1. Look for the product name in B2:B4.
2. Return the value from the corresponding row in C2:C4.
3. Choose an optional message to display if there is no match.

=XLOOKUP(D2, B2:B4, C2:C4, "Product not found")

Using the Keyboard/25, Mouse/12, Monitor/180 table from Level 1, if D2 is Mouse, the result is 12. XLOOKUP uses exact matching by default and returns the first match.

2.2 Look left and handle not-found results

Unlike VLOOKUP, XLOOKUP can return a column to the left of the search column. For example, if names are in A2:A4 and IDs are in B2:B4, use the ID to find its product name:

=XLOOKUP(D2, B2:B4, A2:A4, "ID not found")

Here, IDs are in B2:B4 and names are in A2:A4. If D2 contains P-102, the result is Mouse. XLOOKUP's if_not_found argument handles a missing key directly, so an IFERROR wrapper is usually unnecessary for that case.

2.3 Return several columns (spilled ranges)

A return array can contain more than one column. For example, look up a product ID in A2:A4 and return the matching product name and price from B2:C4:

=XLOOKUP(E2, A2:A4, B2:C4, "Product not found")

When E2 contains P-102, Mouse and 12 fill the cells to the right of the formula. Excel calls this a spill. Leave those destination cells empty; if something blocks them, Excel reports a spill error. Older Excel versions without dynamic arrays cannot return the range this way.

3.1 Use MATCH to find a position

MATCH does not return the item itself. It returns the item's relative position inside the range you give it. Use 0 for an exact match.

=MATCH("Mouse", B2:B4, 0)

If B2:B4 contains Keyboard, Mouse, Monitor, this returns 2: Mouse is second within that range (not row 2 of the worksheet).

3.2 Use INDEX to retrieve a value

INDEX returns the value at a position in a range. For a one-column range, the second argument is the position.

=INDEX(C2:C4, 2)

If C2:C4 contains 25, 12, 180, this returns 12, the second value. Think of MATCH as finding where and INDEX as retrieving what.

3.3 Combine INDEX and MATCH

Use the position found in the key list to retrieve the corresponding value from the return list:

=INDEX(C2:C4, MATCH(D2, B2:B4, 0))

1. MATCH(D2, B2:B4, 0) finds the exact product name's position.
2. INDEX(C2:C4, ...) returns the value at that same position.

With product names in B2:B4, prices in C2:C4, and Mouse in D2, the formula returns 12. This pattern can look left or right because the key range and result range are independent.

3.4 Find a row-and-column intersection

A two-dimensional lookup uses one MATCH to find a row and another to find a column. INDEX returns the value where they intersect.

With region names in A2:A4, month labels in B1:D1, values in B2:D4, the requested region in G2 and month in G3:

=INDEX(B2:D4, MATCH(G2, A2:A4, 0), MATCH(G3, B1:D1, 0))

The first MATCH finds the row position; the second finds the column position. For North and Feb, INDEX returns 110.

4.1 Match multiple criteria with Boolean logic

Suppose a sales list has names in A2:A100, departments in B2:B100, and sales amounts in C2:C100. A name can appear in more than one department, so use both conditions to identify the right row. G2 contains the name and H2 the department.

=XLOOKUP(1, (A2:A100=G2)*(B2:B100=H2), C2:C100, "No match")

1. A2:A100=G2 checks the name on every row and produces TRUE/FALSE values.
2. B2:B100=H2 checks the department.
3. Multiplying the checks acts like AND: only a row where both are true becomes 1.
4. XLOOKUP finds 1 and returns that row's amount.

This returns the first row that meets both conditions. If multiple matching rows should be added together, use an aggregation instead.

4.2 Use SUMIFS when the answer should be a total

A lookup retrieves a value from a matching row. SUMIFS adds values across all rows that meet one or more conditions.

=SUMIFS(C2:C100, A2:A100, G2, B2:B100, H2)

This totals sales in C2:C100 where the name equals G2 and the department equals H2. See the SUMIFS tutorial. Use COUNTIFS to count matching rows instead of summing them; see the COUNTIFS tutorial.

4.3 Choose SUMIFS or a PivotTable

• Choose a lookup to return a specific related value, such as the price for one product ID.
• Choose SUMIFS for a formula-driven total with a small, known set of criteria.
• Choose a PivotTable to explore and summarize many categories—such as sales by department and month—without writing a separate formula for every group. Select the data, choose Insert > PivotTable, then place fields in Rows, Columns, Values, and Filters.

These tools answer different questions; a PivotTable is a summary, not a direct replacement for a lookup that retrieves one record.