Sobre este exercício
Um conjunto de dados de 90 linhas em 2 planilhas, uma célula de resposta, cerca de 10 minutos. Corrigido no seu navegador: o valor e a fórmula por trás dele, com feedback que mostra o que corrigir.
Como resolver
- Quotes!F2
=XLOOKUP(C2&D2,Prices!$A$2:$A$61&Prices!$B$2:$B$61,Prices!$D$2:$D$61)
C2&D2 glues the product and the region into one text, and Prices!A2:A61&Prices!B2:B61 does the same for every row of the price list at once, producing a temporary array of 60 combined keys. XLOOKUP then finds the combined key in that array and returns the price from the same row — a two-column match without a helper column. SUMIFS reaches the same number another way: it adds every price whose product and region match, and since each pair appears exactly once, the sum is that single price.
Pitfall 1: joined keys can collide — "AB"&"C" equals "A"&"BC"; insert a separator that never appears in the data (C2&"|"&D2 on both sides) when keys are free text. Pitfall 2: SUMIFS silently sums duplicates and returns 0 for a missing pair, where XLOOKUP returns #N/A; use SUMIFS only when uniqueness is guaranteed and 0 is not a plausible price. Pitfall 3: in Excel 2019 and earlier, the array form needs INDEX/MATCH entered with Ctrl+Shift+Enter: =INDEX(Prices!$D$2:$D$61,MATCH(C2&D2,Prices!$A$2:$A$61&Prices!$B$2:$B$61,0)). Alternatives: boolean algebra =XLOOKUP(1,(Prices!$A$2:$A$61=C2)*(Prices!$B$2:$B$61=D2),Prices!$D$2:$D$61) avoids concatenation; =SUMPRODUCT((Prices!$A$2:$A$61=C2)*(Prices!$B$2:$B$61=D2)*Prices!$D$2:$D$61) is the pre-SUMIFS form; FILTER(…) spills every match, useful when pairs are not unique. Performance: array concatenation evaluates the whole price list for every quote row — fine for thousands of rows, slow for hundreds of thousands, where a helper key column on the Prices sheet is the classic fix.
Outras formas que funcionam
- =SUMIFS(Prices!$D$2:$D$61,Prices!$A$2:$A$61,C2,Prices!$B$2:$B$61,D2) — works in every Excel version; relies on each pair being unique
- =XLOOKUP(1,(Prices!$A$2:$A$61=C2)*(Prices!$B$2:$B$61=D2),Prices!$D$2:$D$61) — boolean array instead of concatenation
- =INDEX(Prices!$D$2:$D$61,MATCH(C2&D2,Prices!$A$2:$A$61&Prices!$B$2:$B$61,0)) — INDEX/MATCH array form (Ctrl+Shift+Enter before Excel 365)
- =SUMPRODUCT((Prices!$A$2:$A$61=C2)*(Prices!$B$2:$B$61=D2)*Prices!$D$2:$D$61) — the classic two-criteria formula
Erros comuns
Looking up the product only, =XLOOKUP(C2,Prices!$A$2:$A$61,Prices!$D$2:$D$61): it returns the first region listed for that product (North), which is wrong for every quote line in another region — 24 of 30 here.
Como corrigimos
Qualquer fórmula que produza o resultado certo é aceita, a menos que a tarefa cite uma função: nesse caso, outra função vale metade da pontuação. Comparamos os valores com uma pequena tolerância e ignoramos maiúsculas e espaços no texto. Um número certo digitado à mão em vez de uma fórmula vale metade da pontuação. Tarefas com várias células valem a pontuação integral com 100% das células certas e metade a partir de 50%. Quando faz diferença, também mudamos uma célula de entrada em uma cópia da sua planilha e verificamos se sua fórmula acompanha.
Respostas calculadas com as regras do Excel 365 e verificadas no nosso corretor em 16 de novembro de 2026. Autor: GridBelt team.