Price by SKU and region, from a scanned key
The problem
Problem statement
The same SKU sells at a different price per region, so one key is not enough. The price list is in A2:C7, the scanned SKU is in F1 (with its padding), and the region is in F2. Put the price in the Price cell F3.
Two criteria, and the SKU has to be cleaned inside the formula — the scanner writes it lower-case with spaces.
Problem data
Show the sheet as a table (selectable, copyable)
| A | B | C | E | F | |
|---|---|---|---|---|---|
| 1 | SKU | Region | Price | SKU scanned | sku-102 |
| 2 | SKU-101 | US | 49 | Region | EU |
| 3 | SKU-102 | EU | 19.5 | Price | |
| 4 | SKU-102 | US | 21 | ||
| 5 | SKU-103 | EU | 24.99 | ||
| 6 | SKU-101 | EU | 52 | ||
| 7 | SKU-103 | US | 25.5 |
Notes
When the answer is a number and every condition is equality, SUMIFS doubles as a lookup: it returns the total of the rows that satisfy every pair, which is the single matching price. TRIM(UPPER(F1)) cleans the key first. INDEX + MATCH with two multiplied conditions is the array alternative, and it is what you reach for when the answer is text.
How to approach
Your answer goes in F3. On submit we re-run your formula against 3 different sets of input, so a hardcoded number will fail — the formula has to follow the cells.
- Target cell is F3
- Clean the key: TRIM(UPPER(F1))
- SUMIFS with two range/criteria pairs acts as a lookup
- Shape: =SUMIFS(?, ?, ?, ?, ?)
Official solutions
Show 2 solutions — try it yourself first
Approach 1: SUMIFS as a two-criteria lookup
=SUMIFS($C$2:$C$7, $A$2:$A$7, TRIM(UPPER(F1)), $B$2:$B$7, F2)
TRIM(UPPER(" sku-102 ")) is "SKU-102"; the only row matching both that and EU is 19.50. No match returns 0, which is why the fallback matters when 0 is a legal price.
Approach 2: INDEX + MATCH over two conditions
=INDEX($C$2:$C$7, MATCH(1, ($A$2:$A$7=TRIM(UPPER(F1)))*($B$2:$B$7=F2), 0))
MATCH(1, ...) finds the first row where both conditions are 1 after multiplication. Same answer, and unlike SUMIFS it works when the value you want is text — and it returns #N/A instead of a silent 0.
Function guide
FAQ
How do I look up a value that depends on two columns?
=SUMIFS(values, col1, key1, col2, key2) when the answer is numeric, or =INDEX(values, MATCH(1, (col1=key1)*(col2=key2), 0)) when it is text.
Why does SUMIFS return 0 instead of an error?
SUMIFS with no matching row sums nothing, which is 0. If 0 is a possible real value, wrap it: =IF(COUNTIFS(...)=0, "not found", SUMIFS(...)).