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

By Ali, Founder, Excel Lessons

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.

Read more about this

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 up, check how many rows match both criteria.

In B4, type:

=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 up, check how many rows match both criteria. In B4, type: =COUNTIFS(C2:C5,B2,D2:D5,B3).

    Hint. Two pairs of range and criterion.

  2. 2

    In B5, get the price with SUMIFS. That is the sum where both the region and the product 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, find the North Tee price with XLOOKUP. Join the two words as text to search the key.

    Hint. "North"&"Tee".