Excel Practice
Lessons Lesson 46

A Lookup on Two Criteria

About this lesson

A lookup on two criteria has two answers. SUMIFS over the matching rows, when the value is a number and the pair of criteria matches exactly one row. Or a helper column that joins the criteria into one key for XLOOKUP to search.

By the end you can

  • look up a number by two criteria with SUMIFS.
  • build a helper key and look it up with XLOOKUP.
  • say when the SUMIFS shortcut adds two rows without warning.

Practises SUMIFS COUNTIFS XLOOKUP INDEX MATCH

The idea

Neither VLOOKUP nor XLOOKUP takes two criteria, so there are two ways round it. SUMIFS with both conditions returns the sum of the matching rows. When exactly one row matches, that sum is the value. A helper column joining the criteria with & makes a single key. Then any lookup works on it, for text as well as numbers.

The trap is the SUMIFS shortcut with two matching rows. It adds them and says nothing. Count with COUNTIFS first, or use the key. Excel 365 users also write XLOOKUP(1, (C2:C5=B2)*(D2:D5=B3), E2:E5). That is the same idea as the key, done inside the formula.

The mistake to watch for

Using the SUMIFS shortcut when two rows match. It adds them and calls the total a price. Count with COUNTIFS first, or join the criteria into a key column and look that up. And build the key the same way on both sides, with or without a separator. Otherwise the lookup misses, for a reason that looks like a data problem.

Where this comes up again

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

Before looking anything up, check how many rows match both the region in B2 and the product in B3. In B4, count them with COUNTIFS.

To begin, type it exactly:

=COUNTIFS(C2:C5,B2,D2:D5,B3)

B4
Row A B C D E F G
1 Want Region Product Price Key
2 Region South North Tee 8 NorthTee
3 Product Hoodie North Hoodie 22 NorthHoodie
4 Matches South Tee 9 SouthTee
5 Price South Hoodie 21 SouthHoodie
6 By key
7
8 North Tee

Every step

  1. 1

    Before looking anything up, check how many rows match both the region in B2 and the product in B3. In B4, count them with COUNTIFS. To begin, type it exactly: =COUNTIFS(C2:C5,B2,D2:D5,B3).

    Hint. Two pairs of range and criterion.

  2. 2

    In B5, get the price with SUMIFS. The sum of the price column where the region and the product both match.

    Hint. Prices first.

  3. 3

    Here is the trap. Read =SUMIFS(E2:E5,C2:C5,"North") and say what it returns with only the region given.

    Hint. Both North rows.

  4. 4

    Column F joins region and product into one key. So the table can be searched on one column. In B6, look up the price with XLOOKUP. Join B2 and B3 the same way for the value to find.

    Hint. Join with & on both sides.

  5. 5

    In B8, write a formula using XLOOKUP and the key column that gives the North Tee price. Type the two words into the formula and join them.

    Hint. "North"&"Tee".