XLOOKUP Example With SUM in Excel

With product names in A2:A5, monthly values in B2:D5, and the product to find in E2, use =SUM(XLOOKUP(E2,A2:A5,B2:D5)). If E2 contains Lamp and its values are 200, 175, and 225, the formula returns 600: XLOOKUP finds the matching row, and SUM adds the values returned from that row.

Set up the example data

This example uses one row per product and three columns of monthly figures. Enter the products in A2:A5, January through March figures in B2:D5, and a product name such as Lamp in E2. The relevant row might look like this:

Widget: 120, 135, 145
Cable: 80, 90, 110
Lamp: 200, 175, 225
Chair: 300, 250, 275

Put the formula in F2, or another empty cell. For Lamp, the formula returns the three cells from its row—200, 175, and 225—and SUM adds them to 600. Changing E2 to Chair returns 825. This setup is useful when a lookup identifies a record and the answer should total several values stored across that record.

Keep the lookup list and return range aligned: A2:A5 has four rows, and B2:D5 has the same four rows. XLOOKUP uses the position of the matching product in the first range to choose the corresponding row in the second range.

How XLOOKUP and SUM work together

XLOOKUP searches a lookup array for a value and returns the corresponding item or range from a return array. In the example, E2 is the value to search for, A2:A5 is the search range, and B2:D5 is the range of monthly figures to return. Because the return range has multiple columns, the result for a match is a horizontal array of three values.

SUM then adds those returned values. XLOOKUP does not add them by itself; it finds and returns them. That distinction matters because an ordinary lookup of a single cell followed by SUM may be redundant. For example, if the return range were only B2:B5, XLOOKUP would return one number, and wrapping that one number in SUM would not provide a total across multiple months.

XLOOKUP uses exact matching by default. It also searches from the first item to the last by default, so if the lookup list contains the same product more than once, the first matching row is returned. This is a lookup of one matching record, not a sum of every record with that name.

Enter the formula and adapt it

With the product names in A2:A5, monthly amounts in B2:D5, and the chosen product in E2, enter this in an empty cell:

=SUM(XLOOKUP(E2,A2:A5,B2:D5))

For the sample data, the result for Lamp is 600. To adapt the formula, replace E2 with the cell containing your lookup value, A2:A5 with the lookup list, and B2:D5 with the adjacent values you want returned and added. The return range can contain more than three columns, provided its rows correspond to the lookup range.

In Excel for Microsoft 365 on Windows, select a cell, type the formula in the formula bar or directly into the cell, and press Enter. You do not need to select an output range in advance: SUM consumes the returned values and produces one total. Excel for Mac and Excel for the web use the same formula and comma separators in the usual regional settings; ribbon placement and some interface details can differ, but the formula logic does not.

Use XLOOKUP for multiple chosen items

There is a different case where you want to find a value for each of several selected items and add those results. Suppose the product list is in A2:A5, each product’s single total is in B2:B5, and E2:E3 contains two product names. With Lamp and Chair in E2:E3, enter:

=SUM(XLOOKUP(E2:E3,A2:A5,B2:B5))

For the sample totals, Lamp is 600 and Chair is 825, so this returns 1,425. XLOOKUP evaluates the two lookup values and returns their corresponding values from B2:B5; SUM adds the results. This is useful for adding a short, explicit selection of lookup results, not for adding every transaction that happens to meet a condition.

This formula requires a version of Excel that supports dynamic arrays, such as Microsoft 365 or Excel 2021. If one of the requested names is not found, XLOOKUP normally returns a not-found error and SUM passes that error through. You can decide how to handle missing selections, but do not hide errors automatically unless treating a missing name as zero is appropriate for the data.

When SUMIF or SUMIFS is the better choice

If the goal is to total all rows that match a product, category, person, or other criterion, use SUMIF rather than XLOOKUP. XLOOKUP returns a corresponding match; by default it does not collect every matching row. For instance, a transaction list may contain Lamp several times, and you may want the sum of every Lamp transaction.

