# How to use Array with Sumproduct or other formula

I am trying to use an array formula like SUMPRODUCT to perform a lookup with 2 criteria.

I have a sheet named data that stores customer-specific pricing for products.

A B C             D
1 Description General Cust A Cust B
2 Product A  \$ 12.90  \$ 7.90  \$ 8.90
3 Product B  \$ 12.90  \$ 7.90  \$ 8.90
4 Product C  \$ 15.90  \$ 8.90  \$ 9.90
5 Shipping Fee  \$ 15.00  \$ 8.00  \$ 18.00

In other sheets I have my desired result, customer orders, and I want to be able to return the correct pricing based on the specific customer and product.

A B C D E
1 Customer Name Cust B
2
3 Item Description Qty Unit Price Total
4 1 Product A 3  \$ 8.90  \$ 26.70
5 2 Product B 5  \$ 8.90  \$ 44.50
6 3 Product C 6  \$ 9.90  \$ 59.40
7 4 Shipping Fee 1  \$ 18.00  \$ 18.00

I tried to use the following formula to match the correct price with the product and customer but am getting errors.

``````=SUMPRODUCT(Data!\$B\$2:\$M\$31,
(\$B\$1=Data!\$A\$1::\$M\$1)*
(\$B4=Data!\$A\$2:\$A\$31)*
ISNUMBER(Data!\$B\$:\$M\$31))
``````

• While a link to a spreadsheet might be helpful, the question should still be self-contained. This means you should describe your spreadsheet's relevant parts and include sample data in the question body. Commented May 14 at 14:32
• The question does not explicitly specify what should happen when a customer name cannot be found in the `Data` table. Presumably, the lookup should default to the `General` column then? Commented May 16 at 7:41
• @doubleunary you are correct (as usual) to flag that. I have now included error handling in case the options are not limited to valid strings. Commented May 30 at 15:53

# Formula

Returns customer-specific pricing for a list of products. I think VLOOKUP with MATCH is a better fit than SUMPRODUCT.

``````=LET(c,B1, d,Data!A2:D, p,B2:B,
INDEX(VLOOKUP(TOCOL(p,1), d,
MATCH(c, INDEX(d,1),0))))
``````
1. `c` stores the customer name.
2. `d` stores the Data lookup range
3. `p` stores the range of products to look up.

As @doubleunary pointed out in his answer my formula doesn't provide a success path if the customer name is absent or not found. If you plan to use either a valid customer name or the value `"General"`, such as a data validation drop menu from a helper column, then it isn't an issue. Otherwise, as they pointed out, it is prudent to add error handling which I did not originally.

## Formula w/ error Handling

``````=LET(c,B1, d,Data!A2:D, p,B2:B,
INDEX(VLOOKUP(TOCOL(p,1), d,
IFNA(MATCH(c, INDEX(d,1), 2), 0))))
``````
• If the customer name `c` cannot be found, IFNA supplies the value `2` which matches the `General` column.
• thanks for helping. Commented May 30 at 13:31