Skip to content
GridBelt
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign in
Start the free test
  • Excel Test
  • Practice
  • Programs
  • Pricing
Sign inCreate account
Start the free test
GridBelt

Practice Excel in your browser.

Excel Test

  • Level 1: Beginner
  • Level 2: Intermediate
  • Level 3: Expert
  • Excel test in 7 days
  • Leaderboard
  • Excel test for interview
  • Indeed Excel assessment

Practice

  • All exercises
  • Programs
  • For beginners

Guides

  • Excel formulas
  • Excel shortcuts

Company

  • About
  • Pricing
  • How we grade

© GridBelt. All rights reserved.

  • Privacy Policy
  • Terms of Use
  • Refund Policy

Excel is a trademark of Microsoft Corporation. Indeed, TestGorilla, SHL and other marks are the property of their owners. GridBelt is an independent practice site and is not affiliated with, endorsed by or sponsored by Microsoft, TestGorilla, SHL or Indeed.

  1. Practice
  2. /
  3. XLOOKUP

XLOOKUP with two criteria: price by product and region

  • Expert
  • ~10 min
  • XLOOKUP

A building-materials supplier prices by region: the Prices sheet holds 60 rows — 15 products × 4 regions — with product, region, unit and unit price. The Quotes sheet has 30 quote lines, each with a product in column C and a region in column D. A lookup on the product alone returns the first region's price and is wrong on 24 of the 30 lines; the key is the pair product + region.

What you need to do

  1. 1On Quotes, click F2 and build the two-part key: C2&D2 joins the product and the region into one text.
  2. 2Type =XLOOKUP(C2&D2, then build the matching key for the whole price list: Prices!$A$2:$A$61&Prices!$B$2:$B$61 (select each column on the Prices tab, press F4 / ⌘T, join them with &).
  3. 3Add the return column Prices!$D$2:$D$61, close the bracket and press Enter — Excel 365 evaluates the joined columns as an array, no Ctrl+Shift+Enter needed.
  4. 4Fill F2 down to F31. Alternative without arrays: =SUMIFS(Prices!$D$2:$D$61,Prices!$A$2:$A$61,C2,Prices!$B$2:$B$61,D2) works because every pair appears exactly once.

Answer cells

Feedback

Type your formula in the grid, press Enter, then Check. We grade the value in each answer cell and the formula behind it.

Tasks

  1. Not checkedIn F2, return the unit price from the Prices sheet for the product in C2 and the region in D2 — XLOOKUP on the two keys joined with & (or SUMIFS with both criteria) — then fill down to F31.

Desktop recommended for spreadsheet tasks. The sheet scrolls inside this box.

StartInsertFormulasDataView
A1fxPreparing your workbook…
A
B
C
D
E
F
G
H
I
J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Enter your formula in the sheet, then check.

About this exercise

A 90-row dataset on 2 sheets, one answer cell, about 10 minutes. Graded in your browser: the value and the formula behind it, with feedback that names the fix.

How to solve it

  • 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.

Other ways that work

  • =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

Common mistakes

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.

How we grade

Any formula that produces the right result is accepted, unless the task names a function: then another function earns half credit. We compare values with a small tolerance and ignore case and spaces in text. A correct number typed by hand instead of a formula earns half credit. Multi-cell tasks earn full credit at 100% of cells, half credit from 50%. Where it matters, we also change an input cell on a copy of your sheet and check that your formula follows.

Answers computed with Excel 365 rules and checked in our grading engine on November 16, 2026. Author: GridBelt team.

In these programs

  • Excel test in 7 days, Level 3 planDay 5
  • Excel for Data AnalystsDay 9
  • Excel for Accounting and BookkeepingDay 9

Related exercises

  • Replace VLOOKUP with XLOOKUPIntermediate, ~6 min
  • Look left: employee ID by emailExpert, ~6 min
  • Two-way lookup in a matrixExpert, ~8 min
  • Last order date per customerExpert, ~10 min

Function reference

Official documentation

Next exercise

INDEX and MATCH exact lookup