Assume transaction products are in A2:A8, their amounts are in B2:B8, and E2 contains Lamp. In the sample transaction list, Lamp appears with amounts 200, 175, and 225, totaling 600. Use:

=SUMIF(A2:A8,E2,B2:B8)

SUMIF checks the criteria range A2:A8 and adds the corresponding cells from B2:B8 when the product matches E2. For more than one condition—for example, a product and a region—SUMIFS is designed to sum values meeting multiple criteria. Choosing the function by the question being asked avoids a common misconception: a single XLOOKUP does not automatically total duplicate matches.

Handle missing values and common errors

If the lookup value is not present, XLOOKUP returns #N/A unless you provide its optional if_not_found argument. With the same product and monthly layout, this version displays a message when no product matches:

=SUM(XLOOKUP(E2,A2:A5,B2:D5,"Product not found"))

For a missing product, the result is the text Product not found rather than a numeric total. This can be clearer than showing #N/A, but the output cell will then contain text in that situation. If downstream calculations require a number, decide explicitly whether a missing product should be treated as zero or flagged for correction.

Common issues to check include:

Range sizes differ: The lookup and return ranges need matching row counts. A2:A5 and B2:D5 both cover four rows.
Unexpected duplicates: XLOOKUP returns the first matching row by default. Check whether the lookup field is unique or whether SUMIF/SUMIFS is needed.
Numbers stored as text: Values that look numeric but are stored as text may not sum as expected. Check the source data type and clean or convert the data when needed.
Extra spaces or inconsistent names: “Lamp” and “Lamp ” may not match as intended. Standardize labels and inspect the source and lookup value.
Wrong return columns: Confirm that the selected return range contains the figures that should be added, not unrelated fields.

Version support and practical limits

XLOOKUP is available in Microsoft 365 and Excel 2021, and in later Excel releases that include the function. It is not available in Excel 2019 or Excel 2016, so a workbook using it may show #NAME? when opened in those older versions. If a workbook must work in an older edition, consider a compatible lookup approach such as INDEX and MATCH, or use SUMIF/SUMIFS when the actual task is totaling rows by criteria.

The example uses a small, fixed range for readability. In a growing workbook, Excel Tables can make formulas easier to maintain because structured references expand as rows are added. Another practical option is to use sufficiently broad ranges, while avoiding unnecessarily huge ranges in large workbooks if calculation speed becomes a concern. The key requirement remains that each lookup item’s return values belong to the same record.

For a more complex calculation—such as selecting a product and totaling only certain months—make the intended month selection explicit. You could return only the month columns needed, or use a separate calculation designed around the date or month criteria. Do not assume that the lookup alone filters out unwanted columns; it returns the columns included in its return array.

Frequently Asked Questions

Can XLOOKUP return multiple values for SUM to add?
Yes. If the return array includes several columns or rows for the matched item, XLOOKUP can return those corresponding values, and SUM can add them. The main example returns three monthly amounts from one product row.

Does XLOOKUP with SUM add every duplicate match?
No. A standard XLOOKUP returns the first match by default. Use SUMIF or SUMIFS to total all rows that meet one or more criteria.

Can I use XLOOKUP with SUM for two or more lookup values?
Yes, in dynamic-array versions such as Microsoft 365 or Excel 2021, a range of lookup values can be passed to XLOOKUP and the returned numbers wrapped in SUM. Each lookup must be found unless you intentionally handle missing values.

Why does my formula return #N/A?
The lookup value may not appear in the lookup range, or the values may differ because of spaces, spelling, or data type. Check the lookup input and source list; you can also use XLOOKUP’s if_not_found argument to display a chosen fallback.

Is SUM(XLOOKUP(…)) the same as SUMIF?
No. SUM(XLOOKUP(…)) adds the values returned for a lookup match or set of lookup values. SUMIF adds values from all rows whose criteria meet the condition, including repeated matches.

Why is Excel showing #NAME? for XLOOKUP?
The workbook may be open in an Excel version that does not support XLOOKUP, such as Excel 2019 or Excel 2016. Use Microsoft 365, Excel 2021 or a compatible later version, or replace the formula with a method supported by the older edition.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.