How to / Lookups

How to look up a value with two criteria in Excel

Use =XLOOKUP(1, (A2:A5="Shirt")*(B2:B5="L"), C2:C5). It finds the first row where both conditions are true.

The price of a large shirt

ABCDE
1ProductSizePrice
2ShirtM2022
3ShirtL22
4HatM12
5HatL14

In E2: =XLOOKUP(1, (A2:A5="Shirt")*(B2:B5="L"), C2:C5) → 22

How it works

  1. Each test gives TRUE or FALSE for every row. Multiplied together they give 1 where both are true and 0 everywhere else.
  2. XLOOKUP searches that column of 1s and 0s for a 1.
  3. It returns the price from the same row.

Variations

  1. In older versions of Excel

    ABCDE
    1ProductSizePrice
    2ShirtM2012
    3ShirtL22
    4HatM12
    5HatL14

    In E2: =INDEX(C2:C5, MATCH(1, (A2:A5="Hat")*(B2:B5="M"), 0)) → 12

    In Excel 2019 and earlier, finish it with Ctrl+Shift+Enter.

  2. When the result is a number

    ABCDE
    1ProductSizePrice
    2ShirtM2022
    3ShirtL22
    4HatM12
    5HatL14

    In E2: =SUMIFS(C2:C5, A2:A5, "Shirt", B2:B5, "L") → 22

    If only one row can match, adding up the matches gives that row's value. It does not work for text.

  3. Your own text when nothing matches

    ABCDE
    1ProductSizePrice
    2ShirtM20Not found
    3ShirtL22
    4HatM12
    5HatL14

    In E2: =XLOOKUP(1, (A2:A5="Coat")*(B2:B5="L"), C2:C5, "Not found") → Not found

Watch out for

Doesn't work on your data? Paste your formula into the formula checker with a few rows of your sheet. It points at the part that fails and says why.

Functions used

Practise it

Related